Analytical & Reporting Queries

Real-world reporting demands techniques that go beyond basic aggregation. This lesson covers date series generation, gap-and-island problems, running totals, percentiles, pivot/unpivot patterns, and JSON aggregation for building comprehensive reports.

---

Date Series Generation

Many reports need a row for every day, week, or month — even when no data exists for that period.

-- Generate a continuous daily series
SELECT generate_series(
  '2024-01-01'::date,
  '2024-12-31'::date,
  '1 day'::interval
)::date AS dt;

Filling Gaps in Time Series

-- Revenue per day, including days with zero revenue
WITH date_range AS (
  SELECT generate_series(
    '2024-01-01'::date,
    '2024-03-31'::date,
    '1 day'::interval
  )::date AS dt
)
SELECT
  dr.dt,
  COALESCE(SUM(o.total), 0) AS daily_revenue
FROM date_range dr
LEFT JOIN orders o ON o.order_date = dr.dt
GROUP BY dr.dt
ORDER BY dr.dt;

Monthly Series

WITH months AS (
  SELECT generate_series(
    DATE_TRUNC('month', '2024-01-01'::date),
    DATE_TRUNC('month', '2024-12-01'::date),
    '1 month'::interval
  )::date AS month_start
)
SELECT
  m.month_start,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.total), 0) AS revenue
FROM months m
LEFT JOIN orders o ON DATE_TRUNC('month', o.order_date) = m.month_start
GROUP BY m.month_start
ORDER BY m.month_start;

---

Gap-and-Island Problems

Identify consecutive sequences (islands) and breaks (gaps) in data.

Finding Gaps