Advanced Date Based Analysis

Concept

Analytical questions are usually questions about time: how does this month compare with last, are new customers coming back, what does the trend look like week by week. Three techniques cover most of them.

Complete date ranges

A GROUP BY can only produce rows for values that exist. A month with no orders simply vanishes, which breaks charts and misleads readers. Generate the full range first, then left join.

WITH RECURSIVE months AS (
    SELECT DATE('2024-01-01') AS month_start
    UNION ALL
    SELECT DATE_ADD(month_start, INTERVAL 1 MONTH) FROM months
    WHERE  month_start < '2024-12-01'
)
SELECT m.month_start,
       COUNT(o.id)               AS orders,
       COALESCE(SUM(o.total), 0) AS revenue
FROM   months m
LEFT JOIN orders o
       ON o.order_date >= m.month_start
      AND o.order_date <  DATE_ADD(m.month_start, INTERVAL 1 MONTH)
GROUP BY m.month_start
ORDER BY m.month_start;

Note the join condition: a half open range on the bare order_date column, so an index on it can still be used. Writing ON DATE_FORMAT(o.order_date, '%Y-%m') = ... would give the same answer and scan the whole table.

Period over period

WITH monthly AS (
    SELECT DATE_FORMAT(order_date, '%Y-%m-01') AS month_start,
           SUM(total)                          AS revenue,
           COUNT(*)                            AS orders
    FROM   orders
    WHERE  status <> 'cancelled'
    GROUP BY DATE_FORMAT(order_date, '%Y-%m-01')
)
SELECT month_start,
       revenue,
       LAG(revenue) OVER (ORDER BY month_start) AS prev_month,
       revenue - LAG(revenue) OVER (ORDER BY month_start) AS revenue_change,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month_start))
             / NULLIF(LAG(revenue) OVER (ORDER BY month_start), 0), 1) AS pct_change
FROM   monthly
ORDER BY month_start;

NULLIF(..., 0) is doing real work: without it the first month, or any month following a zero, divides by zero.

Rolling windows over time

WITH daily AS (
    SELECT order_date, SUM(total) AS revenue
    FROM   orders
    GROUP BY order_date
)
SELECT order_date,
       revenue,
       ROUND(AVG(revenue) OVER (ORDER BY order_date
                                ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS rolling_7,
       SUM(revenue) OVER (ORDER BY order_date
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)   AS cumulative
FROM   daily
ORDER BY order_date;

A ROWS frame counts rows, not days. If some days have no orders, "6 preceding rows" spans more than 7 calendar days. Join to a generated calendar first when the window must be a true 7 day window.

Cohort retention

WITH first_order AS (
    SELECT customer_id,
           MIN(order_date) AS cohort_date
    FROM   orders
    GROUP BY customer_id
),
activity AS (
    SELECT f.customer_id,
           DATE_FORMAT(f.cohort_date, '%Y-%m') AS cohort_month,
           TIMESTAMPDIFF(MONTH, f.cohort_date, o.order_date) AS months_since_first
    FROM   first_order f
    JOIN   orders o ON o.customer_id = f.customer_id
)
SELECT cohort_month,
       COUNT(DISTINCT CASE WHEN months_since_first = 0 THEN customer_id END) AS month_0,
       COUNT(DISTINCT CASE WHEN months_since_first = 1 THEN customer_id END) AS month_1,
       COUNT(DISTINCT CASE WHEN months_since_first = 2 THEN customer_id END) AS month_2,
       COUNT(DISTINCT CASE WHEN months_since_first = 3 THEN customer_id END) AS month_3
FROM   activity
GROUP BY cohort_month
ORDER BY cohort_month;

Three ideas combined: a CTE to find each customer's cohort, a join to measure activity relative to it, and conditional aggregation to pivot the periods across the top. That is the whole cohort report.

Important rules

• Group by a truncated date, not a formatted string, where the engine offers it (DATE_TRUNC in PostgreSQL, DATETRUNC in SQL Server 2022+).
• Filter with half open ranges on the bare column so indexes stay usable.
• Generate the calendar when a period with no data must still appear.
• ROWS frames count rows; use a complete calendar for true time windows.
• Guard every percentage change with NULLIF(previous, 0).

Common mistakes

• Missing periods silently dropping out of a chart.
• Wrapping the date column in a function in WHERE or in a join condition.
• Assuming a 7 row moving average is a 7 day moving average.
• Dividing by the previous period without guarding against zero.
• Mixing time zones between the stored timestamp and the reporting boundary.

Best practices

• Keep a permanent calendar table with columns for month, quarter, week and working day flags. It simplifies every report of this kind.
• Compute the period key once in a CTE and reuse it.
• Store timestamps in UTC and convert once, at the reporting boundary.
• Always show the period even when its value is zero.

Practice

1. Produce monthly revenue for 2024 with every month present, including empty ones.
2. Add a month over month percentage change column, safe against division by zero.
3. Build a cohort table showing, for each signup month, how many customers ordered in each of the following three months.