Write PostgreSQL string-manipulation query
Company: Bloomberg
Role: Data Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: This question evaluates proficiency in PostgreSQL string manipulation, regular expressions, URL parsing, key-value parsing and timestamp-based filtering for data transformation tasks.
Read the full Bloomberg Data Engineer interview experience this question came from
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`.
- The `query` column contains semicolon-separated key-value pairs like: `utm_source=Email;utm_campaign=Fall_2025; experiment=A/B-12 `.
Write a single PostgreSQL SELECT query that returns, for each qualifying row:
1) the registrable domain extracted from `page` (e.g., `example.com` from `shop.example.com`),
2) the first path segment from `page` (e.g., `cat` from `/cat/electronics?...`),
3) the value of the `ref` URL parameter from `page` if present, else NULL,
4) two columns parsed from `query`: `utm_source` and `utm_campaign` (keys are case-insensitive; values must be trimmed; return NULL if missing),
5) all extracted string outputs must be lowercased,
6) filter 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.
Return columns: session_id, registrable_domain, first_path_segment, ref, utm_source, utm_campaign.
Tables
clickstream(session_id TEXT, page TEXT, query TEXT, ts TIMESTAMP)
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.