Load and visualize large CSV robustly
Company: Voleon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You're screen-sharing in a HackerRank environment with Python 3, pandas, numpy, seaborn, and matplotlib available. You are given a single file data.csv (~1.5 GB) whose delimiter is unknown (',' or ';') and encoding is either UTF-8 or latin-1. Columns: id:int, date:YYYY-MM-DD, region:str, spend:float, clicks:int, signups:int. Up to 5% values may be missing; there can be exact duplicate rows; and some rows have clicks=0. Write code to: (1) detect delimiter and encoding without loading the full file; then load in chunks while keeping peak memory under 1 GB; (2) drop exact duplicates and enforce dtypes; (3) impute missing spend with the median within region; impute missing clicks/signups with 0 only if the entire row's non-null feature count >= 4, otherwise drop the row—justify this rule; (4) create cpc = spend / clicks with safe division and winsorize cpc at the 1st/99th percentiles by region; (5) produce and save: (a) a scatter plot of spend vs signups with a LOWESS smoothed line and 95% CI, (b) a boxplot of signups by region sorted by median, and (c) a time-series line of daily total signups; (6) briefly explain your memory/time complexity choices and how you'd test this code. Provide runnable, end-to-end code with any assumptions stated explicitly.
Overview: This question evaluates competency in large-scale data manipulation and preprocessing, including robust file I/O (delimiter and encoding detection), memory-efficient chunked loading, dtype enforcement, deduplication, imputation strategy design, outlier handling, and statistical visualization techniques.
You are given a large table of advertising events with possible missing values, zero-click rows, and exact duplicate records. Each row represents daily performance for a region.
Table: ad_events
- id: internal row identifier (may contain duplicates)
- event_date: date of the record (YYYY-MM-DD)
- region: region name
- spend: advertising spend amount
- clicks: number of clicks
- signups: number of signups
Business rules for cleaning and transformation:
1. Remove exact duplicate rows (rows that have the same values in all columns: id, event_date, region, spend, clicks, signups), keeping only one copy of each.
2. For each region, compute the median spend using only non-NULL spend values after deduplication. Impute spend for rows where spend IS NULL with that region's median spend.
3. For each row (after spend imputation), count how many of the following columns are non-NULL: event_date, region, spend (imputed), clicks, signups.
- If this non-null feature count is at least 4, then:
- Impute missing clicks as 0.
- Impute missing signups as 0.
- If the non-null feature count is less than 4, drop the row entirely (do not include it in further calculations).
4. On the remaining rows, compute cpc (cost per click) as spend / clicks, but:
- If clicks is NULL or 0, set cpc to NULL (safe division).
5. For each region, winsorize cpc at the 1st and 99th percentiles:
- Compute the 1st percentile and 99th percentile of cpc per region using continuous percentiles (e.g., PERCENTILE_CONT).
- Replace cpc values below the 1st percentile with the 1st percentile value.
- Replace cpc values above the 99th percentile with the 99th percentile value.
- For regions where all cpc values are NULL, leave cpc as NULL.
6. From this cleaned and winsorized data, produce one aggregated result per date with:
- event_date
- total_signups: sum of (imputed) signups for that date
- avg_winsorized_cpc: average of the winsorized cpc values for that date (ignoring NULL cpc).
Write a single SQL query (you may use common table expressions) that implements the above steps and returns the final aggregation with columns: event_date, total_signups, avg_winsorized_cpc, ordered by event_date ascending.
Tables
ad_events(id INT, event_date DATE, region VARCHAR(20), spend DECIMAL(10,2), clicks INT, signups INT)
Hints
- Use a ROW_NUMBER() window function over all columns to remove exact duplicate rows via a CTE.
- Compute medians and percentiles with PERCENTILE_CONT and implement winsorization via CASE expressions on cpc values.