Write SQL and pandas for shopping events

Use the schema and sample data below to answer SQL and pandas tasks. Treat 'today' as 2025-09-01.

Schema
users(user_id INT, country STRING)
pins(pin_id INT, creator_user_id INT, is_shopping_enabled BOOLEAN, created_at DATE)
events(event_id INT, user_id INT, pin_id INT, event_type STRING, event_ts TIMESTAMP, stay_time_sec INT, feature STRING, product_category_code STRING)
product_categories(code STRING, name STRING)

Sample tables (small)

users
user_id | country
1 | US
2 | US
3 | CA

pins
pin_id | creator_user_id | is_shopping_enabled | created_at
10 | 3 | true | 2025-08-20
11 | 2 | false | 2025-08-22
12 | 1 | true | 2025-08-25

events
event_id | user_id | pin_id | event_type | event_ts | stay_time_sec | feature | product_category_code
101 | 1 | 10 | shopping_click | 2025-08-30 10:00:00 | 35 | shopping_module | A
102 | 1 | 10 | shopping_click | 2025-09-01 09:00:00 | 50 | shopping_module | null
103 | 2 | 12 | shopping_click | 2025-08-31 12:00:00 | null | shopping_module | H
104 | 2 | 11 | view | 2025-09-01 08:00:00 | null | feed | null
105 | 3 | 10 | shopping_click | 2025-08-28 07:00:00 | 15 | shopping_module | A
106 | 3 | 10 | shopping_click | 2025-09-01 11:59:00 | -5 | shopping_module | ?

product_categories
code | name
A | Apparel
H | Home
? | Unknown

Tasks SQL

1. Daily shopping engagement last 7 days: Write a single SQL query that returns, for each date d in [2025-08-26, 2025-09-01], the columns (d, dau_shopping, clicks, avg_stay_time_pos_sec, rolling_7d_uniq_users). Count only events where event_type='shopping_click' and feature='shopping_module'. Deduplicate rapid repeat clicks per (user_id, pin_id) if they occur within 5 minutes (treat as one); implement dedup with window functions. Exclude stay_time_sec <= 0 or NULL from the average, but still count those clicks in 'clicks'. Ensure dates with no activity appear with zeros using a generated dates CTE.
2. Next-day retention: Among users with at least one shopping_click on 2025-08-31, compute the percentage that also have a shopping_click on 2025-09-01.
3. Top pins per user: For the 7 days ending 2025-09-01, return for each user their top 2 pins by number of deduplicated shopping_clicks; break ties by greater total positive stay_time_sec, then by smallest pin_id.

Python (pandas) Given a DataFrame events_df with the same columns as events: (a) Map product_category_code using dict = {'A':'Apparel','H':'Home','?':'Unknown'} so missing/unknown codes become 'Unknown'. (b) Replace negative stay_time_sec with NaN; fill remaining NaN stay_time_sec with 0 for aggregation but exclude zeros from averages where appropriate. (c) Sort events_df by ['user_id' asc, 'event_ts' desc, 'stay_time_sec' desc]. (d) Compute, for the last 7 days ending 2025-09-01, each user's top category by total positive stay_time_sec and return a Series user_id -> top_category (tie-breaker: alphabetical).

Daily Shopping Engagement with Deduplicated Clicks

Using the tables below, write a single SQL query that returns daily shopping engagement metrics for each date d in the range from 2025-08-26 to 2025-09-01 (inclusive). The output should have the columns (d, dau_shopping, clicks, avg_stay_time_pos_sec, rolling_7d_uniq_users) with the following definitions:

• Count only events where event_type = 'shopping_click' AND feature = 'shopping_module'.
• Deduplicate rapid repeat clicks per (user_id, pin_id) if they occur within 5 minutes of the previous click on the same (user_id, pin_id); treat such a cluster as a single click, and implement this deduplication using window functions. Use the timestamp of the first event in each cluster as the click time.
• dau_shopping: number of distinct users with at least one deduplicated shopping_click on that date d.
• clicks: number of deduplicated shopping_clicks on that date d.
• avg_stay_time_pos_sec: average stay_time_sec over deduplicated shopping_clicks on that date d where stay_time_sec > 0; exclude stay_time_sec that are NULL or <= 0 from the average, but still count those clicks in 'clicks'. If there are no positive stay times on a date, return 0 for this average.
• rolling_7d_uniq_users: for each date d, the number of distinct users who had at least one deduplicated shopping_click in the 7-day window [d - 6 days, d], inclusive. Ensure that every date in [2025-08-26, 2025-09-01] appears in the result, even if there is no activity, with zeros for dau_shopping, clicks, avg_stay_time_pos_sec, and rolling_7d_uniq_users. Use a generated date series (for example, via a CTE) to produce the full date range and implement the deduplication with window functions.

