Go beyond the basics with subqueries, CTEs, window functions and the analytics patterns analysts use every day, practised in your browser.

SQL for Analysis

## Why it matters

Total active users can rise every month while the product is quietly failing: if marketing brings in lots of new people who all leave after a week, the headline number hides it. **Cohort analysis** groups users by when they started (their cohort) and tracks what share are still active one, two, three months later. The resulting **retention table** is one of the most requested analyses at subscription, app and e-commerce companies, and it is a frequent interview question for analyst roles.

## The shape of a retention table

| cohort_month | month 0 | month 1 | month 2 |
|---|---|---|---|
| 2026-01 (4 users) | 100% | 75% | 50% |
| 2026-02 (3 users) | 100% | 67% | |
| 2026-03 (2 users) | 100% | | |

Read across a row to see how one cohort decays. Read down a column to see whether newer cohorts retain better than older ones (did last month's onboarding change help?). The triangle shape is normal: newer cohorts have not had time to reach month 2 yet.

In SQL it is easier to produce the **long** form, one row per cohort and month number, and let a spreadsheet or BI tool pivot it:

| cohort_month | month_number | active_users | retention_pct |
|---|---|---|---|
| 2026-01 | 0 | 4 | 100.0 |
| 2026-01 | 1 | 3 | 75.0 |

## Step 1: assign each user to a cohort

Usually the cohort is the month of the user's first event: signup, first purchase or first login. If it is not stored, derive it:

```sql
SELECT user_id, MIN(substr(order_date, 1, 7)) AS cohort_month
FROM orders
GROUP BY user_id;
```

`substr(date, 1, 7)` turns `'2026-01-15'` into `'2026-01'`. (PostgreSQL would use `date_trunc('month', order_date)`.)

## Step 2: measure months since the cohort

To subtract months, turn `YYYY-MM` into a single month count: year × 12 + month.

```sql
(CAST(substr(activity_month, 1, 4) AS INTEGER) * 12 + CAST(substr(activity_month, 6, 2) AS INTEGER))
- (CAST(substr(signup_month, 1, 4) AS INTEGER) * 12 + CAST(substr(signup_month, 6, 2) AS INTEGER))
```

January 2026 to March 2026 gives `(2026*12+3) - (2026*12+1) = 2`, and the formula works across year boundaries (December to January is 1). This `month_number` is the column header of the retention table.

## Step 3: count distinct users, divide by cohort size

Put it together with CTEs:

```sql
WITH cohort_size AS (
  SELECT signup_month, COUNT(*) AS users
  FROM users
  GROUP BY signup_month
),
user_months AS (
  SELECT DISTINCT u.id AS user_id, u.signup_month,
         (CAST(substr(a.activity_month, 1, 4) AS INTEGER) * 12 + CAST(substr(a.activity_month, 6, 2) AS INTEGER))
       - (CAST(substr(u.signup_month, 1, 4) AS INTEGER) * 12 + CAST(substr(u.signup_month, 6, 2) AS INTEGER)) AS month_number
  FROM users AS u
  JOIN activity AS a ON a.user_id = u.id
)
SELECT m.signup_month AS cohort_month,
       m.month_number,
       COUNT(*) AS active_users,
       ROUND(COUNT(*) * 100.0 / s.users, 1) AS retention_pct
FROM user_months AS m
JOIN cohort_size AS s ON s.signup_month = m.signup_month
GROUP BY m.signup_month, m.month_number, s.users;
```

Key points:

- **`DISTINCT` in `user_months`** means a user who was active 20 times in February counts once for February. Retention counts people, not events.
- **The denominator is the full cohort**, from `users`, not just the users who appear in `activity`. Someone who signed up and never came back must still be in the denominator, or retention looks better than it is.
- **`* 100.0`** avoids integer division.

## Definitions are business decisions

Before writing the query, agree with the stakeholder on:

- **What starts a cohort:** signup, first purchase, or the first paid month?
- **What counts as active:** any login, a purchase, or a key action like "created a project"?
- **Classic vs rolling retention:** classic counts users active **in** month N; rolling ("unbounded") counts users active in month N **or later**. They answer different questions, and they give different numbers.
- **Period length:** weekly cohorts suit apps with daily use; monthly or quarterly suit slower purchase cycles.

Write the chosen definitions as comments at the top of the query so the next reader knows what the numbers mean.

## Common mistakes

- **Counting events instead of users** (forgetting `DISTINCT`).
- **Using only active users as the denominator**, which hides churn.
- **Comparing a young cohort's month 2 with an old cohort's month 2** when the young cohort's month 2 is not finished yet. Exclude the current, partial month.
- **Mixing time zones** at month boundaries when timestamps are in UTC but the business reports in local time.
- **Subtracting `'2026-03' - '2026-01'`** as text. Convert to the year × 12 + month number first.

## Quick recap

- A cohort is a group of users who started in the same period; retention = active users in period N / cohort size.
- Build it in steps: cohort per user, month number per activity, distinct count, divide by cohort size.
- Produce the long form in SQL and pivot it in a BI tool.
- Agree on the definitions first and write them in the query.

## Try it

Open the **Do** tab and build the long-form retention table for three monthly cohorts.


Group users by when they started and measure what share stay active in later months with a retention table.

Cohort and retention queries

How to Calculate Churn & Retention with Cohort Analysis (SQL + Google Sheets)

Cohort Retention analysis with SQL | Data Analyst Project

## Why it matters

A **funnel** is a sequence of steps users are meant to pass through: visit the site, sign up, add to cart, purchase. At each step some people drop out. Funnel analysis counts how many make it through each step and where the biggest drop-off is, which tells product and marketing teams where to focus. A 2-point improvement at the weakest step is often worth more than doubling traffic.

## Start from an event log

Funnels are usually built from an **event table**: one row per thing a user did.

| user_id | event_name | event_time |
|---|---|---|
| 1 | visit | 2026-06-01 09:00 |
| 1 | signup | 2026-06-01 09:05 |
| 1 | purchase | 2026-06-02 10:00 |
| 2 | visit | 2026-06-01 11:00 |
| 2 | visit | 2026-06-03 08:00 |
| 6 | purchase | 2026-06-04 09:00 |

Notice two realistic problems: user 2 visited twice, and user 6 purchased without any recorded visit or signup (maybe they came from an app, or tracking failed). A good funnel query handles both.

## Step 1: one row per user with step flags

Pivot the events into one row per user, with a 1/0 flag per step. `MAX(CASE ...)` is the standard trick: the flag is 1 if **any** of the user's rows match.

```sql
SELECT user_id,
       MAX(CASE WHEN event_name = 'visit'    THEN 1 ELSE 0 END) AS did_visit,
       MAX(CASE WHEN event_name = 'signup'   THEN 1 ELSE 0 END) AS did_signup,
       MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS did_purchase
FROM events
GROUP BY user_id;
```

Duplicates disappear, because `MAX` of several 1s is still 1. This is why funnels count **distinct users**, never event rows.

## Step 2: enforce the step order

In a strict funnel, a user only counts at a step if they passed every earlier step. Multiplying the flags does that neatly: `did_visit * did_signup` is 1 only when both are 1.

```sql
WITH user_steps AS ( ...step 1... ),
funnel AS (
  SELECT SUM(did_visit)                             AS visitors,
         SUM(did_visit * did_signup)                AS signups,
         SUM(did_visit * did_signup * did_purchase) AS purchasers
  FROM user_steps
)
SELECT visitors, signups, purchasers,
       ROUND(signups * 100.0 / visitors, 1)    AS signup_rate_pct,
       ROUND(purchasers * 100.0 / signups, 1)  AS purchase_rate_pct,
       ROUND(purchasers * 100.0 / visitors, 1) AS overall_conversion_pct
FROM funnel;
```

User 6, who purchased with no visit, is excluded from every step. Whether that is right is a business decision: a "loose" funnel would count each step independently. Say which one you used.

## Step conversion vs overall conversion

Report both:

- **Step conversion:** signups / visitors, purchasers / signups. Shows **where** people drop.
- **Overall conversion:** purchasers / visitors. Shows the end-to-end result.

With 9 visitors, 5 signups and 3 purchasers, step conversion is 55.6% then 60.0%, and overall conversion is 33.3%. The visit-to-signup step loses the most people, so that is where to look first.

## Adding time: did it happen in order, within a window?

Flags ignore timing. A user who purchased on Monday and signed up on Friday would pass the strict funnel above. When order and timing matter, compare timestamps per user:

```sql
WITH firsts AS (
  SELECT user_id,
         MIN(CASE WHEN event_name = 'visit'  THEN event_time END) AS first_visit,
         MIN(CASE WHEN event_name = 'signup' THEN event_time END) AS first_signup
  FROM events
  GROUP BY user_id
)
SELECT COUNT(first_visit) AS visitors,
       SUM(CASE WHEN first_signup >= first_visit
                 AND julianday(first_signup) - julianday(first_visit) <= 7
                THEN 1 ELSE 0 END) AS signed_up_within_7_days
FROM firsts;
```

`MIN(CASE ... THEN event_time END)` returns the first time each step happened (the `CASE` gives `NULL` for other events, and `MIN` ignores `NULL`). `COUNT(first_visit)` counts only users who have a visit. Text timestamps in `YYYY-MM-DD HH:MM` form compare correctly as strings, which is why ISO dates are so useful.

## Segmenting the funnel

Once the per-user CTE exists, add a dimension to compare groups: join to `users` and `GROUP BY` device, country or traffic source. A funnel that is fine on desktop but collapses on mobile points straight to a bug or a design problem.

## Common mistakes

- **Counting events, not users.** Repeat visits inflate the top of the funnel and make conversion look worse.
- **Not enforcing step order** when the stakeholder expects a strict funnel, or enforcing it when they expect independent counts.
- **Dividing by the wrong base.** Be explicit whether a rate is "of previous step" or "of visitors".
- **Integer division** in rates: use `100.0`.
- **Mixing time periods:** visitors from June and purchases from July in the same funnel. Fix a cohort window first.

## Quick recap

- Turn the event log into one row per user with `MAX(CASE ...)` flags.
- Multiply flags to require earlier steps; sum them to count users per step.
- Report step conversion and overall conversion.
- Use first-event timestamps when order or a time limit matters.

## Try it

Open the **Do** tab and compute a strict visit -> signup -> purchase funnel from an event log.


Count distinct users through each step of a conversion funnel and find where people drop out.

Funnels

#4 SQL Funnel Analysis Interview Question Explained Step by Step

## Why it matters

Every analysis inherits the problems of its data. A duplicated order inflates revenue, a negative quantity hides returns inside sales, a missing customer id drops rows out of a join, and a date in the future breaks a "last 30 days" filter. None of these cause an error: the query runs and the number is simply wrong. Experienced analysts run **data quality checks** before trusting a table, and data teams schedule the same checks to run after every load. Each check is a query that should return zero problem rows.

## The main kinds of check

| Check | Question | Typical query |
|---|---|---|
| Completeness | Are required values present? | `WHERE col IS NULL OR col = ''` |
| Uniqueness | Is the key really unique? | `GROUP BY key HAVING COUNT(*) > 1` |
| Validity | Are values in the allowed range or list? | `WHERE amount < 0`, `WHERE status NOT IN (...)` |
| Referential integrity | Does every foreign key match a row? | `LEFT JOIN ... WHERE parent.id IS NULL` |
| Timeliness and range | Are dates plausible? | `WHERE order_date > date('now')` |
| Consistency | Do related values agree? | `WHERE shipped_at < ordered_at` |
| Volume | Did the expected number of rows arrive? | `COUNT(*)` per load date vs the usual |

## Worked examples

**Completeness.** Empty strings often hide where `NULL` checks do not look:

```sql
SELECT COUNT(*) AS missing_email
FROM orders
WHERE email IS NULL OR TRIM(email) = '';
```

**Uniqueness.** List the duplicated keys and how often they appear:

```sql
SELECT order_id, COUNT(*) AS copies
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;
```

To count the **extra** rows in one number: `SELECT COUNT(*) - COUNT(DISTINCT order_id) FROM orders`. An id that appears three times contributes two extra rows. (Note that `COUNT(DISTINCT ...)` ignores `NULL`, so check for `NULL` keys separately.)

**Validity.** Values outside an allowed list:

```sql
SELECT status, COUNT(*) AS n
FROM orders
WHERE status NOT IN ('pending', 'shipped', 'cancelled', 'refunded')
   OR status IS NULL
GROUP BY status;
```

The `OR status IS NULL` matters: `NULL NOT IN (...)` is not true, so `NULL` rows would otherwise slip through.

**Referential integrity.** Orders whose customer does not exist (an "orphan"):

```sql
SELECT o.id, o.customer_id
FROM orders AS o
LEFT JOIN customers AS c ON c.id = o.customer_id
WHERE c.id IS NULL;
```

**Consistency between columns:**

```sql
SELECT id FROM shipments WHERE delivered_at < shipped_at;
```

## One query, many checks

Running checks one at a time is slow to review. Combine them with `UNION ALL` into a single report with one row per check:

```sql
SELECT 'missing_email' AS check_name, COUNT(*) AS failing_rows
FROM orders WHERE email IS NULL OR email = ''
UNION ALL
SELECT 'negative_amount', COUNT(*)
FROM orders WHERE amount < 0
UNION ALL
SELECT 'orphan_customer', COUNT(*)
FROM orders AS o LEFT JOIN customers AS c ON c.id = o.customer_id
WHERE c.id IS NULL;
```

Because each part uses `COUNT(*)` with no `GROUP BY`, every check returns exactly one row, even when it finds nothing. That is what you want: a row showing `0` proves the check ran. Tools such as dbt tests and Great Expectations are built on the same idea, turning checks like these into automatic tests that fail a pipeline when the count is not zero.

## Find, then decide

A check tells you something is wrong; it does not tell you what to do. Before you filter rows out:

1. **Look at examples.** Change `COUNT(*)` to `SELECT *` and read 10 failing rows.
2. **Ask the data owner.** A negative amount might be a refund that is valid in this system.
3. **Fix at the source if you can**, rather than patching every report.
4. **If you must exclude rows in your analysis, say so** in the report, with the count ("12 duplicate orders removed").

Never silently "clean" data in a way that changes a business number without telling anyone.

## Profiling a new table

When you first meet a table, a quick profile catches most issues:

```sql
SELECT COUNT(*)                        AS row_count,
       COUNT(DISTINCT order_id)        AS distinct_ids,
       SUM(email IS NULL)              AS null_emails,
       MIN(amount), MAX(amount),
       MIN(order_date), MAX(order_date)
FROM orders;
```

In SQLite a comparison such as `email IS NULL` returns 1 or 0, so `SUM(...)` counts matches. In PostgreSQL write `COUNT(*) FILTER (WHERE email IS NULL)` or `SUM(CASE WHEN ... THEN 1 ELSE 0 END)`.

## Common mistakes

- **Forgetting empty strings and whitespace** in completeness checks.
- **`NOT IN` with `NULL`s**, which lets bad rows through.
- **Using `INNER JOIN` to find orphans.** An inner join hides them; use `LEFT JOIN ... IS NULL`.
- **Checks that return no row when everything is fine**, so you cannot tell "passed" from "did not run".
- **Hard-coding today's date** in a scheduled check; use the load date or `date('now')`.

## Quick recap

- Check completeness, uniqueness, validity, referential integrity, date ranges and consistency.
- Each check is a query that should return 0 problems; combine them with `UNION ALL`.
- Investigate and document before excluding rows.

## Try it

Open the **Do** tab and write a four-check quality report for a messy `orders` table.


Write queries that catch missing values, duplicates, invalid values and orphan rows before they reach a report.

Data quality checks

SQL Data Quality Checks Every Data Engineer Must Know

Build the queries analysts are asked for most: cohort retention tables, conversion funnels and data quality checks.