Load and prepare JSON for modeling

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.

Prepare Click-Through Dataset for Modeling

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).

Table user_activity

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
hours_read_A	DECIMAL(4,1)
hours_read_B	DECIMAL(4,1)
hours_read_C	DECIMAL(4,1)
current_category	CHAR(1)
click	INT

Sample data:

click	current_category	hours_read_A	hours_read_B	hours_read_C	user_id
1	A	1.5	0.5	0	1
0	B	0	2	1	2
1	C	0.5	0.5	2	3
1	A	2	1	0	4
0	B	1	1	1	5
1	C	0	0	3	6

Expected output

One row per user session with numeric hours, binary click, one-hot encoded category columns (cat_A, cat_B, cat_C), and three validation columns showing the total number of rows and how many have click = 1 vs click = 0. All rows in the result are complete (no NULLs in any of the used fields).

cat_A	cat_B	cat_C	click	hours_read_A	hours_read_B	hours_read_C	total_negative	total_positive	total_rows	user_id
1	0	0	1	1.5	0.5	0	2	4	6	1
0	1	0	0	0	2	1	2	4	6	2
0	0	1	1	0.5	0.5	2	2	4	6	3
1	0	0	1	2	1	0	2	4	6	4
0	1	0	0	1	1	1	2	4	6	5
0	0	1	1	0	0	3	2	4	6	6

Hints

1. Use CASE expressions to turn the categorical current_category column into separate 0/1 indicator columns.
2. Use window functions (COUNT(*) OVER () and SUM(...) OVER ()) to compute overall class counts and total rows without changing the row-level granularity.