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.
Quick Answer: 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.