Write PostgreSQL string-manipulation query

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at Bloomberg.

Q: What role is this question designed for?

This question is commonly asked for Data Engineer candidates at Bloomberg during technical interviews.

Question

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.

PracHub · Accepted Answer

This question evaluates proficiency in PostgreSQL string manipulation, regular expressions, URL parsing, key-value parsing and timestamp-based filtering for data transformation tasks.

Quick Overview

Quick Overview