Write SQL for snapshot features and labels

Use 'today' = 2025-09-01. Invented schema (UTC):

users(u_id INT, signup_dt DATE, country STRING)
events(u_id INT, event_time TIMESTAMP, arrival_time TIMESTAMP, event_type STRING, session_id STRING)
orders(order_id INT, u_id INT, order_time TIMESTAMP, amount DECIMAL(10,2))

Sample rows:

users
u_id | signup_dt | country
1 | 2025-07-20 | US
2 | 2025-08-25 | CA
3 | 2025-08-30 | US

events
u_id | event_time | arrival_time | event_type | session_id
1 | 2025-08-29 10:00:00 | 2025-08-29 10:00:02 | view | a
1 | 2025-08-31 09:00:00 | 2025-08-31 09:00:03 | add_to_cart | a
1 | 2025-09-01 12:00:00 | 2025-09-01 12:00:01 | view | b
2 | 2025-08-30 15:00:00 | 2025-08-30 15:00:02 | view | c
2 | 2025-09-01 09:30:00 | 2025-09-01 09:30:01 | view | d
3 | 2025-08-31 23:59:00 | 2025-09-02 00:00:05 | view | e (late-arriving)
3 | 2025-09-01 08:00:00 | 2025-09-01 08:00:00 | view | e
3 | 2025-09-01 08:00:00 | 2025-09-01 08:00:00 | view | e (duplicate)

orders
order_id | u_id | order_time | amount
101 | 1 | 2025-09-10 13:00:00 | 120.00
102 | 2 | 2025-08-31 16:00:00 | 50.00
103 | 2 | 2025-09-03 11:00:00 | 60.00

Task: Write one SQL query that produces a training dataset at anchor snapshot_ts = '2025-09-01 00:00:00' with columns: (u_id, country, days_since_signup, last_event_time, events_7d, sessions_7d, added_to_cart_7d, y_30d). Rules: (A) Features use only events with event_time < snapshot_ts AND arrival_time <= snapshot_ts; drop duplicates by (u_id, event_time, event_type, session_id). (B) Compute 7-day windows over [snapshot_ts - 7 days, snapshot_ts). (C) sessions_7d counts distinct session_id in window; events_7d counts distinct (event_time, event_type, session_id). (D) y_30d = 1 if there exists an order in [snapshot_ts, snapshot_ts + INTERVAL '30' DAY), else 0. (E) Include users with no events (fill nulls/zeros appropriately). Show the exact row outputs for the provided sample data and explain why user 3's late-arriving 2025-08-31 event must be excluded from features.

Build snapshot features and 30-day label at an anchor timestamp

You are building a point-in-time training table for a churn/purchase model. All timestamps are in UTC. You are given three tables:

• users(u_id INT, signup_dt DATE, country VARCHAR)
• events(u_id INT, event_time TIMESTAMP, arrival_time TIMESTAMP, event_type VARCHAR, session_id VARCHAR) — event_time is when the action happened; arrival_time is when the row became available in the warehouse (it can be later than event_time due to ingestion latency).
• orders(order_id INT, u_id INT, order_time TIMESTAMP, amount DECIMAL(10,2))

Use a fixed anchor snapshot timestamp snapshot_ts = '2025-09-01 00:00:00'. Write a single PostgreSQL query (CTEs allowed) that returns one row per user in users with exactly these columns, in this order:

• u_id
• country
• days_since_signup — integer number of days from signup_dt to DATE '2025-09-01' (i.e. DATE '2025-09-01' - signup_dt).
• last_event_time — the latest event_time among the user's qualifying feature events (see rules A); NULL if the user has none.
• events_7d — count of the user's deduplicated feature events that fall in the 7-day feature window.
• sessions_7d — count of distinct session_id values among those 7-day-window feature events.
• added_to_cart_7d — count of 7-day-window feature events with event_type = 'add_to_cart'.
• y_30d — 1 if the user has at least one order in the 30-day label window, else 0.

Rules:

A) Qualifying feature events. Only consider event rows with event_time < snapshot_ts AND arrival_time <= snapshot_ts (an event that physically occurred before the snapshot but arrived afterward is not yet known at snapshot time and must be excluded). Deduplicate these rows on (u_id, event_time, event_type, session_id) before counting. last_event_time is MAX(event_time) over these qualifying (deduplicated) events — it is not restricted to the 7-day window.

