You've been handed read access to a production database and a question: 'How is retention trending?' No data dictionary, no notebook — the warehouse IS your exploration environment. Analysts at most companies spend more time exploring data in SQL than in pandas, because that's where the data lives. By the end of this lesson you'll profile any unknown table in five queries, and build the two workhorse analyses of product analytics: cohort retention and conversion funnels.
What you'll learn
What
SQL for EDA (Exploratory Data Analysis) is the practice of using queries to understand a dataset before analysing it: profiling row counts, NULL rates, and distinct values; bucketing distributions; and composing CTEs (Common Table Expressions) into cohort and funnel analyses that answer product questions directly in the warehouse.
Why
Data at real companies lives in warehouses measured in terabytes — you can't download it into a DataFrame. Exploring where the data lives means no sampling bias, no stale exports, and results every teammate can re-run. Without profiling first, your aggregates silently inherit every NULL, duplicate, and orphan key in the table.
Where it's used
Every analytics team profiles new tables in SQL before trusting them. Cohort retention and funnel queries are the daily bread of product analysts at every subscription and marketplace business, and both are among the most common SQL interview questions.
Where this runs in production
Growth analysts group new listeners by signup month and track what share stream music in month 1, 2, 3 — the retention matrix that tells them whether onboarding changes actually keep people listening.
Search → listing view → booking request → confirmed stay. Funnel queries over event tables show where guests drop off, and every experiment is judged by how it moves those step-to-step conversion rates.
Before any revenue analysis, analysts profile the charges table: NULL rates on payment method, distinct currencies, amount ranges — because a single unprofiled NULL-heavy column can silently understate totals in downstream reports.
SELECT COUNT(*) AS total_rows, COUNT(email) AS email_present, COUNT(*) - COUNT(email) AS email_nulls, COUNT(DISTINCT customer_id) AS distinct_customers, MIN(created_at) AS first_row, MAX(created_at) AS last_row FROM customers;
The single most useful profiling query. COUNT(*) counts rows; COUNT(email) counts only non-NULL values — so the difference is the NULL count. COUNT(DISTINCT customer_id) versus COUNT(*) tells you whether the column is a true key: if the numbers differ, the table has duplicate customer rows and every join against it can fan out.
A pilot walks around the aircraft before every flight — checking flaps, tyres, fuel — not because failures are likely, but because the cost of finding one mid-flight is enormous. Profiling queries are your walk-around: cheap checks on row counts, NULLs, and key uniqueness before you 'take off' into aggregations. The limitation of the analogy: aircraft rarely change between flights, but tables change daily, so you re-profile every time the stakes are high.
1) Row count: COUNT(*). 2) NULL rate per important column: COUNT(*) - COUNT(col). 3) Key uniqueness: COUNT(DISTINCT id) = COUNT(*)? 4) Value ranges: MIN/MAX on dates and amounts — negative amounts and future dates are the classic surprises. 5) Category inventory: GROUP BY on low-cardinality columns to spot casing variants like 'Web' vs 'web'.
A cohort is a group of users who share a starting event in the same period — usually 'signed up in the same month'. Cohort analysis compares behaviour across cohorts at the same relative age: what share of the January cohort was still active in its second month, versus the February cohort in its second month? DATE_TRUNC('month', signup_date) assigns the cohort; the month difference between an activity date and the cohort month is the cohort age.
A funnel is an ordered sequence of steps users move through — visit → signup → first purchase. The funnel query counts DISTINCT users at each step, and the conversion rate between steps is the ratio of adjacent counts. Two rules keep funnels honest: count distinct users, not events (one user can view a page ten times), and require the steps to happen in order (a purchase before a signup is a data problem, not a conversion).
Every exploratory result needs a reconciliation check against a number you already trust. Funnel step 1 should equal total distinct visitors from the raw table. Every cohort's month-0 retention must be 100% by construction — if it isn't, your join is wrong. The sum of bucket counts must equal the table's row count. A query that runs without errors is not a query that is right.
Click each stage — every exploration session moves left to right, and every answer gets reconciled before it ships.
You've just been granted access to an orders table at a subscription-box company. Before anything else: the walk-around.
First check: is order_id a true key? If total_rows is 5,000 and distinct_orders is 4,988, twelve rows are duplicates and every SUM you run will double-count them. Here both return 5000 — the key is clean.
Your task
A meal-kit subscription service gives you its orders table (order_id, customer_id, amount, order_date). Profile it, then build a two-month cohort check: how many customers placed their first order each month, and how many of those came back the following month.
total_rows | distinct_customers | null_amounts
620 | 240 | 3
cohort_month | cohort_size | retained_m1
2026-04-01 | 130 | 52
2026-05-01 | 110 | 41Write your solution in the editor on the right, then hit Run.
A table has 10,000 rows. SELECT COUNT(email) FROM users returns 9,200. What does this tell you?
In a cohort retention matrix, why is every cohort's month-0 retention 100% by construction?
A funnel query counts 5,000 'add to cart' EVENTS but the analyst reports 5,000 users reached the cart step. What's the flaw?
What should you tell them?
The events table has (user_id, event_type, event_time) with event types 'view' and 'signup'. Write a query returning three columns: viewers (distinct users with a view), signers (distinct users with a signup AFTER their first view), and the conversion rate as a percentage rounded to 1 decimal. Use CTEs.
Where did the 90 missing rows most likely go, and what is bucket 5?
You get read access to a production table you've never seen. Walk me through how you'd profile it before running any analysis.
I run a fixed checklist before trusting anything. First, size and key integrity: COUNT(*) versus COUNT(DISTINCT id) — if they differ, the table has duplicates and every join and SUM downstream is at risk. Second, NULL rates on the columns I'll use, with the COUNT(*) minus COUNT(col) idiom, because a NULL-heavy column silently shrinks aggregates and drops rows from GROUP BYs. Third, ranges: MIN and MAX on dates and amounts, hunting for negative amounts, zero-value placeholders, and future dates. Fourth, category inventories: GROUP BY on low-cardinality columns to catch casing and whitespace variants that would split groups. Finally I reconcile one number against something already trusted — a dashboard total or a known row count — because matching an external reference is the fastest way to validate my understanding of the table's grain. The whole ritual takes five queries and a few minutes, and it converts silent errors into visible ones.
Design a query that produces monthly cohort retention for a subscriptions product. What are the pieces, and what's the classic mistake?
Three CTEs. The first assigns each user a cohort: DATE_TRUNC('month', MIN(activity_date)) grouped by user — the month of their first activity. The second builds a distinct (user, active_month) table, using DISTINCT so multiple events in a month count once. The third joins them and computes cohort age as the month difference between active month and cohort month, handling year boundaries with year*12 + month arithmetic. The final SELECT groups by cohort month and pivots ages into columns with COUNT(DISTINCT user_id) FILTER (WHERE age = n). I validate with the built-in check: month-0 must be 100% of cohort size by construction, because the first activity both defines the cohort and counts as activity. The classic mistake is reading the newest cohort's near-zero retention as a collapse when its follow-up window hasn't finished — if data ends June 30, the June cohort's month 1 hasn't happened. I mask incomplete cells rather than report them.
Before you send an exploratory result to a stakeholder, how do you validate it?
Three layers. First, internal reconciliation: parts must sum to wholes — bucket counts to the row count, funnel step 1 to total distinct users, segment revenues to total revenue. Any gap means a filter or join is losing rows somewhere. Second, external reconciliation: I compare one headline number against an independent source — the finance dashboard, a previous report, a back-of-envelope estimate — because two independent paths agreeing is far stronger evidence than one clean-looking query. Third, a plausibility pass: does a 4% retention month follow four 40% months? Did revenue triple in a week? Numbers that would be surprising if true deserve suspicion proportional to their surprise, and most 'amazing findings' at this stage are joins that fanned out or windows that were incomplete. Only after all three do I write the finding — and I ship it with its caveats attached, because a stakeholder who later discovers an undisclosed limitation stops trusting every number after that.
Common Mistakes to Avoid
1) Aggregating before profiling — a table with duplicate keys or NULL-heavy columns corrupts every downstream number, and profiling costs one query. 2) Counting events instead of distinct users in funnels: one user's ten page views are not ten users. 3) Reading the newest cohort's low retention as a collapse when its follow-up window simply hasn't finished yet. 4) Writing 100 * count_a / count_b with integers — integer division truncates to 0; use 100.0. 5) Forgetting width_bucket's overflow buckets: values outside [low, high) land in bucket 0 or n+1, and ignoring them means your histogram silently drops rows — always reconcile bucket sums against the row count.
Ask the AI Tutor
Try these prompts in the AI Tutor panel: • 'Give me a mystery table schema and quiz me on which profiling queries to run first.' • 'Walk through the cohort retention query one CTE at a time and check my explanation of each.' • 'My funnel shows more signups than visitors — help me debug the possible causes.' • 'Generate a funnel exercise with a planted step-ordering bug and let me find it.' • 'Interview mode: ask me to design a retention query out loud and grade my answer.'
Glossary
EDA (Exploratory Data Analysis) — understanding a dataset's shape and quality before analysing it. Profiling — the opening queries that measure row counts, NULL rates, key uniqueness, and value ranges. NULL rate — the share of rows where a column has no value; COUNT(*) - COUNT(col). Cohort — a group of users who share a starting event in the same period, usually signup month. Cohort age — how many periods after joining an activity occurred. Retention matrix — cohorts as rows, ages as columns, active-user counts (or rates) as cells. Funnel — an ordered sequence of steps with distinct-user counts at each, revealing where users drop off. Conversion rate — the ratio of users at one funnel step to the previous step. CTE (Common Table Expression) — a named intermediate result introduced with WITH, chaining analysis steps readably. width_bucket — PostgreSQL's function assigning a value to one of n equal-width buckets, with 0 and n+1 reserved for out-of-range values. DATE_TRUNC — snaps a timestamp down to a period boundary ('month', 'week'), the standard cohort and trend grouping tool. Reconciliation — checking a result against a known total before trusting it.
Recommended Resources
• Docs: PostgreSQL's aggregate functions page (FILTER clause) and the width_bucket entry under mathematical functions — the two tools this lesson leans on hardest. • Read: any public write-up of cohort analysis from a growth team (Reforge and Amplitude publish good ones) and map their charts back to the three-CTE skeleton you built here. • Practice: load a public dataset into a local Postgres or an online SQL playground and run the full profiling checklist before anything else — make the walk-around automatic. • Next in DSM: the capstone — a complete business analysis in pure SQL where these profiling, cohort, and funnel patterns answer real stakeholder questions end to end.
Recap
✓ Profile before you analyse: COUNT(*) vs COUNT(DISTINCT id) for key integrity, COUNT(*) - COUNT(col) for NULL rates, MIN/MAX for range surprises. ✓ Distributions in SQL are histograms you build yourself — width_bucket assigns buckets, and bucket counts must reconcile to the row count. ✓ Cohorts group users by first-activity month (DATE_TRUNC + MIN); the retention matrix counts distinct users per (cohort, age) cell. ✓ Month-0 retention is 100% by construction — a free correctness check on your join. ✓ Funnels chain CTEs, count DISTINCT users per step, and enforce step order with time conditions. ✓ The newest cohort's follow-up window is always incomplete — mask it, don't report it as a collapse. ✓ Nothing leaves the warehouse unreconciled: parts sum to wholes, and one number matches an external reference. Next up: 🏗 Project: Business Analysis in SQL. Everything in this course converges — you'll take a five-table e-commerce database and a set of stakeholder questions, and answer them end to end in pure SQL.
Run your code to see the output here.