Lessons › SQL › Deep Dive: Business Analytics Patterns

SQL lesson

GENERATE_SERIES() for date ranges and gap filling

GENERATE_SERIES() for dates produces every date in a range - including days where nothing happened - so gaps never silently disappear from your results.

Example

Filling Retail Date Gaps with GENERATE_SERIES()

WITH date_spine AS (
  SELECT generate_series('2024-03-01'::date, '2024-03-08'::date, '1 day'::interval)::date AS order_date
)
SELECT ds.order_date,
       COALESCE(SUM(o.revenue), 0) AS total_revenue,
       COALESCE(SUM(o.units_sold), 0) AS total_units
FROM date_spine ds
LEFT JOIN orders o ON o.order_date = ds.order_date
GROUP BY ds.order_date
ORDER BY ds.order_date

Result:

Step by step

1. WITH lets you name a sub-query so the main query can reference it.

   WITH date_spine AS (
     SELECT generate_series('2024-03-01'::date, '2024-03-08'::date, '1 day'::interval)::date AS order_date
   )

2. After SELECT, list the column names you want, separated by commas.

   WITH date_spine AS (
     SELECT generate_series('2024-03-01'::date, '2024-03-08'::date, '1 day'::interval)::date AS order_date
   )
   SELECT ds.order_date,
          COALESCE(SUM(o.revenue), 0) AS total_revenue,
          COALESCE(SUM(o.units_sold), 0) AS total_units

3. FROM tells SQL which table to pull the data from.

   WITH date_spine AS (
     SELECT generate_series('2024-03-01'::date, '2024-03-08'::date, '1 day'::interval)::date AS order_date
   )
   SELECT ds.order_date,
          COALESCE(SUM(o.revenue), 0) AS total_revenue,
          COALESCE(SUM(o.units_sold), 0) AS total_units
   FROM date_spine ds

Practice

Try each one in your head first, then open the answer.

SELECT GENERATE_SERIES('2024-01-01'::date, '2024-12-31'::date, INTERVAL '1 day')

SELECT GENERATE_SERIES('2024-01-01'::date, '2024-12-31'::date, INTERVAL '1 day')

SELECT GENERATE_SERIES('2024-01-01'::date, '2024-12-31'::date, INTERVAL '1 day')

SELECT GENERATE_SERIES('2024-01-01'::date, '2024-12-31'::date, INTERVAL '1 day')

SELECT GENERATE_SERIES('2024-01-01'::date, '2024-12-31'::date, INTERVAL '1 day')