Write Postgres string parsing and aggregation query

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.

Parse pipe-delimited key=value strings and aggregate distinct users by day in PostgreSQL

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.

Table events

column	type	constraints
user_id	TEXT	NOT NULL
raw	TEXT	NOT NULL

Sample data:

raw	user_id
user=U1|country=US|ts=2025-08-01 10:00:00	U1
country=US|ts=2025-08-01 15:45:00|user=U1	U1
user=U2|country=US|ts=2025-08-01 11:30:00	U2
user=U3|country=CA|ts=2025-08-01 12:00:00	U3
user=U2|country=US|ts=2025-08-02 09:15:00	U2
user=U4|country=US|ts=2025-08-15 20:00:00	U4
user=U5|country=US|ts=2025-09-01 00:00:00	U5
user=U6|country=US|ts=2024-08-01 10:00:00	U6
ts=2025-08-01 08:00:00|user=U7|country=US	U7

Expected output

One row per calendar day (DATE) for US events in August 2025, with the count of distinct user_id values that day, ordered by day ascending.

day	distinct_user_count
2025-08-01	3
2025-08-02	1
2025-08-15	1

Hints

1. Use substring(raw FROM 'key=([^|]+)') to regex-extract each value — the [^|]+ stops at the next pipe, so key order doesn't matter.
2. Cast the extracted ts text to TIMESTAMP with ::timestamp, then apply both the country = 'US' filter and the August 2025 bounded range.
3. GROUP BY ts::date and COUNT(DISTINCT user_id) give the per-day distinct user count; add ORDER BY day for a stable result.