Calculate Weekly Event Sums from Daily Counts
Company: Amazon
Role: Business Intelligence Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: 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.
You are given a table **EVENT_LOG** with daily event counts:
- `event_date` (DATE) — the day the events occurred
- `cnt` (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 of `cnt` from `EVENT_LOG` for 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.
Tables
EVENT_LOG(event_date DATE, cnt INTEGER)
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.