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.
GENERATE_DATE_ARRAY(start, end)returns an array with oneDATEper day, including both ends. A third argument such asINTERVAL 1 WEEKchanges the step.FROM UNNEST(GENERATE_DATE_ARRAY('2024-07-01', '2024-07-14')) AS dayturns that array into 14 rows with oneDATEcolumn calledday. TheUNNESTis required:FROM GENERATE_DATE_ARRAY(...)alone fails with the errorTable-valued function not found, because the function returns an array.- Join the data to the calendar with
LEFT JOIN orders AS o ON o.order_date = day, with the calendar on the left. BigQuery refusesRIGHT JOIN UNNESTandFULL JOIN UNNEST. A day with three orders gets three rows. A day with none gets one row where everyocolumn isNULL. COUNT(o.order_id)skipsNULL, so an empty day counts 0.COUNT(*)counts theNULLrow as well and returns 1.SUM(o.quantity)isNULLon an empty day.COALESCE(SUM(o.quantity), 0)turns that into 0.- A condition that decides which orders count, such as a status or a date range, goes in
ON. InWHEREit isNULLon the row of an empty day,WHEREkeeps only rows where a condition is true, and so the empty day is removed. - A test for the missing row goes in
WHERE:WHERE o.order_id IS NULLkeeps the days that found no order. WriteIS NULL. BigQuery refuses= NULLwith an error.
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 dayThe 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.