Your question is Rolling Average Over Last 30 Days. Start with the requirements and the one table on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Write a query using SQL window functions to calculate the rolling average of customer engagement over the last 30 days.
Use the engagement_daily table. Include each recorded engagement row, and treat the 30-day period as inclusive of the current date. Rows with a NULL engagement score remain in the output but do not contribute to the average.
customer_id, engagement_date, engagement_score, rolling_average, and engagement_id.customer_id, engagement_date, and engagement_id.| Column | Type | Description |
|---|---|---|
| engagement_idPK | INTEGER | Unique engagement record identifier |
| customer_id | VARCHAR(30) | Customer associated with the engagement |
| engagement_date | DATE | Date on which the engagement was recorded |
| engagement_score | NUMERIC(10,2) | Measured customer engagement score |