Pinterest·Data Scientist·Technical Phone Screen
- Write a single SQL query returning daily shopping engagement metrics for each date in a 7-day window, including DAU, click counts, average positive stay time, and a rolling 7-day unique user count. Deduplicate clicks within 5 minutes per user-pin pair using window functions, and use a generated dates CTE to fill in days with zero activity.
- Compute next-day retention: among users who had at least one shopping click on 2025-08-31, what percentage also had a shopping click on 2025-09-01?
- For the 7-day window ending 2025-09-01, return each user's top 2 pins by deduplicated shopping click count, with ties broken by total positive stay time and then by smallest pin ID.
- In pandas, map product category codes to names using a provided dictionary so that any missing or unrecognized codes fall back to 'Unknown', replace negative stay_time_sec values with NaN, fill remaining NaN stay times with 0 for aggregation while still excluding zeros from averages, sort the DataFrame by user ID ascending then event timestamp and stay time descending, and finally compute each user's top product category by total positive stay time over the last 7 days with alphabetical tie-breaking.
“This one took me a while to structure.”