Provide queries to analyze data in the database

[jawaad-ahmad]

@DesignChallengeGuy wants to analyze the data collected during the competition. The following queries have been requested:

1. Need the exchange of messages between each team's phone an the MS during the competition.
2. How many attempts did it take each team in solving each challenge?
3. How many markers at each vertex were scanned at the Launch challenge before the correct marker was found? If the average was just 1, that implies the students' GPS tool was very accurate; whereas if the average was 3, that implies that more guessing was involved.

The limitation here is that the output will only be as good as the data stored in the database. We had the issue of times continuing on the scoreboard, but it doesn't look like that will impact any of the queries requested above.

[jawaad-ahmad]

Here's my take on these off the top of my head without trying anything out. Hopefully others can refine and catch obvious problems with these prior to trying them out. Also do we have the MS SD card dump available somewhere so we can run these queries against its database dump?

1. Need the exchange of messages between each team's phone an the MS during the competition.

   SELECT * FROM pi_event WHERE TODO ORDER BY team, pi, time

2. How many attempts did it take each team in solving each challenge?

   SELECT * FROM pi_event e SOME JOIN pi_station ON e.pi = s.id WHERE type = SUBMIT_MSG_TYPE GROUP BY pi ORDER BY team, time

3. How many markers at each vertex were scanned at the Launch challenge before the correct marker was found?

   SELECT * FROM pi_event e SOME JOIN pi_station s ON e.pi = s.id WHERE type = SUBMIT_MSG_TYPE AND station_type = LAUNCH_STATION_TYPE GROUP BY pi ORDER BY team, time