Coding questions · Amazon
Calculate Weekly Event Sums from Daily Counts
This question evaluates a candidate's ability to perform temporal data aggregation and date manipulation in SQL and/or Python, focusing on ISO-week boundaries, weekly grouping, and summation of event counts.
EVENT_LOG
+------------+------+
| event_date | cnt |
+------------+------+
| 2025-05-01 | 17 |
| 2025-05-02 | 12 |
| 2025-05-08 | 30 |
+------------+------+
Scenario
A product analytics team stores daily event counts and needs weekly aggregates for reporting.
Question
Given the EVENT_LOG table (event_date DATE, cnt INT) and a parameter current_date, write an SQL query that returns, for every ISO-week falling in the month prior to current_date, the week start date and the sum of cnt.
Hints
Generate all weeks of the previous month, date_trunc('week', …) or week number, join to the log, aggregate.
Weekly aggregates for the previous month using ISO weeks
You are given a table EVENT_LOG with daily event counts:
event_date(DATE) — the day the events occurredcnt(INTEGER) — the number of events on that day
Treat the current date as fixed at '2025-06-01', so the previous month is May 2025 (2025-05-01 through 2025-05-31 inclusive).
Weeks are ISO weeks (Monday-start). Write a single PostgreSQL query that, for every ISO week that overlaps May 2025, returns one row containing:
week_start— the Monday that begins that ISO week (DATE)weekly_cnt— the sum ofcntfromEVENT_LOGfor dates that fall within May 2025 only and belong to that ISO week. Days outside May 2025 (e.g. the late-April portion of the first ISO week) must NOT be counted.
Include every ISO week that overlaps May 2025 even if it has no qualifying events; such weeks must have a weekly_cnt of 0 (not NULL).
Order the result by week_start ascending.
Table EVENT_LOG
| column | type | constraints |
|---|---|---|
| event_date | DATE | |
| cnt | INTEGER |
Sample data:
| cnt | event_date |
|---|---|
| 17 | 2025-05-01 |
| 12 | 2025-05-02 |
| 30 | 2025-05-08 |
Expected output
One row per ISO week overlapping May 2025: the Monday week_start and the sum of cnt for May-2025 dates in that week, with empty weeks reported as 0, ordered by week_start.
| week_start | weekly_cnt |
|---|---|
| 2025-04-28 | 29 |
| 2025-05-05 | 30 |
| 2025-05-12 | 0 |
| 2025-05-19 | 0 |
| 2025-05-26 | 0 |
Hints
- Treat the current date as '2025-06-01', so the target month is May 2025 (2025-05-01 to 2025-05-31).
- In PostgreSQL,
date_trunc('week', d)returns the Monday of the ISO week (there is no 'isoweek' field). Use it both to generate the week list and to bucket each event date. - Generate all overlapping weeks with generate_series, aggregate cnt restricted to May dates only, then LEFT JOIN and COALESCE missing sums to 0.