Build DID panel and compute effects in SQL

Using the schema and toy data below, write SQL to construct a user-week panel and compute a clean pre/post DID dataset for first reminder exposure. Requirements: (i) Define treat_date as each user’s first reminder_dt; (ii) For users not yet treated, they contribute to controls until their treat_date; (iii) Keep weeks in [treat_date − 8 weeks, treat_date + 8 weeks]; (iv) Exclude users whose household_id has another member treated earlier (to reduce spillovers); (v) Aggregate weekly CSAT (mean) and sessions (sum minutes, sum purchase_flag) per user-week; (vi) Output one row per user-week with columns: user_id, week_start, treated_post (1 if week_start >= treat_week and user has a treat_date, else 0), ever_treated (0/1), household_id, device, csat_mean_wk, minutes_wk, purchases_wk. Then: 1) Write a query to produce group-level pre/post means and the 2x2 DID estimate. 2) In Python, outline code to compute propensity scores (logit) on baseline covariates (device, country, pre-period usage) and perform 1:1 nearest-neighbor matching with caliper=0.05, reporting SMDs before/after matching.

Schema:

• users(user_id INT, signup_date DATE, country VARCHAR, household_id VARCHAR, device VARCHAR)
• subscriptions(sub_id INT, user_id INT, drug_id VARCHAR, start_date DATE, auto_refill TINYINT)
• reminders(user_id INT, reminder_dt DATE, channel VARCHAR)
• csat(user_id INT, event_dt DATE, csat_score INT)
• sessions(user_id INT, session_dt DATE, minutes INT, purchase_flag TINYINT)

Sample data (ASCII): users

subscriptions

reminders

csat

sessions