Calculate Weekly Event Sums from Daily Counts

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at Amazon.

Q: What role is this question designed for?

This question is commonly asked for Business Intelligence Engineer candidates at Amazon during technical interviews.

Question

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.

PracHub · Accepted 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.

Quick Overview

Calculate Weekly Event Sums from Daily Counts