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 | user_id | signup_date | country | household_id | device | |--------:|-------------|---------|--------------|---------| | 1 | 2025-01-05 | US | H1 | ios | | 2 | 2025-01-07 | US | H1 | android | | 3 | 2025-01-12 | US | H2 | web | | 4 | 2025-01-20 | CN | H3 | ios | subscriptions | sub_id | user_id | drug_id | start_date | auto_refill | |-------:|--------:|---------|------------|-------------| | 10 | 1 | D1 | 2025-01-10 | 1 | | 11 | 2 | D1 | 2025-01-15 | 0 | | 12 | 3 | D2 | 2025-01-20 | 1 | reminders | user_id | reminder_dt | channel | |--------:|-------------|---------| | 1 | 2025-02-01 | push | | 1 | 2025-03-15 | email | | 2 | 2025-03-01 | push | | 3 | 2025-04-01 | sms | csat | user_id | event_dt | csat_score | |--------:|------------|------------| | 1 | 2025-01-25 | 3 | | 1 | 2025-02-08 | 5 | | 2 | 2025-02-20 | 4 | | 2 | 2025-03-10 | 2 | | 3 | 2025-03-15 | 4 | | 3 | 2025-04-10 | 3 | sessions | user_id | session_dt | minutes | purchase_flag | |--------:|------------|--------:|---------------| | 1 | 2025-01-28 | 6 | 0 | | 1 | 2025-02-06 | 12 | 1 | | 2 | 2025-02-22 | 9 | 0 | | 2 | 2025-03-05 | 3 | 0 | | 3 | 2025-03-18 | 7 | 1 | | 3 | 2025-04-09 | 5 | 0 |