Sign-ups by UTM Source Read From Landing URLs — SQL Problem
The sign-up form stores the full landing URL the visitor arrived on, query string and all, but nobody split out the UTM tags.
- Difficulty: Medium
- Topics: Strings, Aggregation
- Dialect: MySQL
- Problem: #281
Problem statement
The sign-up form stores the full landing URL the visitor arrived on, query string and all, but nobody split out the UTM tags. The acquisition team wants sign-ups per source.
The source is the value of the utm_source parameter: the text after utm_source= up to the next & (or the end of the URL). A sign-up whose URL has no utm_source=, or whose URL is NULL, counts under the source 'direct'. Return utm_source and signups, ordered by signups descending, then utm_source ascending.
Tables
Table: Signup
| Column | Type |
|---|---|
| signup_id | int |
| varchar | |
| landing_url | varchar |
| signed_up_at | datetime |
Primary key: signup_id.
One row per new account. landing_url is the first page of the visit, or NULL when the browser blocked it. UTM values are lower case.
Examples
Example 1
Signup
| signup_id | landing_url | signed_up_at | |
|---|---|---|---|
| 1 | aarav@mail.in | https://shop.example.in/diwali?utm_source=google&utm_medium=cpc&utm_campaign=diwali | 2024-10-21 10:02:11 |
| 2 | diya@mail.in | https://shop.example.in/?utm_medium=social&utm_source=instagram | 2024-10-21 11:45:00 |
| 3 | kabir@mail.in | https://shop.example.in/ | 2024-10-21 12:10:40 |
| 4 | meera@mail.in | https://shop.example.in/sale?utm_source=google&utm_medium=cpc | 2024-10-22 09:30:05 |
| 5 | zara@mail.in | NULL | 2024-10-22 18:22:19 |
| 6 | rohan@mail.in | https://shop.example.in/app?utm_source=newsletter | 2024-10-23 07:55:00 |
| 7 | ira@mail.in | https://shop.example.in/?utm_source=instagram&utm_content=reel | 2024-10-23 20:14:31 |
Output
| utm_source | signups |
|---|---|
| direct | 2 |
| 2 | |
| 2 | |
| newsletter | 1 |
How to solve Sign-ups by UTM Source Read From Landing URLs
Parse first, aggregate second. For a URL containing utm_source=, SUBSTRING_INDEX(url, 'utm_source=', -1) returns the text after the last occurrence of the marker — google&utm_medium=cpc — and SUBSTRING_INDEX(…, '&', 1) keeps what precedes the first &, which is the value whether the parameter sits first, in the middle or last.
The guard matters: when the delimiter is absent, SUBSTRING_INDEX returns the whole input, so a URL with no UTM tag would become its own "source". LOCATE('utm_source=', url) > 0 routes those to 'direct', and since LOCATE on a NULL URL is NULL, the comparison is unknown and the NULL URLs fall to ELSE too. The first alternative uses INSTR and SUBSTRING with the marker's length (11); the second counts the direct sign-ups and the tagged ones in two queries joined by UNION ALL (the HAVING stops an empty direct group from appearing as a 0).
Grouping by the alias of a derived table is portable. One scan; string work is per row.
Reference solution (MySQL)
SELECT utm_source, COUNT(*) AS signups
FROM (
SELECT CASE
WHEN LOCATE('utm_source=', landing_url) > 0
THEN SUBSTRING_INDEX(SUBSTRING_INDEX(landing_url, 'utm_source=', -1), '&', 1)
ELSE 'direct'
END AS utm_source
FROM Signup
) s
GROUP BY utm_source
ORDER BY signups DESC, utm_sourceAnother way
SELECT utm_source, COUNT(*) AS signups FROM (
SELECT IF(IFNULL(INSTR(landing_url, 'utm_source='), 0) = 0, 'direct',
SUBSTRING_INDEX(SUBSTRING(landing_url, INSTR(landing_url, 'utm_source=') + 11), '&', 1)) AS utm_source
FROM Signup) s
GROUP BY utm_source ORDER BY signups DESC, utm_sourceAnother way
SELECT 'direct' AS utm_source, COUNT(*) AS signups FROM Signup WHERE landing_url IS NULL OR landing_url NOT LIKE '%utm_source=%' HAVING COUNT(*) > 0
UNION ALL
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(landing_url, 'utm_source=', -1), '&', 1), COUNT(*) FROM Signup WHERE landing_url LIKE '%utm_source=%'
GROUP BY SUBSTRING_INDEX(SUBSTRING_INDEX(landing_url, 'utm_source=', -1), '&', 1)
ORDER BY signups DESC, utm_source← Label Each Campaign by Its Monthly Budget Tier · Cost per Acquisition of Every Campaign →