Merge ad CSVs and compute CTR | Capital One SQL Question

Quick Overview

This question evaluates SQL data manipulation competencies such as cleaning and merging CSVs, forward-filling missing dates, correct multi-table joins without duplication, use of window functions, time-windowed aggregations, and computation of click-through rate (CTR).

Merge ad CSVs and compute CTR

Company: Capital One

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Online Assessment

Using SQL, clean and merge four CSVs and answer all parts exactly. Schema and sample rows (assume types: date is DATE, others INT/VARCHAR): platforms(platform_id, platform) +-------------+----------+ | platform_id | platform | | 10 | Web | | 20 | Mobile | +-------------+----------+ ads(ad_id, platform_id, video_id) +------+-------------+----------+ | ad_id| platform_id | video_id | | 1001 | 10 | 501 | | 1002 | 10 | 502 | | 1003 | 20 | 501 | +------+-------------+----------+ videos(video_id, title, duration_sec) +----------+--------------+--------------+ | video_id | title | duration_sec | | 501 | Summer Promo | 30 | | 502 | Winter Promo | 45 | +----------+--------------+--------------+ totals(date, ad_id, plays, clicks, watch_time_sec) +------------+------+-------+--------+------------------+ | date | ad_id| plays | clicks | watch_time_sec | | 2008-01-01 | 1001 | 150 | 18 | 3400 | | - | 1002 | 220 | 25 | 7100 | | - | 1003 | 90 | 5 | 1800 | | 2008-01-02 | 1001 | 130 | 17 | 3200 | | - | 1002 | 210 | 22 | 6800 | +------------+------+-------+--------+------------------+ Notes: In totals, a '-' in the date column means “same as the most recent non-dash date above in file order.” Assume file order is by appearance and stable. Tasks: (a) In SQL, forward-fill the date within totals so that each row has a valid DATE; do not use procedural code; assume you can reference row_number() over file order. (b) Produce a 7-day window starting 2008-01-01 (inclusive) and ending 2008-01-07 (inclusive). (c) Compute, for that window, per ad_id and per platform, total_plays, total_clicks, total_watch_time_sec, and CTR = CASE WHEN total_plays>0 THEN total_clicks*1.0/total_plays ELSE NULL END. (d) Return: (1) the overall top 3 ads by total_plays in the window (tie-break by higher CTR, then lower ad_id), with platform name and video title; and (2) for every ad present in the window, the date within the window on which its watch_time_sec is maximal (break ties by earliest date). (e) Ensure all joins are correct and no row duplication occurs (explain the join keys you used). Provide a single SQL query (CTEs allowed) that outputs both result sets, clearly labeled.

Overview: This question evaluates SQL data manipulation competencies such as cleaning and merging CSVs, forward-filling missing dates, correct multi-table joins without duplication, use of window functions, time-windowed aggregations, and computation of click-through rate (CTR).

You are given four staging tables loaded from CSVs. Using **PostgreSQL** and **SQL only** (no procedural code), clean, merge, and aggregate the data, then return a single combined result set. ### Tables - `platforms(platform_id, platform)` - `ads(ad_id, platform_id, video_id)` - `videos(video_id, title, duration_sec)` - `totals(row_id, raw_date, ad_id, plays, clicks, watch_time_sec)` Notes about `totals`: - `row_id` is the original CSV file order (unique and strictly increasing). - `raw_date` is a `VARCHAR`. It is either a valid `'YYYY-MM-DD'` date string, or the literal string `'-'`, which means "same date as the most recent non-dash row above it in `row_id` order." ### Steps 1. **Forward-fill** `raw_date` into a real DATE (`event_date`): for every row whose `raw_date = '-'`, use the most recent non-dash date from a preceding row in `row_id` order. (Hint: a windowed `MAX` over `row_id` works.) 2. Keep only rows whose `event_date` is in the **7-day window 2008-01-01 through 2008-01-07, inclusive**. 3. For each `ad_id` (joined to its platform and video), compute over that window: - `total_plays = SUM(plays)` - `total_clicks = SUM(clicks)` - `total_watch_time_sec = SUM(watch_time_sec)` - `ctr = ROUND(total_clicks * 1.0 / total_plays, 4)` when `total_plays > 0`, else `NULL`. 4. Produce **two labeled result sets in one combined output**: - **`'top_ads'`** — the overall **top 3** ads by `total_plays` in the window. Break ties by **higher `ctr`**, then **lower `ad_id`**. Number them `top_rank` 1..3. - **`'max_watch_date'`** — for **every** ad in the window, the `event_date` on which its per-day `watch_time_sec` is **maximal** (break ties by the **earliest** date). Report that date and the per-day `watch_time_sec` on it. ### Required output columns (one combined result) `result_type` (`'top_ads'` | `'max_watch_date'`), `ad_id`, `platform`, `video_title`, `total_plays`, `total_clicks`, `total_watch_time_sec`, `ctr`, `max_watch_date` (DATE; `NULL` for `top_ads` rows), `max_watch_time_sec` (INT; `NULL` for `top_ads` rows; the per-day `watch_time_sec` on the max date for `max_watch_date` rows), `top_rank` (INT 1..3 for `top_ads` rows; `NULL` for `max_watch_date` rows). ### Ordering Return all `top_ads` rows first (by `top_rank` ascending), then all `max_watch_date` rows (by `ad_id` ascending). Join only on the proper keys (`ads.ad_id`, `platforms.platform_id`, `videos.video_id`) so no rows are duplicated. The query should work for the general case, not just this sample.

Tables

platforms(platform_id INT, platform VARCHAR(20))

ads(ad_id INT, platform_id INT, video_id INT)

videos(video_id INT, title VARCHAR(100), duration_sec INT)

totals(row_id INT, raw_date VARCHAR(10), ad_id INT, plays INT, clicks INT, watch_time_sec INT)

Hints

Forward-fill with a running window: MAX(CASE WHEN raw_date <> '-' THEN CAST(raw_date AS DATE) END) OVER (ORDER BY row_id ROWS UNBOUNDED PRECEDING). Dash rows become NULL so MAX keeps the last real date.
Aggregate per ad_id first (joining ads->platforms and ads->videos on their keys to avoid row duplication), then build the two result sets and UNION ALL them.