It was a very long time ago and, then, everyone was wondering where Snowden was. I think this was after he was identified, and just before he ended up in Russia.
Anyway, you are given an NSA Prism terminal, complete with Windows 95 look and feel and a paperclip.
You have to use your leet SQL skills to find out what flight he was on, and then book your agents onto intercepting flights.
Of course it was a complete flop!
But making an sql engine under that kind of time pressure was pretty intense.
Hard to believe that whole Snowden thing was 6 years ago now!?
But wasn't Emscripten already a thing in 2013? SQLite requires nearly no dependencies so it should have been possible to compile it.
Writing an SQL engine is like writing a ray tracer or implementing a compression algorithm: every programmer should do it!
"A crime has taken place and the detective needs your help. The detective gave you the crime scene report, but you somehow lost it. You vaguely remember that the crime was a murder that occurred sometime on Jan.15, 2018 and that it took place in SQL City . Start by retrieving the corresponding crime scene report from the police department’s database."
This type of gamification is awesome and as someone who loves coding but doesn't find Sudoku's or whatever very enjoyable, I would love a place I could do little coding challenges like this in break times!
Anyone know of any? I'm thinking "Escape Rooms" via coding could be a big thing!
Answering my own question, 10-20 minutes if you're pretty basic at SQL :)
This was a lot of fun; I'd definitely play level 2.
The nice thing about it was that you had some flexibility around getting the exact SQL query, and just visualizing a slightly larger superset of the data.
I've already put 10 with a data analyst course so i know what a sqlite , select and joins are. I've created a not trivial query once.
Is this OK for learn some more ?
Try looking into SQL functions & TRIGGERs, LATERAL JOINs, ROLLUP & CUBE.
If you have even more time, check out the different things an SQL server can do when you DROP a row that contains references, how you define CONSTRAINTs, and some particular features that only some subset of SQL dialects have, such as LISTEN/NOTIFY for PostgreSQL.
If you're a developer, I would look into CONSTRAINTS way before getting to anything like this. Learning how to model your constraints correctly in the database (preventing duplicates, etc) is one of the most important bits of schema design because it will save you tons of code and likely a bunch of race conditions.
That said, be careful with going too overboard with one specific tool (SQL). You may find yourself using a hammer for everything, when another tool may be more appropriate.
More specifically, it's easy to abuse advanced SQL constructs to create a mess for yourself. Too much business logic in the database is something I've seen become a nightmare for other programmers looking at software later. :)
This post goes a bit into more detail about explaining where you should keep your "business logic" when choosing to put it in code or into SQL.
We love that people are enjoying this so much, so we're also grateful to Simon Willison for reviving attention on the original project, and Zi Chong Kao, whose SQL tutorial site showed us the possibility of mounting the whole thing in a browser page.
(I'm the person who guided the Knight Lab students' original work and ported it to the web hosted version at mystery.knightlab.com)
Ultra tiny desire - I only wish there was more on the story line/intro framing. Took at look at the schema and was like "What is Get fit now? - ohhhh, it's a gym... got it..."
We'll still get occasional requests for information or questions, but by and large he can deal with the day to day.
So good on your buddy for wanting to learn how to do it himself. If he's even halfway successful it'll save him and his teammates time in the long run.
> ... not looking at the schema ...
EDIT: Running the queries from the other URL (http://mystery.knightlab.com/) works. Admins, you should change the URL to this one.
I got it in around 15 minutes though, really wish there was a bit more too it, or that more complex queries were required.
And really, the most valuable piece, IMO, are the SQL web components, from SelectStarSQL, which are CC0 public domain.
This was after I solved it the old fashioned way though :)
Fun little thing, thanks!