Business query patterns — period comparisons, customer-level analysis and cohorts

Period comparisons

Month-over-month is distorted by seasonality. Year-over-year (YoY) compares a period with the same period a year earlier.

WITH monthly AS (
  SELECT strftime('%Y', o.order_date) AS yr, strftime('%m', o.order_date) AS mo,
         SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct)) AS revenue
  FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed'
  GROUP BY 1, 2
)
SELECT mo AS month,
       ROUND(SUM(CASE WHEN yr = '2024' THEN revenue END)) AS rev_2024,
       ROUND(SUM(CASE WHEN yr = '2025' THEN revenue END)) AS rev_2025,
       ROUND(100 * (SUM(CASE WHEN yr = '2025' THEN revenue END)
                  / SUM(CASE WHEN yr = '2024' THEN revenue END) - 1), 1) AS yoy_pct
FROM monthly
GROUP BY mo
ORDER BY mo;

The conditional aggregation pivots years into columns. Every month can now be judged against its seasonal twin.

Period-to-date

"Are we ahead of last year so far?" requires equal-length periods. If today were 15 November 2025, compare 1 Jan–15 Nov 2025 with 1 Jan–15 Nov 2024 — not with the full year 2024.

SELECT strftime('%Y', o.order_date) AS year,
       ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct))) AS ytd_revenue_to_15_nov
FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'completed'
  AND strftime('%m-%d', o.order_date) <= '11-15'
  AND strftime('%Y', o.order_date) IN ('2024', '2025')
GROUP BY 1;

Customer-level tables

Many analyses start by turning transactions into one row per customer:

WITH order_rev AS (
  SELECT o.order_id, o.customer_id, o.order_date,
         SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct)) AS revenue
  FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed'
  GROUP BY o.order_id, o.customer_id, o.order_date
)
SELECT customer_id,
       MIN(order_date) AS first_order,
       MAX(order_date) AS last_order,
       CAST(julianday('2025-12-31') - julianday(MAX(order_date)) AS INT) AS recency_days,
       COUNT(*) AS frequency,
       ROUND(SUM(revenue), 2) AS monetary
FROM order_rev
GROUP BY customer_id
ORDER BY monetary DESC
LIMIT 6;

Recency, frequency and monetary value are the inputs of RFM segmentation (Module 8). Note the analysis date (31 Dec 2025) is fixed and explicit — using "today" would make results change every day.

Cohort analysis

A cohort is a group of customers who share a starting event — here, the month of their first completed order. A cohort table shows what share of each cohort is still active N months later. It answers: are newer customers more or less loyal than older ones?

WITH firsts AS (
  SELECT customer_id, MIN(strftime('%Y-%m', order_date)) AS cohort
  FROM orders WHERE status = 'completed' GROUP BY customer_id
),
activity AS (
  SELECT DISTINCT o.customer_id, f.cohort,
         (CAST(strftime('%Y', o.order_date) AS INT) - CAST(substr(f.cohort, 1, 4) AS INT)) * 12
       + (CAST(strftime('%m', o.order_date) AS INT) - CAST(substr(f.cohort, 6, 2) AS INT)) AS month_index
  FROM orders o JOIN firsts f ON f.customer_id = o.customer_id
  WHERE o.status = 'completed'
),
sizes AS (SELECT cohort, COUNT(*) AS cohort_size FROM firsts GROUP BY cohort)
SELECT a.cohort, s.cohort_size,
       ROUND(100.0 * SUM(a.month_index = 1) / s.cohort_size, 1) AS m1_pct,
       ROUND(100.0 * SUM(a.month_index = 3) / s.cohort_size, 1) AS m3_pct,
       ROUND(100.0 * SUM(a.month_index = 6) / s.cohort_size, 1) AS m6_pct,
       ROUND(100.0 * SUM(a.month_index = 12) / s.cohort_size, 1) AS m12_pct
FROM activity a JOIN sizes s ON s.cohort = a.cohort
WHERE a.cohort BETWEEN '2023-01' AND '2023-12'
GROUP BY a.cohort, s.cohort_size
ORDER BY a.cohort;

How to read it: m3_pct for the 2023-01 cohort is the percentage of customers who made their first purchase in January 2023 and purchased again in April 2023. Each step:

1. firsts — each customer's cohort (first purchase month).
2. activity — every month in which each customer bought, expressed as months since their cohort month (month_index).
3. sizes — customers per cohort.
4. Final — share of each cohort active at month 1, 3, 6, 12. (SUM(condition) works in SQLite because TRUE is 1; elsewhere use SUM(CASE WHEN … THEN 1 ELSE 0 END).)

The earliest cohorts are larger because they include long-standing customers making their first purchase in the data — a left-censoring effect: customers who signed up before 2023 appear as "new" in January 2023. Professional cohort analysis acknowledges this or uses signup date where available.

Practice

Independent assignment

Using the telecom database, build a cohort table by signup month for 2024 cohorts, showing the share of subscribers still billed 3, 6 and 12 months after signup (use monthly_usage). Write three sentences interpreting the result for the Commercial Director.

Summary

• Use YoY and equal-length period-to-date comparisons in seasonal businesses; LFL removes opening/closing effects.
• Customer-level tables (first/last order, recency, frequency, monetary) are the base for segmentation.
• Cohort tables track retention by starting month; be aware of censoring at the data's edges.