Chapter 1 · Advanced Queries
Analytics Patterns: Growth, Cohorts, Retention and Gaps
- Page 4 of 22
- 17 min read
Some questions come up again and again, in analytics work and in SQL interviews: what is the second-highest salary? how much did revenue grow this month? of the users who joined in January, how many were still active in March? what is each user's longest streak? how many chat sessions did they have? Each has a standard shape built from the pieces you already know — CTEs, GROUP BY, joins and window functions. Learn the shapes once and you will recognise them everywhere.
They matter for AI work too. Retention and streaks are the labels and features of churn and engagement models, and sessionising an LLM chat log is the first step of every "how are people using our assistant?" analysis.
What you will learn
- Find the N-th highest value correctly, with ties, per group and when it does not exist
- Compute month-over-month growth, including months with no data
- Build a cohort retention table from a daily activity log
- Find consecutive-day streaks with the gaps-and-islands trick
- Split an event stream into sessions with
LAGand a runningSUM
Second-highest value
The classic interview question. There are several correct answers and one popular wrong one. The salaries are 250000, 180000, 160000, 120000 (twice), 110000 and 95000:
-- 1. The largest value below the maximum
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
-- 2. Distinct values, skip one
SELECT DISTINCT salary AS second_highest
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
-- 3. DENSE_RANK, which generalises to "N-th"
WITH ranked AS (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT DISTINCT salary AS second_highest FROM ranked WHERE rnk = 2;+----------------+
| second_highest |
+----------------+
| 180000 |
+----------------+
+----------------+
| second_highest |
+----------------+
| 180000 |
+----------------+
+----------------+
| second_highest |
+----------------+
| 180000 |
+----------------+All three say 180000. The wrong answer is ORDER BY salary DESC LIMIT 1 OFFSET 1 without DISTINCT: it returns the second row, which is only the second value when the top value is not tied. Ask yourself whether "second" means second person or second distinct amount; interviews usually mean the amount.
Per group, and when a group has no second value: rank with DENSE_RANK, then start from the list of departments and look up rank 2 with a scalar subquery, so every department stays in the result:
WITH ranked AS (
SELECT department, salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
FROM employees
),
departments AS (SELECT DISTINCT department FROM employees)
SELECT d.department,
(SELECT MAX(r.salary) FROM ranked AS r
WHERE r.department = d.department AND r.rnk = 2) AS second_highest,
(SELECT COUNT(*) FROM ranked AS r
WHERE r.department = d.department AND r.rnk = 2) AS people_at_it
FROM departments AS d
ORDER BY d.department;+-------------+----------------+--------------+
| department | second_highest | people_at_it |
+-------------+----------------+--------------+
| Data | 110000 | 1 |
| Engineering | 120000 | 2 |
| Management | NULL | 0 |
+-------------+----------------+--------------+Engineering's second-highest salary is 120000 and two people earn it; Management has only one salary, so the honest answer is NULL, not a missing row. That last detail is what interviewers look for.
Month-over-month growth
Growth is (this month − last month) / last month. LAG brings last month onto the same row, and NULLIF(prev, 0) turns a zero divisor into NULL instead of an error or a meaningless number:
WITH monthly AS (
SELECT strftime('%Y-%m', o.order_date) AS month,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY month
)
SELECT month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS growth_pct
FROM monthly
ORDER BY month;+---------+---------+-------+------------+
| month | revenue | prev | growth_pct |
+---------+---------+-------+------------+
| 2026-01 | 6600 | NULL | NULL |
| 2026-02 | 7000 | 6600 | 6.1 |
| 2026-03 | 21400 | 7000 | 205.7 |
| 2026-04 | 23450 | 21400 | 9.6 |
| 2026-05 | 8500 | 23450 | -63.8 |
| 2026-06 | 18100 | 8500 | 112.9 |
+---------+---------+-------+------------+The missing-month trap
Every month had some sale, so that was easy. Per category, months go missing — the Courses category sold nothing in January or April — and LAG then silently compares with the wrong month. The fix is a calendar: generate every month with a recursive CTE and LEFT JOIN the sales onto it. (For all categories at once, CROSS JOIN the months with the categories first, and join on both.)
WITH RECURSIVE months(month) AS (
SELECT '2026-01'
UNION ALL
SELECT strftime('%Y-%m', month || '-01', '+1 month') FROM months WHERE month < '2026-06'
),
sales AS (
SELECT strftime('%Y-%m', o.order_date) AS month, SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products AS p ON p.product_id = oi.product_id
WHERE o.status <> 'cancelled' AND p.category_id = 3 -- Courses
GROUP BY month
),
grid AS (
SELECT m.month, COALESCE(s.revenue, 0) AS revenue
FROM months AS m LEFT JOIN sales AS s ON s.month = m.month
)
SELECT month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS growth_pct
FROM grid
ORDER BY month;+---------+---------+-------+------------+
| month | revenue | prev | growth_pct |
+---------+---------+-------+------------+
| 2026-01 | 0 | NULL | NULL |
| 2026-02 | 3000 | 0 | NULL |
| 2026-03 | 15000 | 3000 | 400.0 |
| 2026-04 | 0 | 15000 | -100.0 |
| 2026-05 | 3000 | 0 | NULL |
| 2026-06 | 15000 | 3000 | 400.0 |
+---------+---------+-------+------------+Now April shows a real −100% drop and May's growth is NULL (growth from zero is undefined), instead of May being compared with March. On a server you would bucket by month with the dialect's own date functions — the window part does not change:
-- PostgreSQL: date_trunc buckets dates; generate_series builds the calendar
WITH months AS (
SELECT generate_series(DATE '2026-01-01', DATE '2026-06-01', INTERVAL '1 month')::date AS month
),
sales AS (
SELECT date_trunc('month', o.order_date)::date AS month, SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products AS p ON p.product_id = oi.product_id
WHERE o.status <> 'cancelled' AND p.category_id = 3
GROUP BY 1
)
SELECT to_char(m.month, 'YYYY-MM') AS month,
COALESCE(s.revenue, 0) AS revenue,
ROUND(100.0 * (COALESCE(s.revenue, 0) - LAG(COALESCE(s.revenue, 0)) OVER (ORDER BY m.month))
/ NULLIF(LAG(COALESCE(s.revenue, 0)) OVER (ORDER BY m.month), 0), 1) AS growth_pct
FROM months AS m
LEFT JOIN sales AS s ON s.month = m.month
ORDER BY m.month;+---------+---------+------------+
| month | revenue | growth_pct |
+---------+---------+------------+
| 2026-01 | 0 | NULL |
| 2026-02 | 3000 | NULL |
| 2026-03 | 15000 | 400.0 |
| 2026-04 | 0 | -100.0 |
| 2026-05 | 3000 | NULL |
| 2026-06 | 15000 | 400.0 |
+---------+---------+------------+In MySQL you would write DATE_FORMAT(order_date, '%Y-%m') for the bucket and a recursive CTE for the calendar.
A daily activity table to analyse
Cohorts, streaks and retention need many days of user activity, more than the shop has. This seeded Python block generates it: 30 learners on a learning app sign up between January and March 2026; each keeps coming back for a random number of days with random gaps. The seed makes the data identical on every machine:
import random
from datetime import date, timedelta
from sqlhelp import con, show
random.seed(7)
con.execute("DROP TABLE IF EXISTS activity")
con.execute("""CREATE TABLE activity (
user_id INTEGER NOT NULL,
active_on DATE NOT NULL,
PRIMARY KEY (user_id, active_on))""")
rows = []
for user in range(1, 31):
signup = date(2026, 1, 1) + timedelta(days=random.randrange(90))
stays_for = random.choice([5, 20, 40, 70, 150]) # days until they stop coming
day = signup
while day < date(2026, 5, 1) and (day - signup).days < stays_for:
rows.append((user, day.isoformat()))
day += timedelta(days=random.choice([1, 1, 1, 2, 4, 7]))
con.executemany("INSERT INTO activity VALUES (?, ?)", rows)
con.commit()
show("SELECT COUNT(*) AS rows, COUNT(DISTINCT user_id) AS users, "
"MIN(active_on) AS first_day, MAX(active_on) AS last_day FROM activity")+------+-------+------------+------------+
| rows | users | first_day | last_day |
+------+-------+------------+------------+
| 525 | 30 | 2026-01-03 | 2026-04-30 |
+------+-------+------------+------------+One row per user per active day: the shape of most real activity logs after deduplication (page views, API calls, lessons opened).
Cohort analysis and retention
A cohort is a group of users who started in the same period. Retention asks: of a cohort, what share was still active N months later? Comparing cohorts tells you whether the product is getting better at keeping people, which a single "active users" number hides.
Step 1 is the long form: each user's cohort month, and for each active month, how many months after the start it is (month_no):
WITH user_months AS (
SELECT DISTINCT user_id,
CAST(strftime('%Y', active_on) AS INTEGER) * 12
+ CAST(strftime('%m', active_on) AS INTEGER) AS month_idx
FROM activity
),
cohorts AS (
SELECT user_id, MIN(month_idx) AS cohort_idx FROM user_months GROUP BY user_id
)
SELECT c.cohort_idx,
um.month_idx - c.cohort_idx AS month_no,
COUNT(*) AS active_users
FROM user_months AS um
JOIN cohorts AS c ON c.user_id = um.user_id
GROUP BY c.cohort_idx, month_no
ORDER BY c.cohort_idx, month_no;+------------+----------+--------------+
| cohort_idx | month_no | active_users |
+------------+----------+--------------+
| 24313 | 0 | 12 |
| 24313 | 1 | 10 |
| 24313 | 2 | 7 |
| 24313 | 3 | 5 |
| 24314 | 0 | 9 |
| 24314 | 1 | 5 |
| 24314 | 2 | 3 |
| 24315 | 0 | 9 |
| 24315 | 1 | 7 |
+------------+----------+--------------+month_idx (year × 12 + month) turns months into consecutive integers, so "months between" is a subtraction. 24313 is January 2026. Step 2 pivots this into the familiar triangle with conditional aggregation, as percentages of the cohort size. A cohort cannot be measured for months that have not happened yet — data ends in April — so those cells must be NULL, not 0:
WITH user_months AS (
SELECT DISTINCT user_id,
CAST(strftime('%Y', active_on) AS INTEGER) * 12
+ CAST(strftime('%m', active_on) AS INTEGER) AS month_idx
FROM activity
),
cohorts AS (
SELECT user_id, MIN(month_idx) AS cohort_idx FROM user_months GROUP BY user_id
),
counts AS (
SELECT c.cohort_idx, um.month_idx - c.cohort_idx AS month_no, COUNT(*) AS users
FROM user_months AS um JOIN cohorts AS c ON c.user_id = um.user_id
GROUP BY c.cohort_idx, month_no
),
last AS (SELECT MAX(month_idx) AS last_idx FROM user_months)
SELECT printf('%d-%02d', (cohort_idx - 1) / 12, (cohort_idx - 1) % 12 + 1) AS cohort,
MAX(CASE WHEN month_no = 0 THEN users END) AS size,
ROUND(100.0 * MAX(CASE WHEN month_no = 1 THEN users END)
/ MAX(CASE WHEN month_no = 0 THEN users END), 0) AS m1_pct,
CASE WHEN cohort_idx + 2 <= last_idx THEN
ROUND(100.0 * COALESCE(MAX(CASE WHEN month_no = 2 THEN users END), 0)
/ MAX(CASE WHEN month_no = 0 THEN users END), 0) END AS m2_pct,
CASE WHEN cohort_idx + 3 <= last_idx THEN
ROUND(100.0 * COALESCE(MAX(CASE WHEN month_no = 3 THEN users END), 0)
/ MAX(CASE WHEN month_no = 0 THEN users END), 0) END AS m3_pct
FROM counts CROSS JOIN last
GROUP BY cohort_idx, last_idx
ORDER BY cohort_idx;+---------+------+--------+--------+--------+
| cohort | size | m1_pct | m2_pct | m3_pct |
+---------+------+--------+--------+--------+
| 2026-01 | 12 | 83.0 | 58.0 | 42.0 |
| 2026-02 | 9 | 56.0 | 33.0 | NULL |
| 2026-03 | 9 | 78.0 | NULL | NULL |
+---------+------+--------+--------+--------+Read it row by row: of the 12 January learners, 83% came back in February, 58% in March and 42% in April. The table is a triangle because later cohorts have had less time. The CASE WHEN cohort_idx + n <= last_idx guard is the difference between "nobody came back" and "we cannot know yet" — mixing those up makes new cohorts look terrible.
Gaps and islands: consecutive-day streaks
An island is a run of consecutive days; a gap is a break between runs. The trick: number each user's days with ROW_NUMBER() and subtract that from the date. Inside a run both go up by one per row, so the difference stays constant; after a gap it jumps. Look at learner 6:
SELECT active_on,
ROW_NUMBER() OVER (ORDER BY active_on) AS rn,
date(active_on, '-' || ROW_NUMBER() OVER (ORDER BY active_on) || ' days') AS island
FROM activity
WHERE user_id = 6
ORDER BY active_on;+------------+----+------------+
| active_on | rn | island |
+------------+----+------------+
| 2026-02-18 | 1 | 2026-02-17 |
| 2026-02-19 | 2 | 2026-02-17 |
| 2026-02-20 | 3 | 2026-02-17 |
| 2026-02-21 | 4 | 2026-02-17 |
| 2026-02-22 | 5 | 2026-02-17 |
| 2026-02-23 | 6 | 2026-02-17 |
| 2026-03-02 | 7 | 2026-02-23 |
| 2026-03-03 | 8 | 2026-02-23 |
| 2026-03-04 | 9 | 2026-02-23 |
| 2026-03-06 | 10 | 2026-02-24 |
+------------+----+------------+The island column is a label shared by every day of the same run. Group by it and you have the streaks:
WITH labelled AS (
SELECT user_id, active_on,
julianday(active_on)
- ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY active_on) AS island
FROM activity
),
streaks AS (
SELECT user_id, MIN(active_on) AS started, MAX(active_on) AS ended, COUNT(*) AS days
FROM labelled
GROUP BY user_id, island
)
SELECT user_id, started, ended, days
FROM streaks
WHERE days >= 6
ORDER BY days DESC, user_id;+---------+------------+------------+------+
| user_id | started | ended | days |
+---------+------------+------------+------+
| 29 | 2026-03-14 | 2026-03-22 | 9 |
| 30 | 2026-02-18 | 2026-02-26 | 9 |
| 20 | 2026-01-18 | 2026-01-24 | 7 |
| 21 | 2026-02-27 | 2026-03-05 | 7 |
| 6 | 2026-02-18 | 2026-02-23 | 6 |
| 26 | 2026-04-25 | 2026-04-30 | 6 |
+---------+------------+------------+------+The same pattern finds outage windows in monitoring data, runs of consecutive failed logins, or stretches of days where a model's accuracy stayed below target. In PostgreSQL the label is simply active_on - ROW_NUMBER() OVER (…)::int (a date minus an integer is a date); in MySQL, DATE_SUB(active_on, INTERVAL ROW_NUMBER() OVER (…) DAY).
Sessionisation
Raw events (page views, chat messages, API calls) have timestamps but no "session". The usual rule: a gap of more than 30 minutes starts a new session. That is gaps-and-islands with a threshold instead of "exactly one day", done in three steps — mark the gap with LAG, flag new sessions, then a running SUM of the flags numbers them. Here are messages sent to an AI study assistant:
CREATE TABLE chat_events (
user_id INTEGER NOT NULL,
sent_at TEXT NOT NULL
);
INSERT INTO chat_events (user_id, sent_at) VALUES
(1, '2026-05-04 09:00'), (1, '2026-05-04 09:05'), (1, '2026-05-04 09:20'),
(1, '2026-05-04 10:30'), (1, '2026-05-04 10:31'), (1, '2026-05-04 14:00'),
(2, '2026-05-04 09:10'), (2, '2026-05-04 09:50'), (2, '2026-05-04 09:55');
WITH gaps AS (
SELECT user_id, sent_at,
ROUND((julianday(sent_at)
- julianday(LAG(sent_at) OVER (PARTITION BY user_id ORDER BY sent_at))) * 24 * 60)
AS gap_min
FROM chat_events
),
flagged AS (
SELECT *, CASE WHEN gap_min IS NULL OR gap_min > 30 THEN 1 ELSE 0 END AS new_session
FROM gaps
)
SELECT user_id, sent_at, gap_min, new_session,
SUM(new_session) OVER (PARTITION BY user_id ORDER BY sent_at
ROWS UNBOUNDED PRECEDING) AS session_no
FROM flagged
ORDER BY user_id, sent_at;+---------+------------------+---------+-------------+------------+
| user_id | sent_at | gap_min | new_session | session_no |
+---------+------------------+---------+-------------+------------+
| 1 | 2026-05-04 09:00 | NULL | 1 | 1 |
| 1 | 2026-05-04 09:05 | 5.0 | 0 | 1 |
| 1 | 2026-05-04 09:20 | 15.0 | 0 | 1 |
| 1 | 2026-05-04 10:30 | 70.0 | 1 | 2 |
| 1 | 2026-05-04 10:31 | 1.0 | 0 | 2 |
| 1 | 2026-05-04 14:00 | 209.0 | 1 | 3 |
| 2 | 2026-05-04 09:10 | NULL | 1 | 1 |
| 2 | 2026-05-04 09:50 | 40.0 | 1 | 2 |
| 2 | 2026-05-04 09:55 | 5.0 | 0 | 2 |
+---------+------------------+---------+-------------+------------+Wrap that in one more CTE and GROUP BY user_id, session_no to get one row per session with its start, end, length and message count — the table behind "average session length" and "messages per session" on an assistant's dashboard.
Where this shows up
| Pattern | Typical question | Core tool |
|---|---|---|
| N-th highest | second-best model run, runner-up product | DENSE_RANK |
| Growth | MoM revenue, week-over-week token spend | LAG + calendar + NULLIF |
| Cohorts / retention | are newer users sticking around better? | MIN per user + conditional aggregation |
| Gaps and islands | streaks, outages, consecutive failures | date − ROW_NUMBER() |
| Sessions | sessions per user, session length | LAG + flag + running SUM |
Common mistakes
- "Second highest" without
DISTINCTor withRANK. Ties makeLIMIT 1 OFFSET 1return the top value again, andRANKskips numbers after ties sornk = 2may not exist. UseDENSE_RANKorDISTINCT. - Growth across missing periods.
LAGover only the months that had sales compares with the wrong month. Build the calendar andLEFT JOIN. - Dividing by zero or by
NULLsilently. SQLite and MySQL returnNULLforx / 0; PostgreSQL raisesdivision by zero. Say what you mean withNULLIF(prev, 0). - Zeros for months a cohort has not reached. Unobservable is not the same as churned. Guard with the last month in the data, as above.
- Gaps-and-islands on data with duplicates. Two rows for the same day give two row numbers and break the run. Deduplicate first (
SELECT DISTINCT user_id, active_on); here the primary key already guarantees it.
Try it yourself
- Easy: Find the third-highest distinct product price.
- Medium: Using
activity, count active users per month and compute month-over-month growth in active users (as a percentage, 1 decimal). - Hard: For each learner, find their longest streak in days; show the top 5 learners by longest streak (tie-break on
user_id).
Answers
-- Easy
WITH ranked AS (
SELECT price, DENSE_RANK() OVER (ORDER BY price DESC) AS rnk FROM products
)
SELECT DISTINCT price FROM ranked WHERE rnk = 3;-- Medium
WITH monthly AS (
SELECT strftime('%Y-%m', active_on) AS month, COUNT(DISTINCT user_id) AS users
FROM activity
GROUP BY month
)
SELECT month, users,
ROUND(100.0 * (users - LAG(users) OVER (ORDER BY month))
/ NULLIF(LAG(users) OVER (ORDER BY month), 0), 1) AS growth_pct
FROM monthly
ORDER BY month;-- Hard
WITH labelled AS (
SELECT user_id,
julianday(active_on) - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY active_on) AS island
FROM activity
),
streaks AS (
SELECT user_id, COUNT(*) AS days FROM labelled GROUP BY user_id, island
)
SELECT user_id, MAX(days) AS longest_streak
FROM streaks
GROUP BY user_id
ORDER BY longest_streak DESC, user_id
LIMIT 5;Summary
- N-th highest:
DENSE_RANK(orDISTINCT … LIMIT 1 OFFSET n-1); decide what ties mean and returnNULLwhen there is no answer. - Growth:
LAGover a complete calendar, withNULLIFguarding the division. - Cohorts: first period per user, months since start, then pivot with conditional aggregation; leave unobservable cells
NULL. - Islands: date minus
ROW_NUMBER()is constant within a run; group by it. - Sessions:
LAGgap → new-session flag → runningSUMof flags.
Next: Database Design: Keys, ER Diagrams and Normalization steps back from querying to designing: how to turn requirements into tables, choose keys and normalise a messy table so that queries like these stay correct.