Sign-ups by UTM Source Read From Landing URLs

Medium
281.

Sign-ups by UTM Source Read From Landing URLs

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

ColumnType
signup_idint
emailvarchar
landing_urlvarchar
signed_up_atdatetime

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.

Example

The rows must come back in the order shown.

Signup

signup_idemaillanding_urlsigned_up_at
1aarav@mail.inhttps://shop.example.in/diwali?utm_source=google&utm_medium=cpc&utm_campaign=diwali2024-10-21 10:02:11
2diya@mail.inhttps://shop.example.in/?utm_medium=social&utm_source=instagram2024-10-21 11:45:00
3kabir@mail.inhttps://shop.example.in/2024-10-21 12:10:40
4meera@mail.inhttps://shop.example.in/sale?utm_source=google&utm_medium=cpc2024-10-22 09:30:05
5zara@mail.inNULL2024-10-22 18:22:19
6rohan@mail.inhttps://shop.example.in/app?utm_source=newsletter2024-10-23 07:55:00
7ira@mail.inhttps://shop.example.in/?utm_source=instagram&utm_content=reel2024-10-23 20:14:31

Output

utm_sourcesignups
direct2
google2
instagram2
newsletter1
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.

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

ColumnType
signup_idint
emailvarchar
landing_urlvarchar
signed_up_atdatetime

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_idemaillanding_urlsigned_up_at
1aarav@mail.inhttps://shop.example.in/diwali?utm_source=google&utm_medium=cpc&utm_campaign=diwali2024-10-21 10:02:11
2diya@mail.inhttps://shop.example.in/?utm_medium=social&utm_source=instagram2024-10-21 11:45:00
3kabir@mail.inhttps://shop.example.in/2024-10-21 12:10:40
4meera@mail.inhttps://shop.example.in/sale?utm_source=google&utm_medium=cpc2024-10-22 09:30:05
5zara@mail.inNULL2024-10-22 18:22:19
6rohan@mail.inhttps://shop.example.in/app?utm_source=newsletter2024-10-23 07:55:00
7ira@mail.inhttps://shop.example.in/?utm_source=instagram&utm_content=reel2024-10-23 20:14:31

Output

utm_sourcesignups
direct2
google2
instagram2
newsletter1

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_source

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

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