Table users

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
country	VARCHAR(2)	NOT NULL

Sample data:

country	user_id
US	1
US	2
CA	3

Table pins

column	type	constraints
pin_id	INT	PRIMARY KEY, NOT NULL
creator_user_id	INT	NOT NULL
is_shopping_enabled	BOOLEAN	NOT NULL
created_at	DATE	NOT NULL

Sample data:

created_at	creator_user_id	is_shopping_enabled	pin_id
2025-08-20	3	true	10
2025-08-22	2	false	11
2025-08-25	1	true	12

Table events

column	type	constraints
event_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
pin_id	INT	NOT NULL
event_type	VARCHAR(50)	NOT NULL
event_ts	TIMESTAMP	NOT NULL
stay_time_sec	INT
feature	VARCHAR(50)	NOT NULL
product_category_code	VARCHAR(10)

Sample data:

event_id	event_ts	event_type	feature	pin_id	product_category_code	stay_time_sec	user_id
101	2025-08-30 10:00:00	shopping_click	shopping_module	10	A	35	1
102	2025-09-01 09:00:00	shopping_click	shopping_module	10	NULL	50	1
103	2025-08-31 12:00:00	shopping_click	shopping_module	12	H	NULL	2
104	2025-09-01 08:00:00	view	feed	11	NULL	NULL	2
105	2025-08-28 07:00:00	shopping_click	shopping_module	10	A	15	3
106	2025-09-01 11:59:00	shopping_click	shopping_module	10	?	-5	3

Table product_categories

column	type	constraints
code	VARCHAR(10)	PRIMARY KEY, NOT NULL
name	VARCHAR(100)	NOT NULL

Sample data:

code	name
A	Apparel
H	Home
?	Unknown

Expected output

One row per calendar date d from 2025-08-26 to 2025-09-01 inclusive, with deduplicated shopping click metrics and a rolling 7-day distinct-user count. With the sample data, there are no rapid repeat clicks within 5 minutes, so deduplication does not drop any rows.

avg_stay_time_pos_sec	clicks	d	dau_shopping	rolling_7d_uniq_users
0	0	2025-08-26	0	0
0	0	2025-08-27	0	0
15	1	2025-08-28	1	1
0	0	2025-08-29	0	1
35	1	2025-08-30	1	2
0	1	2025-08-31	1	3
50	2	2025-09-01	2	3

Hints

1. Use a recursive CTE or date generator to build the full calendar from 2025-08-26 to 2025-09-01, then left join metrics onto it.
2. Deduplicate clicks with LAG over (user_id, pin_id) to detect gaps greater than 5 minutes, and use a correlated subquery over the deduplicated table to compute the 7-day rolling distinct-user count.

Next-Day Shopping Click Retention

Using the same tables, compute next-day retention for shopping clicks between 2025-08-31 and 2025-09-01. Consider only events where event_type = 'shopping_click' AND feature = 'shopping_module'.

Define the cohort as all distinct users who have at least one qualifying shopping_click on 2025-08-31. A user is retained if they also have at least one qualifying shopping_click on 2025-09-01. Write a SQL query that returns a single row with the columns (cohort_date, next_date, total_users, retained_users, retention_rate), where retention_rate = retained_users / total_users. If there are no users in the cohort, retention_rate should be 0.

Table users

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
country	VARCHAR(2)	NOT NULL

Sample data:

country	user_id
US	1
US	2
CA	3

Table pins

column	type	constraints
pin_id	INT	PRIMARY KEY, NOT NULL
creator_user_id	INT	NOT NULL
is_shopping_enabled	BOOLEAN	NOT NULL
created_at	DATE	NOT NULL

Sample data:

created_at	creator_user_id	is_shopping_enabled	pin_id
2025-08-20	3	true	10
2025-08-22	2	false	11
2025-08-25	1	true	12

Table events

column	type	constraints
event_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
pin_id	INT	NOT NULL
event_type	VARCHAR(50)	NOT NULL
event_ts	TIMESTAMP	NOT NULL
stay_time_sec	INT
feature	VARCHAR(50)	NOT NULL
product_category_code	VARCHAR(10)

Sample data:

