10 ms·
SQL Murder Mystery
- tunesmith 7y agoIt looks like this should help: https://github.com/NUKnightLab/sql-mysteries/blob/master/prompt_experienced.pdf https://github.com/NUKnightLab/sql-mysteries/blob/master/pro...
- rozab 7y agoThank you. Perhaps the OP should link to https://github.com/NUKnightLab/sql-mysteries https://github.com/NUKnightLab/sql-mysteries ?
- Waterluvian 7y agoThere is a Lot of crime in SQL City. I wonder if ORMTown is just as bad. ;)
- tim-- 7y agoVery smart! I love how the `solution` table works to add some additional fun to the challenge.
- ihunter2839 7y agoI 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 7y agoThis link is better: http://mystery.knightlab.com http://mystery.knightlab.com
- dlgeek 7y agoDitto. 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/ http://mystery.knightlab.com/) works. Admins, you should change the URL to this one.
- dlgeek 7y agoFrom 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."
- cyberferret 7y agoThanks! 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)
- jandrese 7y agoHeh, I was looking at all of the murders in the police report database trying to figure out which one it was talking about.
- reagent_finder 7y agoWhaaat, I wasn't supposed to muck around the DB for 5 minutes trying to find the starting point?
- ct0 7y agoWere you successful at solving the puzzle without the starting clues?
- artlovecode 7y agoDid they change it? That exact sentence is right there
- merciBien 7y agoI 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 7y agoIf 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/
- deleted 7y ago[deleted]
- kroltan 7y agoJust 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.
- rrix2 7y agoThis 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!
- pbiggar 7y agoBe 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.
- dbravender 7y agoI 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.
- mushufasa 7y agoreminds me of the command line / unix tools murder mystery https://github.com/veltman/clmystery https://github.com/veltman/clmystery
- JoeGermuska 7y agoIndeed, 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 7y agoReally 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 7y agoOMG 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 7y agowell for me not looking at the schema made it more fun and I guess the plot was also pretty involving ^^
- scaryclam 7y agoOne 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.
- contradictioned 7y agoCool 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
- LifeIsBio 7y ago> 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.
- upstandingdude 7y agolove it
- reilly3000 7y agoGood 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/
- verpeteren 7y agoA few years ago I ported IBM's SqlDetective from informix to postgresql and mysql. https://github.com/verpeteren/SqlDetective https://github.com/verpeteren/SqlDetective
- lucasverra 7y agoI 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 7y agoSure 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.
- UweSchmidt 7y agoAnd, for anyone looking to pick up the basics: These ancient websites got me up to speed ages ago: www.sqlcourse.com www.sqlcourse2.com
- pathseeker 7y ago>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.
- freeqaz 7y agoKnowledge 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/194446/how-much-business-logic-should-the-database-implement https://softwareengineering.stackexchange.com/questions/1944...
- breakingcups 7y agoFun 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.
- overridex 7y agoFun game ! lol. Though time consuming
- Amendeson530 7y agoI 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.
- GordonS 7y agoI'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 7y agoI 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 7y agowe'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.
- willvarfar 7y agoAh, 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=10313 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 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/running-sql-in-your-browser https://williamedwardscoder.tumblr.com/post/59997353762/runn... Hard to believe that whole Snowden thing was 6 years ago now!?
- teekert 7y agoIn 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!
- garaetjjte 7y agoInteresting! But wasn't Emscripten already a thing in 2013? SQLite requires nearly no dependencies so it should have been possible to compile it.
- willvarfar 7y agoThis 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!
- cyborgx7 7y agoI'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 7y agoYou were about one join away from narrowing it down to the killer.
- tomd3v 7y agoVery nice except the fact the whole page breaks for me when using joins.
- threesided 7y agoI 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 7y agowell 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!
- vyper91 7y agoAs 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!
- bbaumgar 7y agoThis only applies during December, but https://adventofcode.com/ https://adventofcode.com/ is a fun experience! Most puzzles range from 30 minutes to 3 hours from start to finish.
- ehou 7y agoMy 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.
- PascLeRasc 7y agoHere'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/
- codezero 7y agoI 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 :)
- atum47 7y agoI don't get it, with just a "select * from sqlite_master" you can see the trigger and solve both mysteries
- awoods187 7y agoThis is super cool
- sqldevuser 7y agoHere 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 7y agoI 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 7y agoThat was a fun distraction. Not too difficult though.