SQL Murder Mystery | Hacker News Reader

SQL Murder Mystery

mystery.knightlab.com

823 pointskickscondor79 comments

Ah, fond memories :) I once made a javascript implementation of an sql engine for a weekend game.

http://ludumdare.com/compo/ludum-dare-27/?action=preview&uid...

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.

Because it was all client-side javascript, and hosted on github pages, its still playable all these years later:

http://williame.github.io/ludum_dare_27_snowden/index.html

Of course it was a complete flop!

But making an sql engine under that kind of time pressure was pretty intense.

https://williamedwardscoder.tumblr.com/post/59997353762/runn...

Hard to believe that whole Snowden thing was 6 years ago now!?

In reality, the government took the easy route and just revoked his passport while he was one his way from Hong Kong to Moscow, to catch a connecting flight to another flight to Ecuador. They forced him to set up residency in Russia of all places!

Interesting!

But wasn't Emscripten already a thing in 2013? SQLite requires nearly no dependencies so it should have been possible to compile it.

This was a Ludum Dare contest entrant. Sometimes, the journey is the destination.

Writing an SQL engine is like writing a ray tracer or implementing a compression algorithm: every programmer should do it!

From the better link from kevindong:

"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."

Thanks! This was the key sentence missing from the original link. I hadn't a clue what I was supposed to be looking for, but this is the minimum info needed to get started. (NB: Solved it in a few minutes after I think 8 queries - though I could have combined a couple I think, for even less queries)

Heh, I was looking at all of the murders in the police report database trying to figure out which one it was talking about.

Whaaat, I wasn't supposed to muck around the DB for 5 minutes trying to find the starting point?

Were you successful at solving the puzzle without the starting clues?

Did they change it? That exact sentence is right there

I accidentally revealed the solution after running the .schema command (the solution is in the trigger code on that table). ️ Might be a record though - I got the answer in only a few seconds.

Looks like that's blocked now.

Not really: `select sql from sqlite_master` still reveals the solution.

For me it shows the hexcode of the name but not the name itself.

yes

It looks like this should help:

https://github.com/NUKnightLab/sql-mysteries/blob/master/pro...

Thank you. Perhaps the OP should link to https://github.com/NUKnightLab/sql-mysteries ?

A few years ago I ported IBM's SqlDetective from informix to postgresql and mysql. https://github.com/verpeteren/SqlDetective

As I have a working knowledge of SQL this was very simple but my friends who use it sparsely in their financial jobs had a blast racing each other.

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!

This only applies during December, but https://adventofcode.com/ is a fun experience! Most puzzles range from 30 minutes to 3 hours from start to finish.

My guess is when you don't make it a hard core coding thing but more of a general search quest it would be more succesful. Like in this case: you need to have a search function / filter options on those tables. Even transfering it to Excel (the horror) would increase the target population with huge factor.

Here's a super fun one where you SSH into different servers and have to use Bash/unix tools to find clues to get to the next one: https://overthewire.org/wargames/bandit/

I was thinking of bringing this challenge into a team meeting. How fast were folks able to solve it on average?

Answering my own question, 10-20 minutes if you're pretty basic at SQL :)

There is a Lot of crime in SQL City. I wonder if ORMTown is just as bad. ;)

Actually, the murder victims are all ORM users.

well said.

"We built SQL City on GRANT and ROLE."

"too many script kiddies... playin' injection games."

:-D

> Time to break out the champagne!

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 always wanted do invest my 20 hrs into SQL.

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 ?

Sure thing. I think learning SQL is never wasted time.

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.

And, for anyone looking to pick up the basics: These ancient websites got me up to speed ages ago:

www.sqlcourse.com

www.sqlcourse2.com

>Try looking into SQL functions & TRIGGERs, LATERAL JOINs, ROLLUP & CUBE.

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.

Knowledge is power and knowing more deeply about what your database is capable of is very helpful. It's worth knowing every tool in your tool chest!

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[0] 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.

0: https://softwareengineering.stackexchange.com/questions/1944...

Yes, I think it's very useful. There are lots of things that SQL can do that are being done by code right now because the developers don't know any better.

The game itself has a walkthrough [0] of all the SQL features you need to solve the murder mystery. From skimming through it, it seems to be pretty good and succinct.

[0]: https://mystery.knightlab.com/walkthrough.htmlhttps://myster...

https://theartofpostgresql.com/

Added to my buy/reading list. Looks like it could be a good mix of simpler queries and more advanced concepts.

