This question evaluates skills in scalable data ingestion, robust data cleaning and normalization, memory-efficient joins and aggregations, and handling heterogeneous file encodings and delimiters within SQL/Python workflows; Domain: Data Manipulation (SQL/Python).
You receive four CSV files that must be merged locally on a laptop with 8 GB RAM, without relying on cloud services:
ads_events.csv: date (YYYY-MM-DD), user_id, ad_id, watch_seconds, clicks
categories.csv: category, category_display_name
Constraints and quirks: files may use different delimiters (comma/semicolon), encodings (UTF-8/Windows-1252), and may contain duplicate header rows and stray BOMs; purchases.csv can exceed 10 million rows. Task: Produce two outputs—(A) category_min_price.csv containing, for every category in products.csv, the lowest price among purchases with stars > 4 (or 0 if none), and (B) weekly_watchtime.csv containing, for every (ISOYear, ISOWeek, category), the total watch_seconds from ads_events.csv after joining ads_events to products via AdID→product_id is not available; instead, a lookup file ad_product_map.csv is provided at runtime with columns ad_id, product_id. Describe and then implement (in pseudocode or Python) a robust merge pipeline that: detects and normalizes encodings and delimiters; streams large files in chunks; de-duplicates by natural keys; handles missing/NULL stars; computes ISO weeks correctly (state the formula or library call); avoids out-of-memory errors (e.g., via chunked pandas + on-disk store, or SQLite/DuckDB with CREATE TABLE AS SELECT); validates row counts and key coverage; and writes both outputs sorted and reproducible. Include exact join keys, data types you will enforce, and how you would unit test the correctness on a 1000-row synthetic sample.
Minimum 5-star price per category (include categories with no qualifying purchases)
You are given product and purchase data loaded into SQL tables.
Create a result that contains one row per category that appears in products, with the lowest purchase price among purchases where stars > 4.
Rules:
Join purchases to products using product_id.
Only consider purchases with stars > 4.
stars can be NULL; NULL should not count as > 4.
If a category has no qualifying purchases, return 0.00 for that category.
Output columns: category, min_price
Sort by category ascending.
Table products
column
type
constraints
product_id
INT
PRIMARY KEY, NOT NULL
category
VARCHAR(50)
NOT NULL
product_name
VARCHAR(200)
NOT NULL
Sample data:
category
product_id
product_name
electronics
1
Phone X
electronics
2
Headphones Pro
books
3
Novel A
toys
4
Puzzle Set
books
5
Cookbook B
Table purchases
column
type
constraints
purchase_id
BIGINT
PRIMARY KEY, NOT NULL
product_id
INT
NOT NULL
price
DECIMAL(10,2)
NOT NULL
stars
INT
Sample data:
price
product_id
purchase_id
stars
699.99
1
101
5
649.99
1
102
4
199.99
2
103
5
15.99
3
104
NULL
12.99
3
105
5
22.5
5
106
3
Table ads_events
column
type
constraints
event_date
DATE
NOT NULL
user_id
VARCHAR(50)
NOT NULL
ad_id
VARCHAR(50)
NOT NULL
watch_seconds
INT
NOT NULL
clicks
INT
NOT NULL
Sample data:
ad_id
clicks
event_date
user_id
watch_seconds
A1
1
2025-01-03
u1
30
A1
0
2025-01-04
u2
45
A4
1
2025-01-05
u3
60
A2
1
2025-01-06
u4
120
A2
0
2025-01-07
u5
80
A3
1
2024-12-30
u6
50
A3
0
2025-01-12
u7
70
A99
1
2025-01-13
u8
40
Table ad_product_map
column
type
constraints
ad_id
VARCHAR(50)
PRIMARY KEY, NOT NULL
product_id
INT
NOT NULL
Sample data:
ad_id
product_id
A1
1
A2
3
A3
4
A4
2
Table categories
column
type
constraints
category
VARCHAR(50)
PRIMARY KEY, NOT NULL
category_display_name
VARCHAR(100)
NOT NULL
Sample data:
category
category_display_name
electronics
Electronics
books
Books
toys
Toys
Expected output
One row per category present in products, with the minimum purchase price among purchases where stars > 4; 0.00 if none.
category
min_price
books
12.99
electronics
199.99
toys
0
Hints
A LEFT JOIN from products to purchases ensures categories with no purchases still appear.
Use an aggregate with a condition (FILTER or CASE) and COALESCE to return 0.00 when there are no qualifying rows.
Weekly (ISO year/week) watch time by category via ad-to-product mapping
You are given ad event data and a runtime mapping from ad_id to product_id.
Compute total watch time per (ISO year, ISO week, category).
Rules:
Join ads_events to ad_product_map on ad_id.
Join to products on product_id to get category.
Exclude ad events that do not have a match in ad_product_map.
Use ISO week definitions (Monday-based weeks; week 1 is the week with the year's first Thursday). In PostgreSQL, you can use EXTRACT(ISOYEAR FROM event_date) and EXTRACT(WEEK FROM event_date).