Write SQL and pandas for shopping events
Company: Pinterest
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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).
Overview: This question evaluates a candidate's ability to perform data manipulation in SQL and pandas, covering event deduplication, time-window aggregations, next-day retention calculations, top-N ranking, joins, and data cleaning/mapping for analytics.
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.
Tables
users(user_id INT, country VARCHAR(2))
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 VARCHAR(50), event_ts TIMESTAMP, stay_time_sec INT, feature VARCHAR(50), product_category_code VARCHAR(10))
product_categories(code VARCHAR(10), name VARCHAR(100))
Hints
- 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.
- 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.
Tables
users(user_id INT, country VARCHAR(2))
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 VARCHAR(50), event_ts TIMESTAMP, stay_time_sec INT, feature VARCHAR(50), product_category_code VARCHAR(10))
product_categories(code VARCHAR(10), name VARCHAR(100))
Hints
- 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.
- 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).
Tables
users(user_id INT, country VARCHAR(2))
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 VARCHAR(50), event_ts TIMESTAMP, stay_time_sec INT, feature VARCHAR(50), product_category_code VARCHAR(10))
product_categories(code VARCHAR(10), name VARCHAR(100))
Hints
- Reuse the 5-minute deduplication logic from the first question to create a deduplicated clicks CTE for the 7-day window.
- 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.