Build DiD dataset with SQL

Using the schema and sample data below, write SQL to build an individual-day panel suitable for staggered-adoption DiD of the shuttle’s effect on participation. Requirements: A) output columns: employee_id, site_id, date, participated, adoption_date (per site), treated_site (1 if date >= adoption_date at that site, else 0; never-treated have NULL adoption_date and 0), event_time_days = date - adoption_date (NULL for never-treated), and a binary post indicator; B) ensure no off-by-one errors on the adoption boundary; C) also produce a weekly site-level table with participation_rate = avg(participated) per site-week, correctly handling sites without shuttle; D) assume employees do not move sites. Provide SQL that works on a modern warehouse (e.g., BigQuery or PostgreSQL). Schema:

sites(site_id, city)
employees(employee_id, site_id, hire_date)
shuttle_service(site_id, start_date) -- present only for treated sites
participation(employee_id, date, participated)

Sample tables:

sites
+---------+------+
| site_id | city |
+---------+------+
| 1 | SEA |
| 2 | NYC |
+---------+------+

employees
+-------------+---------+------------+
| employee_id | site_id | hire_date |
+-------------+---------+------------+
| 101 | 1 | 2024-10-01 |
| 102 | 1 | 2025-01-01 |
| 201 | 2 | 2024-11-15 |
+-------------+---------+------------+

shuttle_service
+---------+------------+
| site_id | start_date |
+---------+------------+
| 1 | 2025-01-15 |
+---------+------------+

participation
+-------------+------------+--------------+
| employee_id | date | participated |
+-------------+------------+--------------+
| 101 | 2025-01-10 | 1 |
| 101 | 2025-01-20 | 1 |
| 102 | 2025-01-20 | 0 |
| 201 | 2025-01-10 | 1 |
| 201 | 2025-01-20 | 0 |
+-------------+------------+--------------+

Build an individual-day DiD panel with staggered site adoption

Using the tables below, write SQL to build an individual-day panel suitable for a staggered-adoption Difference-in-Differences analysis of a shuttle service’s effect on participation.

Output requirements (one row per employee per date):

1. Columns: employee_id, site_id, date, participated, adoption_date (per site), treated_site (1 if date >= adoption_date for that site, else 0; never-treated sites must have NULL adoption_date and treated_site = 0), event_time_days = date - adoption_date (NULL for never-treated), and post (binary indicator equal to 1 if date >= adoption_date for treated sites, else 0).
2. Avoid off-by-one errors: the adoption boundary is inclusive (i.e., date = adoption_date is treated/post).
3. Assume employees never change sites.

Panel construction rules:

• Use the distinct dates present in the participation table as the date spine.
• Include all employees for all spine dates on/after their hire_date.
• If an employee has no participation row for a given spine date, set participated = 0.

Table sites

column	type	constraints
site_id	INT	PRIMARY KEY, NOT NULL
city	VARCHAR(10)	NOT NULL

Sample data:

city	site_id
SEA	1
NYC	2

Table employees

column	type	constraints
employee_id	INT	PRIMARY KEY, NOT NULL
site_id	INT	NOT NULL
hire_date	DATE	NOT NULL

Sample data:

employee_id	hire_date	site_id
101	2024-10-01	1
102	2025-01-01	1
201	2024-11-15	2

Table shuttle_service

column	type	constraints
site_id	INT	PRIMARY KEY, NOT NULL
start_date	DATE	NOT NULL

Sample data:

site_id	start_date
1	2025-01-15

Table participation

column	type	constraints
employee_id	INT	NOT NULL
date	DATE	NOT NULL
participated	INT	NOT NULL

Sample data:

date	employee_id	participated
2025-01-10	101	1
2025-01-20	101	1
2025-01-20	102	0
2025-01-10	201	1
2025-01-20	201	0

Expected output

A 1-row-per-employee-per-spine-date panel (spine dates are distinct dates in participation) on/after hire_date, with missing participation treated as 0, and with treatment/adoption/event-time variables derived from shuttle_service at the site level.

adoption_date	date	employee_id	event_time_days	participated	post	site_id	treated_site
2025-01-15	2025-01-10	101	-5	1	0	1	0
2025-01-15	2025-01-20	101	5	1	1	1	1
2025-01-15	2025-01-10	102	-5	0	0	1	0
2025-01-15	2025-01-20	102	5	0	1	1	1
NULL	2025-01-10	201	NULL	1	0	2	0
NULL	2025-01-20	201	NULL	0	0	2	0

Hints

1. Create a date spine (e.g., distinct dates from participation) then CROSS JOIN employees to it to form the panel.
2. LEFT JOIN shuttle_service at the site level to get adoption_date; use date >= adoption_date (inclusive) to avoid off-by-one errors.

Aggregate to a weekly site-level participation rate (including never-treated sites)

Using the same schema, write SQL to produce a weekly site-level table with:

• site_id
• week_start_date (week bucket start; you may use DATE_TRUNC('week', date) in PostgreSQL)
• participation_rate = AVG(participated)

Requirements:

1. Correctly handle sites without shuttle service (they must still appear for weeks where they have employee-day rows in the panel).
2. Use the same paneling rule as in the individual-day dataset: for each site, include all employee-day rows formed by (employees at the site) x (distinct participation dates) on/after hire_date, with missing participation treated as 0.

Return one row per site-week.

Table sites

column	type	constraints
site_id	INT	PRIMARY KEY, NOT NULL
city	VARCHAR(10)	NOT NULL

Sample data:

city	site_id
SEA	1
NYC	2

Table employees

column	type	constraints
employee_id	INT	PRIMARY KEY, NOT NULL
site_id	INT	NOT NULL
hire_date	DATE	NOT NULL

Sample data:

employee_id	hire_date	site_id
101	2024-10-01	1
102	2025-01-01	1
201	2024-11-15	2

Table shuttle_service

column	type	constraints
site_id	INT	PRIMARY KEY, NOT NULL
start_date	DATE	NOT NULL

Sample data:

site_id	start_date
1	2025-01-15

Table participation

column	type	constraints
employee_id	INT	NOT NULL
date	DATE	NOT NULL
participated	INT	NOT NULL

Sample data:

date	employee_id	participated
2025-01-10	101	1
2025-01-20	101	1
2025-01-20	102	0
2025-01-10	201	1
2025-01-20	201	0

Expected output

One row per site-week (week_start_date derived from date), with participation_rate computed as the average participated across all employee-day rows in that site-week (including 0s for missing participation on spine dates).

participation_rate	site_id	week_start_date
0.5	1	2025-01-06
0.5	1	2025-01-20
1	2	2025-01-06
0	2	2025-01-20

Hints

1. Build (or reuse) the employee-day panel first, then aggregate by site_id and DATE_TRUNC('week', date).
2. To ensure never-treated sites are included, avoid inner joins to shuttle_service when creating the weekly table.