SQL Murder Mystery
mystery.knightlab.com
mystery.knightlab.com
You need at least 3 for the first one - 1 to get the info on the murder which is just text, 2nd to get the interviews (which aren't connected to the murder info, though they probably should), and then you can get to the solution in one big query.
Spoilers ahead:
- 1st query to get the crime report. This contain plaintext clues.
- 2nd query to get witness interviews. Again, these contain plaintext clues that can't be used in a `join` statement or a `where` statement in SQL.
- 3rd query to get interview with suspect. This contain plaintext information needed for solving the second puzzle
- 4th query for finding the master mind.
Short of writing regexes that basically extracts the clues by looking for patterns that are verbatim the clues themselves, I don't see how it can be done any shorter than this.
You can see my solution here on my github: https://github.com/olsgaard/sql_murder_mystery_solution/blob...
where num1 <= date <= num2
select * from facebook_event_checkin fb
where 20171201 <= fb.date <= 20171231:
28508 | 5880 | Nudists are people who wear one-button suits. | 20170913 <--SELECT 20171201 <= fb.date <= 0, COUNT(1), MIN(fb.date), MAX(fb.date) FROM facebook_event_checkin fb GROUP BY 1
returns:
0 6302 20171201 20180501
1 13709 20170101 20171130
.
SQLite has some completely bonkers unexpected behaviors. The above query with the "GROUP BY 1" line removed returns:
0 20011 20170101 20180501
instead of throwing an error, which is an absolutely bonkers decision.