SQL
Generate and Fill Missing Dates for Time-Series Aggregations
Create robust time-series reports by generating a series of dates and left joining your aggregate data to display zeros for periods with no activity.
WITH date_series AS (
SELECT generate_series('2023-01-01'::date, '2023-01-31'::date, '1 day'::interval)::date AS day
),
daily_sales AS (
SELECT
DATE(order_date) AS sale_day,
SUM(total_amount) AS total_sales
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY 1
)
SELECT
ds.day,
COALESCE(dsales.total_sales, 0) AS daily_revenue
FROM date_series ds
LEFT JOIN daily_sales dsales ON ds.day = dsales.sale_day
ORDER BY ds.day;
How it works: This query is crucial for building complete time-series reports, especially for dashboards where every day in a range should be represented, even if there's no data. It uses `generate_series` (a PostgreSQL function, similar functionality exists in other databases) to create a continuous sequence of dates. This date series is then `LEFT JOIN`ed with aggregated daily sales data. `COALESCE(dsales.total_sales, 0)` ensures that any date without sales data will correctly display `0` instead of `NULL`, providing a comprehensive view of daily revenue.