Coding questions · Bloomberg
Create SQL report of top extensions by weekday
This question evaluates a candidate's ability to perform advanced SQL data manipulation and aggregation, encompassing date filtering, string extraction from URL paths, frequency counting with tie resolution, and pivoting results by weekday.
You are given a PostgreSQL table service_access_log with columns: method TEXT (HTTP verb), path TEXT (URL path that may include a filename and optional query string), requested_at TIMESTAMP. Write a single SQL query that produces a report with columns: method, monday, tuesday, wednesday, thursday, friday, saturday, sunday. Requirements:
- Consider only requests in June 2021 (inclusive, based on requested_at).
- One row per distinct method seen in June 2021.
- For each method and each day-of-week column, return: NULL if there are no requests for that method on that weekday; otherwise the most frequent file extension requested on that weekday for that method. Define file extension as the substring after the last '.' of the final path segment, ignoring any query string or fragment; if a path has no '.', exclude that request from extension popularity.
- If exactly one extension has the maximum frequency, return that extension (without the dot).
- If multiple extensions tie for maximum frequency, return a comma-delimited list of the tied extensions sorted alphabetically ascending.
- Sort the output rows by method ascending.
Top file extensions by HTTP method and weekday in June 2021
Table service_access_log
| column | type | constraints |
|---|---|---|
| method | TEXT | NOT NULL |
| path | TEXT | NOT NULL |
| requested_at | TIMESTAMP | NOT NULL |
Sample data:
| method | path | requested_at |
|---|---|---|
| GET | /index.html | 2021-06-07 10:00:00 |
| GET | /styles/main.css | 2021-06-07 11:00:00 |
| GET | /about.html?ref=home | 2021-06-07 12:00:00 |
| GET | /styles/extra.css?version=2#top | 2021-06-07 13:00:00 |
| GET | /images/logo.png | 2021-06-07 09:00:00 |
| GET | /script.js | 2021-06-09 09:30:00 |
| GET | /api/data | 2021-06-09 10:00:00 |
| GET | /assets/app.js?cache=false | 2021-06-09 11:00:00 |
| GET | /readme | 2021-06-06 08:00:00 |
| GET | /old.html | 2021-05-31 12:00:00 |
| GET | /new.html | 2021-07-01 12:00:00 |
| POST | /form/submit | 2021-06-07 14:00:00 |
| POST | /upload.php | 2021-06-03 15:00:00 |
| POST | /submit.php?debug=true | 2021-06-10 16:00:00 |
| POST | /payload/data.json | 2021-06-10 16:30:00 |
| PUT | /api/users/123 | 2021-06-08 10:00:00 |
| DELETE | /remove/item/9 | 2021-05-20 09:00:00 |
Expected output
One row per HTTP method seen in June 2021. Each weekday column contains the most frequent file extension (or comma-separated list of tied extensions) requested on that weekday for that method, considering only June 2021 requests and only paths that contain a dot in the final segment. NULL indicates that no qualifying requests with a file extension exist for that method on that weekday.
| friday | method | monday | saturday | sunday | thursday | tuesday | wednesday |
|---|---|---|---|---|---|---|---|
| NULL | GET | css,html | NULL | NULL | NULL | NULL | js |
| NULL | POST | NULL | NULL | NULL | php | NULL | NULL |
| NULL | PUT | NULL | NULL | NULL | NULL | NULL | NULL |
Hints
- First extract a clean filename from path by removing query strings and fragments, then taking only the last segment after the final '/'.
- Count extensions per (method, weekday, extension), find the maximum count per (method, weekday), then use STRING_AGG over the tied extensions and pivot these results into monday–sunday columns with conditional aggregation.