Given a table of daily ad spend and a table of daily conversions, write a query to calculate the rolling 7-day Return on Ad Spend (ROAS) for lululemon.
Treat missing daily spend or conversion values as zero. Use conversion revenue as the return amount and include every calendar date between the earliest and latest available dated records.
report_date, rolling_7_day_spend, rolling_7_day_revenue, and rolling_7_day_roas.NULL when rolling spend is zero.| Column | Type | Description |
|---|---|---|
| spend_datePK | DATE | Calendar date of advertising spend |
| daily_spend | NUMERIC(12,2) | Total ad spend for the date |
| Column | Type | Description |
|---|---|---|
| conversion_datePK | DATE | Calendar date of attributed conversions |
| conversion_count | INTEGER | Number of attributed conversions |
| daily_revenue | NUMERIC(12,2) | Attributed conversion revenue for the date |