Weekly Leads by UTM Medium

Medium
288.

Weekly Leads by UTM Medium

MediumDatesAggregation

MySQL

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

ColumnType
lead_idint
emailvarchar
utm_mediumvarchar
created_atdatetime

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.

Example

The rows must come back in the order shown.

MarketingLead

lead_idemailutm_mediumcreated_at
1neha@corp.incpc2024-04-01 09:12:00
2rahul@corp.incpc2024-04-07 23:59:59
3sara@corp.insocial2024-04-03 14:00:00
4dev@corp.incpc2024-04-08 00:00:01
5pooja@corp.inNULL2024-04-09 11:30:00
6liam@corp.inemail2024-03-31 18:45:00
7ira@corp.inNULL2024-04-10 10:10:10

Output

week_startutm_mediumleads
2024-03-25email1
2024-04-01cpc2
2024-04-01social1
2024-04-08cpc1
2024-04-08untagged2
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
lead_idint
emailvarchar
utm_mediumvarchar
created_atdatetime

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_idemailutm_mediumcreated_at
1neha@corp.incpc2024-04-01 09:12:00
2rahul@corp.incpc2024-04-07 23:59:59
3sara@corp.insocial2024-04-03 14:00:00
4dev@corp.incpc2024-04-08 00:00:01
5pooja@corp.inNULL2024-04-09 11:30:00
6liam@corp.inemail2024-03-31 18:45:00
7ira@corp.inNULL2024-04-10 10:10:10

Output

week_startutm_mediumleads
2024-03-25email1
2024-04-01cpc2
2024-04-01social1
2024-04-08cpc1
2024-04-08untagged2

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_medium

Another 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 →