Create SQL report of top extensions by weekday
Company: Bloomberg
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Technical Screen
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:
1) Consider only requests in June 2021 (inclusive, based on requested_at).
2) One row per distinct method seen in June 2021.
3) 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.
4) If exactly one extension has the maximum frequency, return that extension (without the dot).
5) If multiple extensions tie for maximum frequency, return a comma-delimited list of the tied extensions sorted alphabetically ascending.
6) Sort the output rows by method ascending.
Quick Answer: 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.