How to Group by Time

In order to group by time, we need to define the granularity level of the time element to group by. For example if we define a group by hour, then we need to extract the hour from the timestamp, and group by this derived field.

WITH sales AS (
  SELECT DATETIME '2022-04-03 11:06:00' AS sold_at,20.34 as revenue UNION ALL
  SELECT DATETIME '2022-04-03 10:25:00', 10.00 UNION ALL
  SELECT DATETIME '2022-04-04 11:55:00', 16.00 UNION ALL
  SELECT DATETIME '2022-04-03 11:34:00', 10.06
select extract(hour from sold_at) as hour, 
       count(*) as quantity
from   sales
group by 1;
hour quantity
10 1
11 3