Stanford's Intro to DBs course is probably the best introduction to SQL available online.

https://lagunita.stanford.edu/courses/DB/2014/SelfPaced/abou...

reminds me of the command line / unix tools murder mystery

https://github.com/veltman/clmystery

Indeed, Noah's project was a direct inspiration for us, and we tried to clearly credit it.

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)

Really enjoyed playing through the game, and especially enjoyed seeing your name in the credits and thinking hey, I was pretty suspicious of you at one point.

OMG I love this. I just sent it to my buddy who as a PM is learning SQL to not rely on the BI team since the turnaround can be days.

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..."

well for me not looking at the schema made it more fun and I guess the plot was also pretty involving ^^

  >  ... not looking at the schema ...

Wait, you just guessed the table names? what am i missing here?

probably means looked at SELECT * from each table and interpreted the FK relationships vs. querying the schema table or looking at the ERD...

One of the productive things we did as a team is to give our SQL savvy PM read access to a production BI replica.

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.

Good clean fun :) Knight Lab does lots for journalism and the web. Here's one of my favorite projects of theirs: https://timeline.knightlab.com/

Be warned that there are spoilers built into the DB, if you look too closely. The trigger on the solution DB has the answer built into the SQL, as I learned when I ruined the game for myself.

To be fair, a hex of the answer, so you only spoil yourself if you're very good at reading base-16-encoded ASCII data.

I saw an unhexed answer in the original datasette link.

Oh, might be outdated. I played it locally from the latest master.

Cool to see more SQL role play games, goes next to this bookmark of my former colleague: http://sql-island.informatik.uni-kl.de/?lang=en

I was surprised I actually got the solution! I'm no SQL expert beyond basic queries, so I definitely had to do a lot of really basic steps to get there, but I did it nonetheless!

well the nice thing is that you can break down each step if you're not confident with joining virtual tables and aggregated results but also after finishing I think you can right a single query to insert the answer, so it's quite widely accessible!

Just spent some nice 30 minutes figuring it out, got the bonus ending too! It's a very cool concept for a game! Wish it was a bit longer.

I played along on the web based ui but for the life of me can't figure out how to check my solution? I see that there is an insert statement given on the github page but I get a 'Statement must be a SELECT' error when I try to execute it. What am I missing?

This link is better: http://mystery.knightlab.com

Ditto. Following the link given in sibling comment, I see the same instruction, but the insert fails with the same error.

EDIT: Running the queries from the other URL (http://mystery.knightlab.com/) works. Admins, you should change the URL to this one.

Very nice except the fact the whole page breaks for me when using joins.

I've never come across an "SQL game" before, this was great fun!

I got it in around 15 minutes though, really wish there was a bit more too it, or that more complex queries were required.

I haven't played it but I'm intrigued by the idea. This is MIT so forking it and creating sequel mysteries wouldn't to far fetched.

we'd love to see that happen!

And really, the most valuable piece, IMO, are the SQL web components, from SelectStarSQL, which are CC0 public domain.

I love this type of story-based learning! I was playing with twilioquest this weekend, I really appreciate the developers’ creativity. How can I find more tools like these?

If you're into logic puzzles and python, I built Jindosh.com. If you like javascript, there's this great game at https://alexnisnevich.github.io/untrusted/

Fun game. The query engine seemed to die on me if I gave it certain syntactically wrong queries though and only a refresh of the page would solve it.

I'm lazy, so for both the first and second stage I narrowed down the selection to 2 to 3 people and then tried them all.

You were about one join away from narrowing it down to the killer.

Very smart! I love how the `solution` table works to add some additional fun to the challenge.

Here is a single query to get you both solutions. https://pastebin.com/P7LN9jMz

This was after I solved it the old fashioned way though :)

I thought you had created a single massive join to find the answer and was pre-impressed. This is fine in the strict "correct solution" answer but totally against the spirit, so boo!

That was a fun distraction. Not too difficult though.

Fun game ! lol. Though time consuming

I don't get it, with just a "select * from sqlite_master" you can see the trigger and solve both mysteries

This is super cool

love it

This version doesn't support INSERTing the solution from the web. https://mystery.knightlab.com does, though, and provides some guidance out the door.

Fun little thing, thanks!

Ok, we'll change the URL to that from https://sql-murder-mystery.datasette.io/sql-murder-mystery, since it seems to be the original source also.

Thanks!

Reply on news.ycombinator.com