Coding questions · Bloomberg
Write PostgreSQL string-manipulation query
This question evaluates proficiency in PostgreSQL string manipulation, regular expressions, URL parsing, key-value parsing and timestamp-based filtering for data transformation tasks.
You are given a PostgreSQL table clickstream(session_id TEXT, page TEXT, query TEXT, ts TIMESTAMP). The page column contains full URLs like 'https://shop.example.com/cat/electronics?ref=ad_123', and query contains semicolon-separated key-value pairs like 'utm_source=Email;utm_campaign=Fall_2025; experiment=A/B-12 '. Write a single PostgreSQL SELECT that: (1) extracts the registrable domain from page (e.g., example.com), (2) extracts the first path segment (e.g., cat), (3) extracts the value of the ref query parameter if present, else NULL, (4) parses query to return two columns utm_source and utm_campaign (keys are case-insensitive; values should be trimmed; return NULL if a key is missing), (5) lowercases all extracted string outputs, and (6) filters rows to those with ts between 2025-09-01 00:00:00 and 2025-09-30 23:59:59 inclusive. Constraints: Use only core PostgreSQL string/regex functions (e.g., substring, position, split_part, regexp_match/regexp_replace, lower, trim); do not use JSON functions or vendor-specific BigQuery functions. Provide the final SQL query.
PostgreSQL URL and key-value string parsing with regex
Table clickstream
| column | type | constraints |
|---|---|---|
| session_id | TEXT | PRIMARY KEY, NOT NULL |
| page | TEXT | NOT NULL |
| query | TEXT | |
| ts | TIMESTAMP | NOT NULL |
Sample data:
| page | query | session_id | ts |
|---|---|---|---|
| https://shop.example.com/cat/electronics?ref=ad_123 | utm_source=Email;utm_campaign=Fall_2025; experiment=A/B-12 | s1 | 2025-09-05 10:15:00 |
| https://blog.example.com/cat?ref=AD_999&utm=1 | UTM_SOURCE = Social ; UTM_CAMPAIGN= Launch | s2 | 2025-09-12 09:00:00 |
| https://example.com/checkout/cart | utm_campaign=Retention; foo=bar | s3 | 2025-09-20 20:00:00 |
| http://m.example.com/?a=1 | utm_source=Paid Search;utm_campaign=Brand | s4 | 2025-09-30 23:59:59 |
| https://shop.example.com/cat/toys?ref=late_1 | utm_source=Email;utm_campaign=October | s5 | 2025-10-01 00:00:00 |
| https://shop.example.com/cat/books?ref=early_1 | utm_source=Email;utm_campaign=August | s6 | 2025-08-31 23:59:59 |
| https://deals.another.org/deals/summer?ref=partner | utm_source=Affiliate;utm_campaign=Summer_Sale | s7 | 2025-09-02 08:30:00 |
Expected output
One row per clickstream record in September 2025 (inclusive). Extract and lowercase the registrable domain (last two hostname labels), the first path segment (or NULL if none), the ref URL parameter value (or NULL), and the utm_source/utm_campaign values from the semicolon-separated query string (case-insensitive keys; trimmed values; NULL if missing).
| first_path_segment | ref | registrable_domain | session_id | utm_campaign | utm_source |
|---|---|---|---|---|---|
| cat | ad_123 | \1 | s1 | fall_2025 | |
| cat | ad_999 | \1 | s2 | launch | social |
| checkout | NULL | \1 | s3 | retention | NULL |
| NULL | NULL | \1 | s4 | brand | paid search |
| deals | partner | \1 | s7 | summer_sale | affiliate |
Hints
- Use regexp_match to extract the hostname, the first path segment, and the ref parameter value; remember it returns a text[] array.
- Use case-insensitive regex with (?i) and allow optional whitespace around keys and '=' when parsing the semicolon-separated query string.