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