Merge ad CSVs and compute CTR

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.

Merge ad CSVs, forward-fill dates, and compute CTR and max watch-time dates

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.

Table platforms

column	type	constraints
platform_id	INT	PRIMARY KEY
platform	VARCHAR(20)	NOT NULL

Sample data:

platform	platform_id
Web	10
Mobile	20

Table ads

column	type	constraints
ad_id	INT	PRIMARY KEY
platform_id	INT	NOT NULL
video_id	INT	NOT NULL

Sample data:

ad_id	platform_id	video_id
1001	10	501
1002	10	502
1003	20	501

Table videos

column	type	constraints
video_id	INT	PRIMARY KEY
title	VARCHAR(100)	NOT NULL
duration_sec	INT	NOT NULL

Sample data:

duration_sec	title	video_id
30	Summer Promo	501
45	Winter Promo	502

Table totals

column	type	constraints
row_id	INT	PRIMARY KEY
raw_date	VARCHAR(10)	NOT NULL
ad_id	INT	NOT NULL
plays	INT	NOT NULL
clicks	INT	NOT NULL
watch_time_sec	INT	NOT NULL

Sample data:

ad_id	clicks	plays	raw_date	row_id	watch_time_sec
1001	18	150	2008-01-01	1	3400
1002	25	220	-	2	7100
1003	5	90	-	3	1800
1001	17	130	2008-01-02	4	3200
1002	22	210	-	5	6800

Expected output

Six rows: the 3 'top_ads' (ranked 1=ad 1002, 2=ad 1001, 3=ad 1003) followed by 3 'max_watch_date' rows (one per ad, each peaking on 2008-01-01 in this data). Columns not relevant to a result_type are NULL.

ad_id	ctr	max_watch_date	max_watch_time_sec	platform	result_type	top_rank	total_clicks	total_plays	total_watch_time_sec	video_title
1002	0.1093	NULL	NULL	Web	top_ads	1	47	430	13900	Winter Promo
1001	0.1250	NULL	NULL	Web	top_ads	2	35	280	6600	Summer Promo
1003	0.0556	NULL	NULL	Mobile	top_ads	3	5	90	1800	Summer Promo
1001	0.1250	2008-01-01	3400	Web	max_watch_date	NULL	35	280	6600	Summer Promo
1002	0.1093	2008-01-01	7100	Web	max_watch_date	NULL	47	430	13900	Winter Promo
1003	0.0556	2008-01-01	1800	Mobile	max_watch_date	NULL	5	90	1800	Summer Promo

Hints

1. 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.
2. 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.
3. PostgreSQL rejects an expression (e.g. CASE) in an ORDER BY applied directly to a UNION. Add a numeric sort_priority column to each branch, wrap the UNION in a CTE/subquery, and ORDER BY that column in the outer SELECT.