
SQL Murder Mystery - kickscondor
https://mystery.knightlab.com/
======
willvarfar
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...](http://ludumdare.com/compo/ludum-
dare-27/?action=preview&uid=10313)

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](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...](https://williamedwardscoder.tumblr.com/post/59997353762/running-
sql-in-your-browser)

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

~~~
garaetjjte
Interesting!

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

~~~
willvarfar
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!

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

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

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

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

~~~
dlgeek
Looks like that's blocked now.

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

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

~~~
prassi
yes

------
tunesmith
It looks like this should help:

[https://github.com/NUKnightLab/sql-
mysteries/blob/master/pro...](https://github.com/NUKnightLab/sql-
mysteries/blob/master/prompt_experienced.pdf)

~~~
rozab
Thank you. Perhaps the OP should link to [https://github.com/NUKnightLab/sql-
mysteries](https://github.com/NUKnightLab/sql-mysteries) ?

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

------
vyper91
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!

~~~
PascLeRasc
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/](https://overthewire.org/wargames/bandit/)

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

~~~
smitty1e
"We built SQL City on GRANT and ROLE."

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

:-D

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

------
lucasverra
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 ?

~~~
rntksi
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.

~~~
pathseeker
>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.

------
mushufasa
reminds me of the command line / unix tools murder mystery

[https://github.com/veltman/clmystery](https://github.com/veltman/clmystery)

~~~
JoeGermuska
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)

~~~
n4r9
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.

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

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

~~~
4ndr3vv

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

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

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

------
reilly3000
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/](https://timeline.knightlab.com/)

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

~~~
kroltan
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.

~~~
pbiggar
I saw an unhexed answer in the original datasette link.

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

------
contradictioned
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](http://sql-
island.informatik.uni-kl.de/?lang=en)

------
threesided
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!

~~~
ticmasta
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!

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

------
ihunter2839
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?

~~~
kevindong
This link is better:
[http://mystery.knightlab.com](http://mystery.knightlab.com)

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

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

~~~
hultner
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.

~~~
JoeGermuska
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.

------
merciBien
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?

~~~
gucciTheWizard
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/](https://alexnisnevich.github.io/untrusted/)

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

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

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

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

------
sqldevuser
Here is a single query to get you both solutions.
[https://pastebin.com/P7LN9jMz](https://pastebin.com/P7LN9jMz)

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

~~~
ticmasta
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!

------
annoyingnoob
That was a fun distraction. Not too difficult though.

------
overridex
Fun game ! lol. Though time consuming

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

------
awoods187
This is super cool

------
upstandingdude
love it

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

Fun little thing, thanks!

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

~~~
rrix2
Thanks!

