Analytical SQL Patterns

A few patterns answer a large share of real analytics questions. They combine CTEs, joins, and window functions from earlier lessons.

Pivoting: rows into columns

SELECT
  p.category,
  SUM(CASE WHEN o.status = 'delivered' THEN oi.quantity ELSE 0 END) AS delivered,
  SUM(CASE WHEN o.status = 'shipped'   THEN oi.quantity ELSE 0 END) AS shipped,
  SUM(CASE WHEN o.status = 'pending'   THEN oi.quantity ELSE 0 END) AS pending
FROM order_items oi
JOIN orders o   ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
GROUP BY p.category
ORDER BY p.category;

+-------------+-----------+---------+---------+
|  category   | delivered | shipped | pending |
+-------------+-----------+---------+---------+
| Accessories |         5 |       2 |       1 |
| Audio       |         0 |       2 |       0 |
| Displays    |         3 |       0 |       0 |
| Video       |         0 |       0 |       3 |
+-------------+-----------+---------+---------+

Conditional aggregation turns status values into columns — a cross-tabulation, like a spreadsheet pivot table.

Cohorts: customers by signup month

WITH first_orders AS (
  SELECT customer_id, MIN(ordered_on) AS first_order
  FROM orders
  GROUP BY customer_id
)
SELECT
  STRFTIME('%Y-%m', c.joined_on) AS signup_month,
  COUNT(*)                       AS customers,
  COUNT(f.customer_id)           AS ordered,
  ROUND(100.0 * COUNT(f.customer_id) / COUNT(*), 0) AS pct_ordered
FROM customers c
LEFT JOIN first_orders f ON f.customer_id = c.id
GROUP BY signup_month
ORDER BY signup_month;

+--------------+-----------+---------+-------------+
| signup_month | customers | ordered | pct_ordered |
+--------------+-----------+---------+-------------+
| 2024-01      |         1 |       1 |       100.0 |
| 2024-03      |         2 |       2 |       100.0 |
| 2024-06      |         1 |       1 |       100.0 |
| 2024-09      |         1 |       1 |       100.0 |
| 2025-01      |         1 |       0 |         0.0 |
+--------------+-----------+---------+-------------+

Grouping customers by when they joined, then measuring what they did later, is the basis of retention analysis.

Gaps and islands: consecutive streaks

Find runs of consecutive days — for example, a user's login streaks:

-- standalone
CREATE TABLE logins (user_id INTEGER, day DATE);
INSERT INTO logins VALUES
  (1, '2025-03-01'), (1, '2025-03-02'), (1, '2025-03-03'),
  (1, '2025-03-07'), (1, '2025-03-08'),
  (1, '2025-03-15');

WITH numbered AS (
  SELECT user_id, day,
    JULIANDAY(day) - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day) AS grp
  FROM logins
)
SELECT user_id, MIN(day) AS streak_start, MAX(day) AS streak_end, COUNT(*) AS days
FROM numbered
GROUP BY user_id, grp
ORDER BY streak_start;

+---------+--------------+------------+------+
| user_id | streak_start | streak_end | days |
+---------+--------------+------------+------+
|       1 | 2025-03-01   | 2025-03-03 |    3 |
|       1 | 2025-03-07   | 2025-03-08 |    2 |
|       1 | 2025-03-15   | 2025-03-15 |    1 |
+---------+--------------+------------+------+

The trick: for consecutive days, the date and the row number increase together, so their difference stays constant within a streak. Grouping by that difference groups each streak.

Deduplication: keep the latest row per key

-- standalone
CREATE TABLE customer_updates (email TEXT, city TEXT, updated_at TEXT);
INSERT INTO customer_updates VALUES
  ('ada@example.com', 'London',    '2025-01-01'),
  ('ada@example.com', 'Cambridge', '2025-03-10'),
  ('linus@example.com', 'Helsinki', '2025-02-02');

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC) AS rn
  FROM customer_updates
)
SELECT email, city, updated_at FROM ranked WHERE rn = 1 ORDER BY email;

+-------------------+-----------+------------+
|       email       |   city    | updated_at |
+-------------------+-----------+------------+
| ada@example.com   | Cambridge | 2025-03-10 |
| linus@example.com | Helsinki  | 2025-02-02 |
+-------------------+-----------+------------+

This "latest record per key" pattern cleans up duplicated imports and event logs.

Percent of total

SELECT category, COUNT(*) AS products,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM products
GROUP BY category
ORDER BY products DESC, category;

+-------------+----------+------+
|  category   | products | pct  |
+-------------+----------+------+
| Accessories |        3 | 50.0 |
| Audio       |        1 | 16.7 |
| Displays    |        1 | 16.7 |
| Video       |        1 | 16.7 |
+-------------+----------+------+

SUM(COUNT(*)) OVER () — a window over the grouped result — gives the grand total on every row.