Write Postgres string parsing and aggregation query
Company: Bloomberg
Role: Data Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You are given a PostgreSQL table events(user_id TEXT, raw TEXT) where raw stores pipe-delimited key=value pairs, for example 'user=U1|country=US|ts=2025-08-31 14:25:00'. Write one SQL query that:
(
1) parses raw into columns user, country, ts using only PostgreSQL string/regex functions (e.g., substring/substr, split_part, regexp_matches/regexp_replace);
(
2) casts ts to TIMESTAMP and filters rows where country = 'US' and the timestamp falls in August 2025; and
(
3) returns the daily count of distinct user_id for those rows, ordered by day. Also name the BigQuery functions you might initially consider and state the PostgreSQL equivalents you used.
Overview: This question evaluates proficiency in SQL string parsing and regular expressions, type casting to timestamps, date-based filtering, distinct aggregation, and awareness of cross-dialect function equivalence, and is categorized under Data Manipulation (SQL/Python) and database querying.
Read the full Bloomberg Data Engineer interview experience this question came from
You are given a PostgreSQL table `events(user_id TEXT, raw TEXT)`. Each `raw` value stores pipe-delimited `key=value` pairs, for example `user=U1|country=US|ts=2025-08-31 14:25:00`. The three keys `user`, `country`, and `ts` may appear in any order, but each appears at most once per row.
Write a single PostgreSQL query that:
1. Parses each `raw` value into separate values for `user`, `country`, and `ts` using only PostgreSQL string/regex functions (for example `substring(string FROM 'pattern')`, `split_part`, `regexp_matches`, or `regexp_replace`).
2. Casts the extracted `ts` to `TIMESTAMP` and keeps only rows where `country = 'US'` **and** the timestamp falls in August 2025 (inclusive range `'2025-08-01 00:00:00'` through `'2025-08-31 23:59:59'`).
3. Returns the daily count of **distinct `user_id`** for those rows.
**Output:** one row per calendar day, with columns `day` (the date, type `DATE`) and `distinct_user_count` (the number of distinct `user_id` values on that day). Order the result by `day` ascending.
Additionally, in SQL comments inside your query, name one or more BigQuery functions you might initially reach for (for example `REGEXP_EXTRACT`, `SPLIT`, `PARSE_TIMESTAMP`) and state the PostgreSQL equivalents you actually use — since BigQuery-only functions are not valid PostgreSQL.
Tables
events(user_id TEXT, raw TEXT)
Hints
- Use `substring(raw FROM 'key=([^|]+)')` to regex-extract each value — the `[^|]+` stops at the next pipe, so key order doesn't matter.
- Cast the extracted `ts` text to `TIMESTAMP` with `::timestamp`, then apply both the `country = 'US'` filter and the August 2025 bounded range.