Write PostgreSQL string-manipulation query

Q: 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.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) 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: (

extracts the registrable domain from page (e.g., example.com), (
extracts the first path segment (e.g., cat), (
extracts the value of the ref query parameter if present, else NULL, (
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), (
lowercases all extracted string outputs, and (
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.

Write PostgreSQL string-manipulation query

Overview

Comments (0)