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.
Quick Answer: 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.