B) 7-day feature window. events_7d, sessions_7d, and added_to_cart_7d are computed over qualifying feature events whose event_time is in ['2025-08-25 00:00:00', '2025-09-01 00:00:00') — i.e. >= snapshot_ts - INTERVAL '7 days' and < snapshot_ts.

C) 30-day label window. y_30d = 1 if the user has at least one order with order_time in ['2025-09-01 00:00:00', '2025-10-01 00:00:00') — i.e. >= snapshot_ts and < snapshot_ts + INTERVAL '30 days'; otherwise 0. (Orders strictly before the snapshot do not count as labels.)

D) Completeness. Include every user from users, even with no events or orders. For users with no qualifying events, last_event_time is NULL and the three *_7d counts are 0. For users with no qualifying orders, y_30d = 0.

Return the rows ordered by u_id ascending.

With the sample data, note in particular that user 3's event with event_time = '2025-08-31 23:59:00' and arrival_time = '2025-09-02 00:00:05' is excluded (it arrived after the snapshot), and user 3's two '2025-09-01 08:00:00' rows are excluded (they occur at/after the snapshot and are duplicates of each other) — so user 3 ends up with no qualifying feature events.

Table users

column	type	constraints
u_id	INT	PRIMARY KEY
signup_dt	DATE	NOT NULL
country	VARCHAR(2)	NOT NULL

Sample data:

country	signup_dt	u_id
US	2025-07-20	1
CA	2025-08-25	2
US	2025-08-30	3

Table events

column	type	constraints
u_id	INT	NOT NULL
event_time	TIMESTAMP	NOT NULL
arrival_time	TIMESTAMP	NOT NULL
event_type	VARCHAR(50)	NOT NULL
session_id	VARCHAR(50)	NOT NULL

Sample data:

arrival_time	event_time	event_type	session_id	u_id
2025-08-29 10:00:02	2025-08-29 10:00:00	view	a	1
2025-08-31 09:00:03	2025-08-31 09:00:00	add_to_cart	a	1
2025-09-01 12:00:01	2025-09-01 12:00:00	view	b	1
2025-08-30 15:00:02	2025-08-30 15:00:00	view	c	2
2025-09-01 09:30:01	2025-09-01 09:30:00	view	d	2
2025-09-02 00:00:05	2025-08-31 23:59:00	view	e	3
2025-09-01 08:00:00	2025-09-01 08:00:00	view	e	3
2025-09-01 08:00:00	2025-09-01 08:00:00	view	e	3

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY
u_id	INT	NOT NULL
order_time	TIMESTAMP	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	order_id	order_time	u_id
120	101	2025-09-10 13:00:00	1
50	102	2025-08-31 16:00:00	2
60	103	2025-09-03 11:00:00	2

Expected output

One row per user at snapshot_ts = '2025-09-01 00:00:00', ordered by u_id: signup-based feature (days_since_signup), latency-respecting last_event_time, 7-day deduplicated event features, and a 30-day order label. User 3 has no qualifying feature events (late arrival + at/after-snapshot duplicates), so its feature columns are 0/NULL and y_30d=0.

added_to_cart_7d	country	days_since_signup	events_7d	last_event_time	sessions_7d	u_id	y_30d
1	US	43	2	2025-08-31T09:00:00	1	1	1
0	CA	7	1	2025-08-30T15:00:00	1	2	1
0	US	2	0	NULL	0	3	0

Hints

1. Apply the point-in-time filter first: keep only events with event_time < snapshot_ts AND arrival_time <= snapshot_ts, and SELECT DISTINCT to drop duplicate ingests — that single CTE explains why user 3 has no features.
2. last_event_time is MAX(event_time) over ALL qualifying events, but events_7d / sessions_7d / added_to_cart_7d are over the narrower 7-day window — keep them in separate CTEs.
3. Drive the final SELECT off the users table with LEFT JOINs so every user appears, COALESCE the aggregates to 0, and compute days as DATE '2025-09-01' - signup_dt (Postgres date subtraction returns an integer).