The Problem

When building dashboards that compare current vs previous period data, you’ll hit a common problem: gaps in your data. If there were no events on Tuesday, you don’t get a row for Tuesday. Your chart looks broken.

Clickhouse has WITH FILL which generates missing rows in your result set. Here’s how to use it for period-over-period comparisons.

Generating a complete date range

This query generates every hour in a range, even if you have no data:

SELECT toDateTime('2025-06-19 12:00:00', 'UTC') AS dt
ORDER BY dt ASC
WITH FILL
FROM dt
TO toDateTime('2025-06-19 18:00:00', 'UTC')
STEP toIntervalHour(1)

For days instead of hours, use toDate and toIntervalDay(1).

Using it with real data

The trick is to use this as a CTE and left join your actual data onto it:

WITH
date_range AS (
  SELECT toDateTime('2025-06-19 12:00:00', 'UTC') AS dt
  ORDER BY dt ASC
  WITH FILL
  FROM dt
  TO toDateTime('2025-06-19 18:00:00', 'UTC')
  STEP toIntervalHour(1)
),
current_data AS (
  SELECT
    toStartOfHour(event_time) AS dt,
    sum(revenue) AS revenue
  FROM events
  WHERE event_time >= '2025-06-19 12:00:00'
    AND event_time < '2025-06-19 18:00:00'
  GROUP BY dt
),
prior_data AS (
  SELECT
    toStartOfHour(event_time) + INTERVAL 7 DAY AS dt,
    sum(revenue) AS prior_revenue
  FROM events
  WHERE event_time >= '2025-06-12 12:00:00'
    AND event_time < '2025-06-12 18:00:00'
  GROUP BY dt
)
SELECT
  dr.dt,
  coalesce(c.revenue, 0) AS revenue,
  coalesce(p.prior_revenue, 0) AS prior_revenue
FROM date_range dr
LEFT JOIN current_data c ON c.dt = dr.dt
LEFT JOIN prior_data p ON p.dt = dr.dt
ORDER BY dr.dt

The date_range CTE guarantees you get every time bucket. The left joins bring in actual data where it exists. coalesce handles the nulls from missing data, showing 0 instead.

This pattern works for any time granularity - just change the interval functions and adjust your date math for the prior period.