Calculate Rolling 7-Day Sum of Answers by Device

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

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.

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

Quick Overview

Calculate Rolling 7-Day Sum of Answers by Device