Parse a video request log and report per-video cache hit rate extremes

A shared coding pad contains a CSV file of request logs for video content. Each row records a requested URL and whether the cache served the request; the only two statuses are hit and miss. Write code that parses the file, collects the video logs, extracts each request's video ID from its URL, and computes each video's cache hit rate: its hits divided by its total requests. Then report: - the largest hit rate that is not 1, together with the log information for that video, and - the smallest hit rate that is not 0, together with the log information for that video. The URLs in the file come in two different formats, so the video ID must be extracted correctly from both. The task needs no particular algorithm; it is judged on whether you write it the way you would write production code. The report does not give the column names or the two URL formats. The sample below assumes plausible ones so the task can be practiced; in the interview, read the real ones from the file. ```text timestamp,url,cache_status 1000,https://media.example.com/video/v101/seg_001.ts,HIT 1001,https://media.example.com/play?vid=v101&seg=2,HIT 1002,https://media.example.com/video/v101/seg_003.ts,MISS 1003,https://media.example.com/play?vid=v202&seg=1,MISS 1004,https://media.example.com/video/v202/seg_002.ts,HIT 1005,https://media.example.com/video/v303/seg_001.ts,HIT 1006,https://media.example.com/play?vid=v303&seg=2,HIT 1007,https://media.example.com/play?vid=v404&seg=1,MISS 1008,https://media.example.com/video/v505/seg_001.ts,MISS 1009,https://media.example.com/video/v505/seg_002.ts,MISS 1010,https://media.example.com/play?vid=v505&seg=3,MISS 1011,https://media.example.com/play?vid=v505&seg=4,HIT ``` In this sample the first format carries the ID as a path segment (`/video/<video_id>/<segment>`) and the second as a query parameter (`/play?vid=<video_id>&seg=<n>`), and several videos appear in both formats. With these assumptions the expected report names v101 (2 hits out of 3 requests) as the largest rate that is not 1, and v505 (1 hit out of 4) as the smallest rate that is not 0. ### Clarifying Questions - Is the hit rate computed per video across all of its requests, as assumed here, or over some other grouping? - What should the "log information" contain: the video ID with its counts and rate, or the log rows of that video? - Can the file contain rows that are not video requests, malformed rows, or statuses other than hit and miss, and should those be skipped or stop the run? - If two videos share the extreme rate, should both be reported? ### Part 1 — Parse the file and extract video IDs Read the CSV and turn each row into a record holding the video ID and the cache status, accepting both URL formats. ```hint Two formats, one ID Look at what the two URL formats have in common and where they differ, and decide what your extractor should do with a URL that matches neither. ``` #### What This Part Should Cover - Reading the CSV with a real parser rather than splitting lines on commas - Extracting the ID from both formats through a parsed URL, not fragile substring searches - What happens to malformed rows, unknown statuses and URLs that match neither format ### Part 2 — Compute and report the hit rates Aggregate the records per video and produce the two results above, each with that video's log information. ```hint Exclusions and ties Decide what the answer is when no video has a rate other than exactly 1, and when two videos share the extreme rate. ``` #### What This Part Should Cover - Counting hits and requests per video in a single pass - Excluding rates of exactly 1 and exactly 0, and comparing rates without floating-point doubt - Behaviour when no video qualifies, and a deterministic rule for ties ### Part 3 — Make it production quality Turn the script into code you would ship: its structure, its error handling, its tests, and its behaviour as the file grows large. ```hint Tests that catch the trap Ask which small inputs would expose a parser that handles only one of the URL formats. ``` #### What This Part Should Cover - Small, typed, testable functions, separated from file reading and printing - Unit tests for both URL formats, malformed rows and the exclusion edge cases - A single streaming pass whose memory grows with the number of videos rather than the number of rows ### What a Strong Answer Covers - Correct per-video hit rates, with both URL formats normalized to the same ID - A clean structure in which parsing, aggregation and reporting are separate and individually testable - Explicit handling of bad input, empty results and ties, rather than crashes or silently wrong answers - Fast tests that cover the cases that break naive versions - Clear communication of the assumptions about the formats and the output ### Follow-up Questions - The log grows to hundreds of gigabytes. What changes in your code? - A third URL format appears next month. How does your code make that change small and safe? - Should a video with only a handful of requests be able to win either comparison, and how would you change the report if not? - How would you produce the same report continuously from a live stream of log events?