← Bloomberg Interview Insights

Bloomberg·Software Engineer·Technical Phone Screen·Senior

Senior
Jun 2026

Summary

Bloomberg data engineering interview, technical phone screen or take-home style question involving some gnarly PostgreSQL string parsing. One question, no fluff, just raw SQL and a lot of edge cases to think through.

Questions Asked (1)

Q1

Given a PostgreSQL clickstream table with URL and semicolon-delimited query columns, write a single SELECT that extracts the registrable domain and first path segment from the URL, pulls a specific query parameter from the URL, parses two UTM fields from the semicolon-delimited column (case-insensitive, trimmed, NULL if missing), lowercases all string outputs, and filters to a specific date range. Core string/regex functions only, no JSON or external extensions.

System DesignTechnical Trade-offsData Modeling
Author's notes

This one is deceptively wide.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Break the problem into modular CTEs: one to parse the URL into domain, path, and query parameter, and another to parse the semicolon-delimited column into UTM fields. Then apply lowercasing, trimming, and NULL handling in the final SELECT, with a WHERE clause for the date range.

Pro tip: Mention that you'd validate regex patterns against edge cases (e.g., URLs with ports, subdomains, or missing query strings) and consider performance implications of regex on large datasets, suggesting indexing strategies if needed.

1. Parse the URL

Use regexp_matches or substring with regex to extract the registrable domain (e.g., example.com from sub.example.com), the first path segment (e.g., /path from /path/to/page), and the target query parameter value.

2. Parse the semicolon-delimited column

Use string_to_array or regexp_split_to_array to split the column by semicolons, then extract the UTM fields by matching key=value pairs case-insensitively, trimming whitespace, and returning NULL if not found.

3. Normalize outputs

Apply lower() to all string outputs and trim() where necessary to ensure consistent formatting.

4. Filter by date range

Add a WHERE clause to restrict rows to the specified date range, using appropriate date/timestamp comparisons.

5. Assemble final SELECT

Combine the parsed fields into a single SELECT statement, using CTEs for readability and to avoid repeating complex expressions.

Key Points to Mention

  • Use of regexp_matches with capture groups for URL parsing, handling optional parts like protocol and subdomains.
  • Handling of missing query parameters or UTM fields by returning NULL, using NULLIF or CASE expressions.
  • Case-insensitive matching for UTM keys, e.g., using lower() on the key before comparison.
  • Trimming whitespace from extracted values to avoid unexpected spaces.
  • Performance considerations: regex can be expensive, so filter early with date range and consider indexing.
  • Portability: using only core string/regex functions, no JSON or extensions like PostGIS.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.