Katkat
Back

Calculate Rolling 7-Day Sum of Answers by Device

AmazonBusiness Intelligence Engineer · Onsite
MediumBusiness Intelligence EngineerOnsiteData Manipulation (SQL/Python)

Last updated

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.

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.