You are viewing a free preview of this lesson.
Subscribe to unlock all 10 lessons in this course and every other course on LearningBro.
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.
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;
-- 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;
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;
Identify consecutive sequences (islands) and breaks (gaps) in data.
Subscribe to continue reading
Get full access to this lesson and all 10 lessons in this course.