URL Path Parsing — Solution & ExplanationProblems / URL Path Parsing / EditorialApproach

Capture the first path segment with regexp_extract(url, 'https?://[^/]+/([^/?#]+)', 1): https? accepts both schemes, [^/]+ consumes the domain, and the capture group [^/?#]+ stops at the next /, ?, or # — so query strings and fragments never leak into the section.

When a URL has no path segment (https://shop.example.com, a bare /, or /?ref=ad), regexp_extract returns an empty string, not NULL. Map it with coalesce(nullif(section, ''), 'home').

Group by section, count(*) as views, and order by views DESC, section.

Key InsightThe no-match behavior is the trap: both DuckDB and Spark return '' from regexp_extract when the pattern fails, so a plain coalesce never fires. You need nullif(..., '') (SQL) or an explicit F.when(col == '', ...) (PySpark) to route homepage traffic into the home bucket.Hints

The pattern 'https?://[^/]+/([^/?#]+)' does the heavy lifting: [^/]+ consumes the domain, and the capture group [^/?#]+ stops at the next /, ? or #.

regexp_extract returns an empty string (not NULL) when nothing matches — homepage URLs need that '' mapped to 'home' via coalesce(nullif(..., ''), 'home') or a CASE.

In PySpark, F.regexp_extract also returns '' on no match; wrap it with F.when(col == '', F.lit('home')).otherwise(col) before grouping.

Solve URL Path Parsing yourself →