Campaigns That Overspent Their Budget
Find campaigns that spent more than their daily_budget on more than 7 days. Report the number of overspend days and the total amount spent above budget on those days.
Expected output
name, daily_budget, overspend_days, overspend_amount (rounded to 2 decimals). Sorted by overspend_amount descending.
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
- Revenue by Product Category (easy)
- Customers Who Never Ordered (easy)
- Quarterly Cancellation Rate (medium)
- Returned Value Share by Category (medium)
- Cancellation Rate by City (easy)