event_id	event_ts	event_type	feature	pin_id	product_category_code	stay_time_sec	user_id
101	2025-08-30 10:00:00	shopping_click	shopping_module	10	A	35	1
102	2025-09-01 09:00:00	shopping_click	shopping_module	10	NULL	50	1
103	2025-08-31 12:00:00	shopping_click	shopping_module	12	H	NULL	2
104	2025-09-01 08:00:00	view	feed	11	NULL	NULL	2
105	2025-08-28 07:00:00	shopping_click	shopping_module	10	A	15	3
106	2025-09-01 11:59:00	shopping_click	shopping_module	10	?	-5	3

Table product_categories

column	type	constraints
code	VARCHAR(10)	PRIMARY KEY, NOT NULL
name	VARCHAR(100)	NOT NULL

Sample data:

code	name
A	Apparel
H	Home
?	Unknown

Expected output

A single row summarizing next-day retention from 2025-08-31 to 2025-09-01. In the sample data, only user 2 has a shopping_click on 2025-08-31, and they have no shopping_click on 2025-09-01, so retention is 0.

cohort_date	next_date	retained_users	retention_rate	total_users
2025-08-31	2025-09-01	0	0	1

Hints

1. Build a cohort of distinct users from 2025-08-31 and then check which of them also appear with a shopping_click on 2025-09-01.
2. You can compute retention_rate by dividing the retained user count by the cohort size, handling the divide-by-zero case explicitly.

Top 2 Shopping Pins per User Over 7 Days

For the 7-day period from 2025-08-26 through 2025-09-01 (inclusive), use the same tables to find, for each user, their top 2 pins ranked by engagement on shopping clicks. Consider only events where event_type = 'shopping_click' AND feature = 'shopping_module'.

Apply the same deduplication rule as in Question 1: within each (user_id, pin_id), rapid repeat clicks within 5 minutes are treated as a single click, implemented via window functions. Using the deduplicated clicks in this 7-day window:

• click_count: number of deduplicated shopping_clicks per (user_id, pin_id).
• total_pos_stay_time_sec: sum of stay_time_sec over deduplicated clicks with stay_time_sec > 0; treat NULL or non-positive stay_time_sec as contributing 0 to this sum.

For each user, rank their pins by:

1. higher click_count, 2) then higher total_pos_stay_time_sec, 3) then smaller pin_id. Return, for each user, only their top 2 pins according to this ordering. The output should include columns (user_id, pin_id, click_count, total_pos_stay_time_sec).

Table users

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
country	VARCHAR(2)	NOT NULL

Sample data:

country	user_id
US	1
US	2
CA	3

Table pins

column	type	constraints
pin_id	INT	PRIMARY KEY, NOT NULL
creator_user_id	INT	NOT NULL
is_shopping_enabled	BOOLEAN	NOT NULL
created_at	DATE	NOT NULL

Sample data:

created_at	creator_user_id	is_shopping_enabled	pin_id
2025-08-20	3	true	10
2025-08-22	2	false	11
2025-08-25	1	true	12

Table events

column	type	constraints
event_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
pin_id	INT	NOT NULL
event_type	VARCHAR(50)	NOT NULL
event_ts	TIMESTAMP	NOT NULL
stay_time_sec	INT
feature	VARCHAR(50)	NOT NULL
product_category_code	VARCHAR(10)

Sample data:

event_id	event_ts	event_type	feature	pin_id	product_category_code	stay_time_sec	user_id
101	2025-08-30 10:00:00	shopping_click	shopping_module	10	A	35	1
102	2025-09-01 09:00:00	shopping_click	shopping_module	10	NULL	50	1
103	2025-08-31 12:00:00	shopping_click	shopping_module	12	H	NULL	2
104	2025-09-01 08:00:00	view	feed	11	NULL	NULL	2
105	2025-08-28 07:00:00	shopping_click	shopping_module	10	A	15	3
106	2025-09-01 11:59:00	shopping_click	shopping_module	10	?	-5	3

Table product_categories

column	type	constraints
code	VARCHAR(10)	PRIMARY KEY, NOT NULL
name	VARCHAR(100)	NOT NULL

Sample data:

code	name
A	Apparel
H	Home
?	Unknown

Expected output

For each user, up to two rows containing their top pins in the 7-day window 2025-08-26 to 2025-09-01, ordered by click_count, then total positive stay time, then pin_id. With the sample data, each user has only one qualifying pin in this window.

click_count	pin_id	total_pos_stay_time_sec	user_id
2	10	85	1
1	12	0	2
2	10	15	3

Hints

1. Reuse the 5-minute deduplication logic from the first question to create a deduplicated clicks CTE for the 7-day window.
2. Aggregate per (user_id, pin_id), then apply ROW_NUMBER() partitioned by user_id and ordered by click_count, total positive stay time, and pin_id to pick the top 2 pins per user.