Reconcile Daily Clicks with Click Logs
Data quality check: for each campaign, compare the total clicks reported in ad_daily with the number of rows in ad_clicks. Show both numbers and the difference.
Expected output
campaign_id, name, reported_clicks, logged_clicks, diff (reported - logged). Sorted by campaign_id.
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)
- Monthly Revenue Trend (easy)
- Average Order Value by Country (easy)
- Repeat Purchase Rate (medium)