Fill in the missing days

A daily report needs one row for every day, including the quiet ones, and a calendar of days joined to the data gives you that. Here the data is the orders table of learn.shop, the small online shop, which holds one row per order, with its order_date, status and quantity.

How it works

First build one row for every day you want to report. Then LEFT JOIN the data onto that list. Every day stays, and a day with no matching rows gets NULL in the data columns.

Common mistake

COUNT(*) after the join reports 1 order on a day that had none, because the empty day is still one row. Count a column of the joined table.

Worked example

SELECT day, COUNT(o.order_id) AS orders
FROM UNNEST(GENERATE_DATE_ARRAY('2024-07-15', '2024-07-21')) AS day
LEFT JOIN `learn.shop.orders` AS o ON o.order_date = day
GROUP BY day
ORDER BY day

The calendar has seven days. Orders exist on 15, 19, 20 and 21 July, so 16, 17 and 18 July show 0.

Each day is one group of the calendar, so a day without orders still has its row.

All BigQuery lessons