How I'd Solve a Murder Mystery Using Only SQL

The Crime Scene

Imagine you're called to Blackwater Bay - a fog-choked coastal town where three prominent citizens have turned up dead in the span of a week. The local police are overwhelmed. Forensics is backed up for months.

But here's the thing: someone digitized the town's records years ago. Financial transactions, phone records, property deeds, employment histories - it's all sitting in a PostgreSQL database, waiting for the right queries.

That's where you come in. Your weapon isn't a magnifying glass. It's a SQL terminal.

Let me walk you through exactly how I'd crack this case - one query at a time.

Step 1: Pull the Suspect List

Every investigation starts with the basics. In detective work, that means canvassing witnesses. In SQL, it means SELECT.

SELECT first_name, last_name, relationship_to_victim
FROM town_residents
WHERE known_associate = true
  AND alibi_verified = false;

SELECT chooses which columns you want to see. FROM tells SQL which table to search. And WHERE filters down to only the rows matching your criteria.

Think of it this way: you're not reading every file in the police station. You're pulling only the files of people who knew the victims and can't prove where they were.

The best SQL learners don't memorize syntax - they learn to ask the right questions. SELECT is just the language you ask them in.

Step 2: Follow the Money

Suspects are nice, but evidence is better. And in a murder case, financial records are where secrets hide. The problem? The money data is in one table, and the suspect data is in another.

Enter the JOIN - SQL's way of connecting related tables.

SELECT r.first_name, r.last_name, 
       t.amount, t.recipient, t.transaction_date
FROM town_residents r
JOIN bank_transactions t 
  ON r.account_id = t.account_id
WHERE t.amount > 5000
  AND t.transaction_date > '2024-01-01'
ORDER BY t.amount DESC;

Now we're getting somewhere. This query connects people to their money. The JOIN links town_residents to bank_transactions through their shared account_id.

We're filtering for large transactions (over $5,000) made recently - because big money moving right before murders is never a coincidence. And ORDER BY amount DESC puts the biggest transfers at the top.

Step 3: Reconstruct the Timeline

Three murders. Three different dates. Is there a pattern? To find out, we need to reconstruct what happened around each crime.

SELECT v.name AS victim, 
       v.date_of_death,
       e.event_type, 
       e.location,
       e.timestamp
FROM victims v
JOIN town_events e 
  ON e.timestamp BETWEEN v.date_of_death - INTERVAL '48 hours' 
                      AND v.date_of_death
ORDER BY v.date_of_death, e.timestamp;

For each victim, we're pulling every recorded event in the 48 hours leading up to their death. BETWEEN defines a time window, and PostgreSQL's INTERVAL lets us do date math naturally.

This is how real analysts work. Whether you're investigating a security breach, debugging a production outage, or solving a fictional murder - querying events within a time range is one of the most practical SQL skills you'll ever learn.

Step 4: Find the Pattern

Individual clues are useful. But patterns crack cases. Let's zoom out and look at aggregate data - who shows up suspiciously often near crime scenes?

SELECT r.first_name, r.last_name,
       COUNT(*) AS appearances_near_scenes,
       SUM(CASE WHEN e.location = cs.crime_location 
           THEN 1 ELSE 0 END) AS at_exact_scene
FROM town_residents r
JOIN town_events e 
  ON r.resident_id = e.resident_id
JOIN crime_scenes cs 
  ON e.timestamp BETWEEN cs.time_start AND cs.time_end
GROUP BY r.first_name, r.last_name
HAVING COUNT(*) >= 2
ORDER BY at_exact_scene DESC, appearances_near_scenes DESC;

GROUP BY collapses individual rows into summaries. COUNT(*) tallies how many times each person appears. HAVING filters those groups - we only want people near at least two crime scenes.

And that CASE WHEN inside the SUM? That's a conditional count - it specifically tallies appearances at the exact crime locations, not just nearby.

This is the moment in every detective story where the board of photos and red string comes together. In SQL, GROUP BY and HAVING are your red string.

Step 5: The Revelation

You've narrowed the suspects. You've followed the money. You've built the timeline. Now it's time for the final query - the one that ties it all together.

SELECT r.first_name, r.last_name, r.occupation,
       money.total_suspicious_amount,
       scenes.times_at_scene
FROM town_residents r
JOIN (
    SELECT account_id, 
           SUM(amount) AS total_suspicious_amount
    FROM bank_transactions
    WHERE recipient IN (
      'Eldritch Antiquities Ltd', 
      'Blackwater Holdings'
    )
    GROUP BY account_id
) money ON r.account_id = money.account_id
JOIN (
    SELECT resident_id, 
           COUNT(*) AS times_at_scene
    FROM town_events e
    JOIN crime_scenes cs 
      ON e.location = cs.crime_location
      AND e.timestamp BETWEEN cs.time_start 
                          AND cs.time_end
    GROUP BY resident_id
    HAVING COUNT(*) >= 2
) scenes ON r.resident_id = scenes.resident_id;

Subqueries. The final boss of SQL fundamentals. Each subquery in parentheses is its own mini-investigation:

• 1.The first finds people with suspicious financial ties to shell companies
• 2.The second finds people present at multiple crime scenes
• 3.The outer query connects them - revealing anyone who appears in both

If someone has been funneling money to shell companies AND was spotted at multiple murder scenes? Case closed.

What You Just Learned (Without Realizing It)

If you followed along with this investigation, you just covered the core of SQL:

That's not a beginner tutorial. That's a real analytical workflow - the same skills used to investigate fraud, analyze user behavior, optimize business operations, and build data pipelines. The only difference? You learned it by chasing a killer instead of reading documentation.

Why This Approach Works

I've spent years teaching SQL - in classrooms, through tutorials, one-on-one. The pattern is always the same: people learn fastest when they're solving a problem they care about.

Nobody gets excited querying a generic "employees" table for the hundredth time. But catching a killer? Cracking a conspiracy? Decoding financial fraud?

That makes you lean forward.

That's exactly why I built SQL Side Quest. Every concept from this post - SELECT, JOIN, GROUP BY, subqueries - you can actually solve in the game. Not as static exercises, but as a living investigation where your queries drive the story forward.

When I posted SQL Side Quest on Reddit, one comment perfectly captured it:

"Played the first mission and it was surprisingly fun. The right level of guidance to not feel totally overwhelmed."

That's the goal. Real SQL skills. Real engagement. No fluff.