22. Parse UTM Parameters

Difficulty: Medium · Topics: Strings, Conditional Logic, Filtering & Selection

visits has visit_id and the landing url. Marketing wants the campaign parameters from the query string.

Return visit_id, source (the value of utm_source) and campaign (the value of utm_campaign). A parameter that is absent must be null, not an empty string. Only real parameters count: my_utm_source=x is a different parameter.

Row order does not matter; column names must match. Your code is graded on 3 test cases, including hidden edge cases.

Sample data

visits

visit_id	url
1	https://shop.in/?utm_source=google&utm_campaign=diwali
2	https://shop.in/sale?utm_campaign=summer&utm_source=newsletter
3	https://shop.in/about
4	https://shop.in/?ref=x&utm_source=instagram

Expected output

visit_id	source	campaign
1	google	diwali
2	newsletter	summer
3	null	null
4	instagram	null

Hints

Hint 1

A parameter starts right after ? or &: [?&]utm_source=([^&#]+) captures its value up to the next & or #.

Hint 2

regexp_extract returns an empty string when nothing matches.

Hint 3

Turn the empty string into null with F.when(col == "", None).otherwise(col).

Learn the concepts

regexp_extract · 3 min read. Pull fields out of text with a regular expression, and what it returns when nothing matches.
when / otherwise · 3 min read. CASE WHEN logic inside a column: first match wins, and a missing otherwise means null.
select · 4 min read. Choose, rename and compute columns, and why select beats a chain of withColumn calls.

PySpark functions you'll practise

when / otherwise
select
regexp_extract

Browse

Topics: Window Functions · Joins · Aggregations · Pivot, Unpivot & Rollup · Arrays · Null Handling · Conditional Logic · Dates · Filtering & Selection · Strings

Difficulty: Easy · Medium · Hard · PySpark interview roadmap · Learn · All problems