InsideEdgeInsideEdge
Download App
Stop 04 · SQL & DatabasesProjects · 1/1

SQL Case Study for a Data Science Problem

Part of the Data Science Roadmap

In short

Take one realistic business question and answer it end to end in SQL. A clear case study like this shows you can think like a data scientist, not just write queries.

Apply SQL to build an actual training-ready dataset — pulling, joining and aggregating raw tables into a clean, feature-ready table for a hypothetical ML problem.

The mascot at a gym entrance checks an attendance register next to a rack of dumbbells.

Think of it like this

The gym owner's January question

Every January the gym fills up with new members. The owner wants to know how many January joiners still show up in March, and whether February joiners stick around better. That's cohort retention, and it's a classic SQL case study.

Watch it happen

Your build plan

Question
Schema
Clean
Metrics
Cohorts
Story

1/6Pick one question, like 'are customers who joined after our app redesign staying longer?'

The big ideas

What you need to know

One sharp question

A focused question beats ten loose ones. It keeps the queries and the story tight.

Define metrics clearly

Write exactly what 'active' or 'retained' means. Interviewers will ask.

Use CTEs for steps

WITH blocks split a long query into named, readable steps you can check one at a time.

Show your checks

Include sanity queries, like row counts and duplicates. It proves you trust your numbers for a reason.

See it in code

Walk through the code

sql
1WITH firsts AS (2  SELECT customer_id, DATE_TRUNC('month', MIN(order_date)) AS cohort3  FROM orders GROUP BY customer_id4),5activity AS (6  SELECT DISTINCT o.customer_id, f.cohort,7         DATE_TRUNC('month', o.order_date) AS active_month8  FROM orders o JOIN firsts f USING (customer_id)9)10SELECT cohort, active_month, COUNT(*) AS customers11FROM activity12GROUP BY cohort, active_month13ORDER BY cohort, active_month;

1/3For each customer, find the month of their first order. That month is their cohort.

The heart of a cohort analysis in PostgreSQL. Divide each count by the cohort's first-month size to get retention rates.

Save yourself the pain

Common mistakes

Oops

Starting with complex queries before checking the data.

Do this instead

First run counts, duplicates and date ranges. A surprise there changes the whole analysis.

Oops

Ending with a giant table and no conclusion.

Do this instead

Finish with two or three plain findings and one recommendation a manager could act on.

Your mission

First milestone

~45 min

Quick check

In a cohort analysis, what defines which cohort a customer belongs to?

Go deeper

  1. 1Pick a hypothetical prediction problem (e.g. churn) and define what features you'd need.
  2. 2Write the SQL to pull and aggregate those features from a multi-table sample dataset.
  3. 3Document your query logic so someone else could reproduce the same feature set.

Stuck on this topic? Ask an Insider

Get 1:1 guidance from people who've walked this exact path — on the InsideEdge app.

Download
InsideEdge