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 :)
SQL Murder Mystery
71–80 of 82 posts
Re: SQL Murder Mystery
#72OMG 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..."
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.
Re: SQL Murder Mystery
#73That was a fun distraction. Not too difficult though.
Re: SQL Murder Mystery
#74Ah, 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 paperc…
Interesting!
But wasn't Emscripten already a thing in 2013? SQLite requires nearly no dependencies so it should have been possible to compile it.
Re: SQL Murder Mystery
#75I 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!
Re: SQL Murder Mystery
#76Earlier quoted context omitted.
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...
Re: SQL Murder Mystery
#77Here 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!
Re: SQL Murder Mystery
#78I 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 ?
https://theartofpostgresql.com/
Added to my buy/reading list. Looks like it could be a good mix of simpler queries and more advanced concepts.
Re: SQL Murder Mystery
#79Ah, 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 paperc…
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!
Re: SQL Murder Mystery
#80reminds 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 i…
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.