How to group data into time buckets of a specified interval using Clickhouse

I'm trying to write a query, where the data for a certain time range is grouped in groups of x days from the start of the date range.

An example would be:

daterange : 05/11/2023 to 05/12/2023

So my expected result would be the following if I was trying to group by 3 days:

Date	Count
05/11/2023	1
08/11/2023	16
11/11/2023	2
14/11/2023	33
17/11/2023	4
20/11/2023	5
23/11/2023	6
26/11/2023	1
29/11/2023	1
02/12/2023	4

Initially i was tying to accomplish it with toStartOfInterval method, but later realized that the intervals produced by this method are relative to 1970-01-01 Hence the grouping are not guaranteed to be within the specified date range.

Solution

Try this:

WITH 
  toDate ('2023-11-05') as d_start
, toDate ('2023-11-10') as d_end
, intDiv (dt - d_start, 3) as grp
SELECT 
  d_start + 3 * grp as d
, count () as cnt
FROM VALUES 
(
  'dt Date'
, (toDate ('2023-11-05'))
, (toDate ('2023-11-06'))
, (toDate ('2023-11-07'))

, (toDate ('2023-11-08'))
, (toDate ('2023-11-08'))
, (toDate ('2023-11-09'))
, (toDate ('2023-11-10'))

, (toDate ('2023-11-11'))
)
WHERE dt BETWEEN d_start AND d_end
GROUP BY grp


|d         |cnt|
|----------|---|
|2023-11-05|3  |
|2023-11-08|4  |