Calculate Rolling 7-Day Sum of Answers by Device

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.

Rolling 7-Day Answers Per Device

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.

Table ANSWERS

column	type	constraints
date	DATE
device	VARCHAR
answers	INTEGER

Sample data:

answers	date	device
10	2019-01-01	echo
20	2019-01-02	echo
30	2019-01-03	echo
10	2019-01-01	dot
15	2019-01-02	dot

Expected output

For each device and each existing date row, return the date, device, and the sum of answers over the current date and the previous 6 days (inclusive). Order the final result by date, then device.

date	device	trailing_7d_answers
2019-01-01	dot	10
2019-01-01	echo	10
2019-01-02	dot	25
2019-01-02	echo	30
2019-01-03	echo	60

Hints

1. Use a window function partitioned by device.
2. Use a date-based RANGE frame for the current date plus the previous 6 calendar days.
3. Quote the table and column identifiers exactly as shown in the schema when running in PostgreSQL.