Calculate Rolling 7-Day Sum of Answers by Device
Company: Amazon
Role: Business Intelligence Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
ANSWERS
+------------+--------+---------+
| date | device | answers |
+------------+--------+---------+
| 2019-01-01 | echo | 10 |
| 2019-01-02 | echo | 20 |
| 2019-01-03 | echo | 30 |
| 2019-01-01 | dot | 10 |
| 2019-01-02 | dot | 15 |
+------------+--------+---------+
##### Scenario
Voice-assistant analytics team needs to monitor recent engagement per device type.
##### Question
Given table ANSWERS(date, device, answers), write a query returning (date, device, trailing_7d_answers) where trailing_7d_answers is the rolling 7-day sum of answers for each device, ordered by date.
##### Hints
Use window functions with PARTITION BY device and a 7-day RANGE or conditional SUM over preceding rows.
Overview: This question evaluates proficiency in time-series aggregation and group-wise metric computation on structured data, specifically calculating rolling seven-day sums of answers per device, and assesses familiarity with data manipulation using SQL or Python.
Using the PostgreSQL table `ANSWERS(date, device, answers)`, return one row for each existing `(date, device)` record with `date`, `device`, and `trailing_7d_answers`. The metric is the rolling 7-day sum of `answers` for the same device, including the current date and the previous 6 calendar days. Order the result by `date`, then `device`.
Tables
ANSWERS(date DATE, device VARCHAR, answers INTEGER)
Hints
- Use a window function partitioned by device.
- Use a date-based RANGE frame for the current date plus the previous 6 calendar days.