Load and prepare JSON for modeling
Company: Reddit
Role: Machine Learning Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Using Python in a Jupyter notebook, load a JSON dataset with fields:
(
1) hours spent reading A posts (float),
(
2) hours spent reading B posts (float),
(
3) hours spent reading C posts (float),
(
4) current post category (A/B/C), and
(
5) click (binary label). Convert it into a pandas DataFrame suitable for modeling: enforce correct data types, encode the categorical post category, validate the schema, and run checks confirming no missing values or class imbalance. Provide code that performs the load, transformation, and validation.
Overview: This question evaluates competency in data preprocessing and validation for machine learning—enforcing correct data types, encoding categorical variables, detecting missing values, and assessing class balance.
You are given a table that stores user reading behavior and whether the user clicked on the current post. Each row represents one user-session. The columns are:
- hours_read_A: hours spent reading posts of category A (float-like numeric)
- hours_read_B: hours spent reading posts of category B (float-like numeric)
- hours_read_C: hours spent reading posts of category C (float-like numeric)
- current_category: category of the current post ('A', 'B', or 'C')
- click: binary label (1 if the user clicked, 0 otherwise)
Write a SQL query that produces a model-ready dataset with the following requirements:
1) Ensure all numeric columns (hours_read_A, hours_read_B, hours_read_C, click) are explicitly cast to appropriate numeric types.
2) Encode the categorical current_category column into three numeric dummy variables: cat_A, cat_B, cat_C (each 0/1), where exactly one of them is 1 for each row.
3) Exclude any rows that have NULL in hours_read_A, hours_read_B, hours_read_C, current_category, or click (i.e., only keep fully complete records).
4) Include simple validation columns to check for class balance: output total number of rows, total number of positive labels (click = 1), and total number of negative labels (click = 0) as windowed aggregate columns that are repeated on every row.
Return one result set with one row per original session, containing: user_id, the three hour columns, click, the three dummy variables (cat_A, cat_B, cat_C), and the three validation columns (total_rows, total_positive, total_negative).
Tables
user_activity(user_id INT, hours_read_A DECIMAL(4,1), hours_read_B DECIMAL(4,1), hours_read_C DECIMAL(4,1), current_category CHAR(1), click INT)
Hints
- Use CASE expressions to turn the categorical current_category column into separate 0/1 indicator columns.
- Use window functions (COUNT(*) OVER () and SUM(...) OVER ()) to compute overall class counts and total rows without changing the row-level granularity.