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

-- Find gaps in a sequence of invoice numbers
WITH numbered AS (
  SELECT
    invoice_number,
    LEAD(invoice_number) OVER (ORDER BY invoice_number) AS next_number
  FROM invoices
)
SELECT
  invoice_number AS gap_start,
  next_number AS gap_end,
  next_number - invoice_number - 1 AS missing_count
FROM numbered
WHERE next_number - invoice_number > 1;

Finding Islands (Consecutive Groups)

-- Identify consecutive days a user was active
WITH activity AS (
  SELECT DISTINCT
    user_id,
    activity_date,
    activity_date - (ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY activity_date
    ))::int AS grp
  FROM user_activity
)
SELECT
  user_id,
  MIN(activity_date) AS streak_start,
  MAX(activity_date) AS streak_end,
  COUNT(*) AS streak_length
FROM activity
GROUP BY user_id, grp
HAVING COUNT(*) >= 3  -- streaks of 3+ days
ORDER BY user_id, streak_start;

The key insight: subtracting the row number from a sequential value produces the same group identifier for consecutive values.

---

Running Totals and Cumulative Metrics

-- Cumulative revenue by month with year-over-year comparison
WITH monthly AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(total) AS revenue
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
  month,
  revenue,
  SUM(revenue) OVER (
    PARTITION BY EXTRACT(YEAR FROM month)
    ORDER BY month
  ) AS ytd_revenue,
  LAG(revenue, 12) OVER (ORDER BY month) AS same_month_last_year,
  CASE
    WHEN LAG(revenue, 12) OVER (ORDER BY month) IS NOT NULL
    THEN ROUND(
      100.0 * (revenue - LAG(revenue, 12) OVER (ORDER BY month))
            / LAG(revenue, 12) OVER (ORDER BY month), 1
    )
  END AS yoy_change_pct
FROM monthly
ORDER BY month;

---

Percentiles

PostgreSQL provides several percentile functions:

-- Percentile values for order totals
SELECT
  PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY total) AS median,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total) AS p75,
  PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY total) AS p90,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY total) AS p95,
  PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY total) AS p99
FROM orders;

Percentiles Per Group