Weekly Leads by UTM Medium — SQL Dates Problem
The demand-generation team reports leads every Monday. A week runs Monday to Sunday and is named by its Monday.
- Difficulty: Medium
- Topics: Dates, Aggregation
- Dialect: MySQL
- Problem: #288
Problem statement
The demand-generation team reports leads every Monday. A week runs Monday to Sunday and is named by its Monday. A lead with a NULL utm_medium arrived untagged and is reported under the medium 'untagged'.
Return week_start (the Monday of the lead's week, as a date), utm_medium and leads (the number of leads), for every week and medium that has leads. Order by week_start, then utm_medium.
Tables
Table: MarketingLead
| Column | Type |
|---|---|
| lead_id | int |
| varchar | |
| utm_medium | varchar |
| created_at | datetime |
Primary key: lead_id.
One row per form fill. utm_medium is lower case (cpc, social, email, referral) or NULL when the link carried no tag.
Examples
Example 1
MarketingLead
| lead_id | utm_medium | created_at | |
|---|---|---|---|
| 1 | neha@corp.in | cpc | 2024-04-01 09:12:00 |
| 2 | rahul@corp.in | cpc | 2024-04-07 23:59:59 |
| 3 | sara@corp.in | social | 2024-04-03 14:00:00 |
| 4 | dev@corp.in | cpc | 2024-04-08 00:00:01 |
| 5 | pooja@corp.in | NULL | 2024-04-09 11:30:00 |
| 6 | liam@corp.in | 2024-03-31 18:45:00 | |
| 7 | ira@corp.in | NULL | 2024-04-10 10:10:10 |
Output
| week_start | utm_medium | leads |
|---|---|---|
| 2024-03-25 | 1 | |
| 2024-04-01 | cpc | 2 |
| 2024-04-01 | social | 1 |
| 2024-04-08 | cpc | 1 |
| 2024-04-08 | untagged | 2 |
How to solve Weekly Leads by UTM Medium
Bucketing by week is bucketing by a computed date. WEEKDAY(created_at) counts days since Monday (Monday 0 … Sunday 6), so DATE_SUB(DATE(created_at), INTERVAL WEEKDAY(created_at) DAY) is the Monday of the week: a lead at 23:59:59 on Sunday 7 April belongs to the week of 1 April, and one second past midnight on Monday 8 April starts the next week. WEEK()/YEARWEEK() are tempting but depend on a mode argument and number weeks rather than dating them.
DAYOFWEEK counts from Sunday = 1, so (DAYOFWEEK(d) + 5) % 7 gives the same days-since-Monday, as the alternative uses with SUBDATE. COALESCE renames the NULL medium before grouping — GROUP BY would put NULLs in one group anyway, but the output must say 'untagged'.
One scan, one aggregate over a handful of (week, medium) buckets.
Reference solution (MySQL)
SELECT week_start, utm_medium, COUNT(*) AS leads
FROM (
SELECT DATE_SUB(DATE(created_at), INTERVAL WEEKDAY(created_at) DAY) AS week_start,
COALESCE(utm_medium, 'untagged') AS utm_medium
FROM MarketingLead
) l
GROUP BY week_start, utm_medium
ORDER BY week_start, utm_mediumAnother way
SELECT SUBDATE(CAST(created_at AS DATE), (DAYOFWEEK(created_at) + 5) % 7) AS week_start, IFNULL(utm_medium, 'untagged') AS utm_medium, COUNT(*) AS leads
FROM MarketingLead
GROUP BY SUBDATE(CAST(created_at AS DATE), (DAYOFWEEK(created_at) + 5) % 7), IFNULL(utm_medium, 'untagged')
ORDER BY week_start, utm_medium← Campaigns Outspending Their Channel's Average · Month-over-Month Ad Spend Change by Channel →