Week-over-Week Spend by Channel
Sum spend per channel per week (weeks start Monday) and compute the change versus the same channel's previous week.
Expected output
channel, week_start (YYYY-MM-DD), spend, prev_spend (both rounded to 2 decimals), change_pct (rounded to 1 decimal, NULL for the first week). Sorted by channel, week_start.
Row order is checked. Column names are not checked; numbers are compared to 2 decimals.
Dataset: Digital ads performance
A retailer's paid campaigns across search, social, display and video in February 2026: daily delivery, individual ad clicks and purchases.
campaigns
One row per campaign.
| campaign_id | INTEGER | primary key |
| name | TEXT | |
| channel | TEXT | search, social, display or video |
| start_date | TEXT | YYYY-MM-DD |
| end_date | TEXT | YYYY-MM-DD inclusive, NULL if still running |
| daily_budget | REAL | USD |
ad_daily
Delivery per campaign per day it ran, as reported by the ad platform. clicks is the platform's count, which may differ from our own click log.
| campaign_id | INTEGER | |
| day | TEXT | YYYY-MM-DD |
| impressions | INTEGER | |
| clicks | INTEGER | |
| spend | REAL | USD |
ad_clicks
Our own click log: one row per ad click that reached the site and was tracked.
| click_id | INTEGER | primary key, increases with clicked_at |
| campaign_id | INTEGER | |
| user_id | INTEGER | |
| clicked_at | TEXT | YYYY-MM-DD HH:MM:SS (UTC) |
conversions
One row per purchase. Some purchases come from users who never clicked an ad.
| conversion_id | INTEGER | primary key |
| user_id | INTEGER | |
| converted_at | TEXT | YYYY-MM-DD HH:MM:SS (UTC) |
| revenue | REAL | USD |
Engine: SQLite. Dates are text (YYYY-MM-DD); use strftime(), date() and julianday() for date math.
Related problems
- Monthly Revenue Trend (easy)
- Quarterly Cancellation Rate (medium)
- Top 2 Products in Each Category (medium)
- Days from Signup to First Order (medium)
- Running Revenue Total for 2025 (medium)