Daily Event Totals — Grouping by Date Periods in SQL

The problem

Scenario: Streamhub's analytics team is reviewing platform activity at a daily level to identify high-traffic days.

Task: Write a query to return each calendar day and the total number of events recorded on that day.

Assumptions:

The events table holds one row per recorded event, with the timestamp stored in occurred_at.
A calendar day is identified by its date and covers every event recorded within that day.

Output:

One row per calendar day present in the data.
Columns in this order: day, event_count.

Schema · analytics 5 tables

users

id integer

name text

email text

country text

plan text

signed_up_at timestamptz

is_active boolean

conversions

id integer

user_id integer

converted_at timestamptz

plan text

amount numeric

sessions

id integer

user_id integer

started_at timestamptz

ended_at? timestamptz

event_count integer

events

id integer

user_id integer

session_id? integer

event_type text

occurred_at timestamptz

properties? jsonb

periods

id integer

name text

start_month integer

end_month integer

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  DATE_TRUNC('day', occurred_at)::date AS DAY,
  COUNT(*) AS event_count
FROM
  events
GROUP BY
  DATE_TRUNC('day', occurred_at)

The shape

date_trunc('day', occurred_at) reduces each event timestamp to midnight of its calendar day, which makes every event from the same day share one grouping key. COUNT(*) then runs once per day and returns one row per calendar day with the event volume for that day.

Clause by clause

SELECT date_trunc('day', occurred_at)::date AS day, COUNT(*) AS event_count returns one row per day. The ::date cast removes the midnight time component so the output column types as a date instead of a timestamp. COUNT(*) counts the events that fall inside the day.
FROM events reads every recorded event. There is no WHERE; every day with at least one event is in scope.
GROUP BY date_trunc('day', occurred_at) uses the same truncation as the grouping key. Two events recorded at 14:32 and 22:05 on the same date both truncate to that date at midnight and end up in the same group.

The trap

The GROUP BY has to repeat the date_trunc expression rather than reference the day alias. The alias defined in SELECT is not yet in scope when GROUP BY is evaluated, so GROUP BY day raises an error. Repeat the expression, or write GROUP BY 1 to reference the first SELECT column by position.

You practiced truncating a timestamp to its calendar day so all events sharing a day collapse into a single row.

Return each calendar day and the total number of `events` recorded on that day

The shape

Clause by clause

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.