Bloomberg·Software Engineer·Technical Phone Screen·Intermediate

Intermediate

Jul 2026

Summary

Bloomberg SQL round for a software engineering role. One question, but it was a beast. The kind of problem where you think you understand it and then re-read requirement 3 and realize you don't.

Questions Asked (1)

Given a PostgreSQL table logging HTTP requests (method, path, timestamp), write a single SQL query that pivots request data for June 2021 into a report with one row per HTTP method and one column per day of the week (Monday through Sunday). Each cell should show the most popular file extension for that method on that weekday, NULL if no requests exist, and a sorted comma-separated list of tied extensions if there's a frequency tie. Paths without a dot should be excluded from extension counting, query strings stripped, and results sorted by method ascending.

Algorithms & Data StructuresData ModelingTechnical Trade-offs

Author's notes

This wrecked me a little.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Break the problem into logical steps: first filter and extract the file extension from the path, then aggregate counts per method, weekday, and extension, then rank extensions within each group, and finally pivot the results into the desired format. Use CTEs to keep the query readable and modular, and handle ties by collecting all top-ranked extensions into a sorted comma-separated string.

Pro tip: Mention that you would test the query on a small dataset and consider performance implications, such as indexing on timestamp and method, and using efficient string functions for extension extraction. Also, clarify assumptions about path format (e.g., query strings) and tie-breaking rules.

1. Filter and extract extension

Filter rows for June 2021, strip query strings, and extract the file extension from the path. Exclude paths without a dot.

2. Aggregate counts

Group by method, weekday, and extension to count occurrences. Use date functions to derive the weekday from the timestamp.

3. Rank extensions

Within each method and weekday, rank extensions by count descending. Identify the top count and collect all extensions with that count.

4. Pivot and format

Pivot the results so each method is a row and each weekday is a column. For each cell, produce a sorted comma-separated list of top extensions or NULL if no requests.

5. Sort and finalize

Sort the final result by method ascending. Ensure the query is a single SQL statement, using CTEs for clarity.

Key Points to Mention

Use of date_trunc or EXTRACT to filter by month and to get weekday.
String manipulation to strip query strings (e.g., split_part on '?') and extract extension (e.g., substring after last dot).
Window functions like RANK() or DENSE_RANK() to identify top extensions per group.
Aggregation with STRING_AGG to concatenate tied extensions in sorted order.
Pivoting using CASE statements or FILTER clause with conditional aggregation.
Handling NULLs for weekdays with no requests and ensuring the output has all 7 columns.

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