Calculate January-2024 SF Promotion Impact Using SQL Queries

campaign_users

+---------+-----------+
| user_id | treatment |
+---------+-----------+
| 1001    | control   |
| 1002    | test      |
| 1003    | control   |
| 1004    | test      |
| 1005    | control   |
+---------+-----------+

sessions

+---------+-----------+--------+---------------------+
| user_id | session_id| region | timestamp           |
+---------+-----------+--------+---------------------+
| 1001    | s1        | SF     | 2024-01-05 10:00:00 |
| 1002    | s2        | NY     | 2024-01-06 11:00:00 |
| 1002    | s3        | SF     | 2024-01-10 12:00:00 |
| 1004    | s4        | SF     | 2024-01-12 09:30:00 |
| 1005    | s5        | SF     | 2024-02-01 08:20:00 |
+---------+-----------+--------+---------------------+

orders

+---------+-----------+----------+---------+--------+
| user_id | session_id| order_id | revenue | profit |
+---------+-----------+----------+---------+--------+
| 1001    | s1        | o101     | 50.00   | 10.00  |
| 1002    | s2        | o102     | 80.00   | 15.00  |
| 1002    | s3        | o103     | 45.00   | 12.00  |
| 1004    | s4        | o104     | 60.00   | 20.00  |
| 1004    | s4        | o105     | 20.00   | 5.00   |
+---------+-----------+----------+---------+--------+

Scenario

Using three relational tables that record campaign assignment, user sessions, and orders, evaluate the January-2024 SF-only promotion experiment and perform follow-up analyses.

Question

Write a SQL query that joins the three tables to compute average revenue per user for treatment vs. control, restricting data to sessions in January-2024 and region = 'SF'. For each treatment arm, return the three user_id values with the highest number of orders. For each treatment arm, calculate the percentage of users who have placed more than five orders.

Hints

Use proper JOINs, WHERE filters on date and region, GROUP BY treatment, COUNT orders, window functions or conditional aggregation as needed.

Campaign experiment: SF January-2024 revenue and ordering behavior

You are analyzing the impact of a January-2024 SF-only promotion experiment. Using the three tables below (campaign assignment, user sessions, and orders), write a SQL query that:

1. Restricts data to sessions in region = 'SF' with timestamps between '2024-01-01' (inclusive) and '2024-02-01' (exclusive).
2. Joins the three tables to compute the average revenue per user for each treatment arm (control vs test), based only on qualifying sessions.
3. For each treatment arm, returns the three user_id values with the highest number of orders (within the filtered SF January sessions).
4. For each treatment arm, calculates the percentage of users who have placed more than five orders (again, considering only orders from qualifying sessions). Return one row per treatment arm including: treatment, average revenue per user, the top three user_ids by order count, and the percentage of users with more than five orders.

Table campaign_users

column	type	constraints
user_id	INTEGER	PRIMARY KEY, NOT NULL
treatment	VARCHAR(10)	NOT NULL

Sample data:

treatment	user_id
control	1001
test	1002
control	1003
test	1004
control	1005

Table sessions

column	type	constraints
user_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES campaign_users(user_id)
session_id	VARCHAR(20)	PRIMARY KEY, NOT NULL
region	VARCHAR(10)	NOT NULL
timestamp	TIMESTAMP	NOT NULL

Sample data:

region	session_id	timestamp	user_id
SF	s1	2024-01-05 10:00:00	1001
NY	s2	2024-01-06 11:00:00	1002
SF	s3	2024-01-10 12:00:00	1002
SF	s4	2024-01-12 09:30:00	1004
SF	s5	2024-02-01 08:20:00	1005

Table orders

column	type	constraints
user_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES campaign_users(user_id)
session_id	VARCHAR(20)	NOT NULL, FOREIGN KEY REFERENCES sessions(session_id)
order_id	VARCHAR(20)	PRIMARY KEY, NOT NULL
revenue	DECIMAL(10,2)	NOT NULL
profit	DECIMAL(10,2)	NOT NULL

Sample data:

order_id	profit	revenue	session_id	user_id
o101	10	50	s1	1001
o102	15	80	s2	1002
o103	12	45	s3	1002
o104	20	60	s4	1004
o105	5	20	s4	1004

Expected output

One row per treatment arm, restricted to sessions with region = 'SF' and timestamp between '2024-01-01' (inclusive) and '2024-02-01' (exclusive). For each arm: (1) average revenue per user based on qualifying sessions, (2) an array of up to the top three user_ids ordered by descending number of orders (and by revenue and user_id as tie-breakers if needed), and (3) the percentage of users in that arm who have more than five orders from qualifying sessions.

avg_revenue_per_user	pct_users_gt_5_orders	top_user_ids_by_orders	treatment
50	0	1001	control
62.5	0	1004,1002	test

Hints

1. Filter sessions to region = 'SF' and timestamp >= '2024-01-01' and < '2024-02-01'.
2. Join campaign_users to the filtered sessions, then LEFT JOIN orders to keep users with zero orders on qualifying sessions.
3. Aggregate to a user-level table (order_count, user_revenue) before computing treatment-level averages.
4. Use window functions to rank users by order_count within each treatment arm and aggregate the top three (e.g., ARRAY_AGG or STRING_AGG).
5. Compute the percentage of heavy-order users with conditional aggregation, e.g. AVG(CASE WHEN order_count > 5 THEN 1.0 ELSE 0.0 END) * 100.