Pinterest·Data Scientist·Technical Phone Screen
Jun 2026
Pinterest data scientist interview with a brutal single SQL problem covering four sub-tasks at once. Window functions, rolling retention, ARPU by channel, edge cases, all in one script. Left feeling like I'd either nailed it or completely missed something obvious.
- Given three tables (users, sessions, orders), write a single SQL script using CTEs and window functions that computes 7-day rolling retention for each calendar day in August 2025: among users who signed up on or before day D, what fraction had at least one session in the 7-day window ending on D?
- For each user, compute their first purchase date, the number of days between signup and first purchase, and prove via ROW_NUMBER or an equivalent window approach (not MIN subqueries) that the sum of orders strictly before the first purchase is zero.
- Compute the top 3 acquisition channels by 7-day ARPU over the window August 26 to September 1, 2025, where ARPU is total order revenue divided by number of users from that channel who had at least one session in that window.
- Handle edge cases within the same SQL script: users with no sessions, multiple sessions on the same day, multiple orders on the same day, users who signed up after the analysis window, and UTC timezone assumptions for all timestamps. Where in the query does each case get handled?
“This is the part that tripped me up most.” The rest of the author's notes on Data Scientist interview at Pinterest, Technical Phone Screen round, covers how they worked through the question, what the panel pushed back on, and what they would do differently.
View Post