Write windowed retention and ARPU SQL
Company: Pinterest
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
You are given three tables. Write one SQL script (CTEs allowed) that answers all parts using window functions and joins (no procedural loops):
Schema:
- users(user_id INT, signup_date DATE, channel VARCHAR)
- sessions(session_id INT, user_id INT, session_start TIMESTAMP, country VARCHAR)
- orders(order_id INT, user_id INT, order_ts TIMESTAMP, amount DECIMAL(10,2))
Sample data:
users
user_id | signup_date | channel
1 | 2025-08-01 | Ads
2 | 2025-08-03 | Organic
3 | 2025-08-05 | Ads
4 | 2025-08-28 | Referral
sessions
session_id | user_id | session_start | country
10 | 1 | 2025-08-02 10:00:00 | US
11 | 1 | 2025-08-08 09:12:00 | US
12 | 2 | 2025-08-04 12:30:00 | US
13 | 3 | 2025-08-06 17:45:00 | CA
14 | 4 | 2025-09-01 00:10:00 | US
orders
order_id | user_id | order_ts | amount
100 | 1 | 2025-08-09 15:00:00 | 20.00
101 | 3 | 2025-08-07 19:00:00 | 10.00
102 | 3 | 2025-09-01 01:00:00 | 5.00
Tasks:
A) For each calendar day D in 2025-08-01..2025-08-31, compute 7-day rolling retention: among users with signup_date <= D, the fraction who had ≥1 session in [D-6, D] (inclusive). Output columns: day, retained_users, eligible_users, retention_rate. Use window functions where appropriate; ensure correct date truncation from session_start.
B) For each user, output first_purchase_date, days_to_first_purchase (from signup_date), and total_amount_before_first_purchase_window (sum of orders strictly before first_purchase_date should be 0 by definition; prove it via ROW_NUMBER/QUALIFY or equivalent instead of MIN subqueries). Show user_id, first_purchase_date, days_to_first_purchase.
C) As of today (2025-09-01), compute top 3 acquisition channels by 7-day ARPU over [2025-08-26, 2025-09-01]: ARPU = total order amount in the window divided by number of active users (users with ≥1 session in the window) from that channel. Output: channel, active_users_7d, revenue_7d, arpu_7d; order by arpu_7d desc and limit 3.
D) Edge cases to handle correctly in your query: users with no sessions; multiple sessions same day; multiple orders on same day; users who signed up after the window; time zones (assume all timestamps are UTC and day boundaries are UTC). Explain briefly in comments where each is handled.
Deliverables: a single SQL script using CTEs and window functions (e.g., ROW_NUMBER, SUM OVER, COUNT DISTINCT via windowing or equivalent) that produces the specified outputs.
Overview: This question evaluates proficiency with SQL window functions, joins, aggregations and time-windowed analytics for computing retention and ARPU, as well as handling date truncation, UTC day boundaries and common data edge cases across user, session, and order tables.
Read the full Pinterest Data Scientist interview experience this question came from
7-day Rolling Retention by Calendar Day
Using the tables below, write a SQL query (CTEs and window functions allowed) that, for each calendar day D from 2025-08-01 to 2025-08-31, computes 7-day rolling retention. For each day D, consider all users with signup_date <= D (eligible_users). A user is retained on day D if they had at least one session whose UTC date (from session_start) falls within the inclusive window [D-6, D]. Output one row per day with columns: day, retained_users, eligible_users, retention_rate, where retention_rate = retained_users / eligible_users. Use proper date truncation from session_start (i.e., convert timestamps to DATE) and use window functions where appropriate. Ensure your logic correctly handles users with no sessions, multiple sessions on the same day, and users who sign up late in the month.
Tables
users(user_id INT, signup_date DATE, channel VARCHAR(50))
sessions(session_id INT, user_id INT, session_start TIMESTAMP, country VARCHAR(50))
orders(order_id INT, user_id INT, order_ts TIMESTAMP, amount DECIMAL(10,2))
Hints
- Generate the list of all calendar days first (e.g., with generate_series) and then join users and sessions to that date dimension.
- Compute per-(day,user) session counts over the 7-day window and then aggregate to get retained_users and eligible_users.
User First Purchase and Time-to-Purchase via Window Functions
Using the same tables, write a SQL query that, for each user, finds their first purchase date (first_purchase_date), the number of days from signup_date to that first purchase (days_to_first_purchase), and the total order amount strictly before the first purchase window (total_amount_before_first_purchase_window). Use window functions such as ROW_NUMBER and a cumulative SUM OVER to identify each user's first purchase instead of using MIN(order_ts) subqueries. For users with no orders, first_purchase_date and days_to_first_purchase should be NULL. Output columns: user_id, first_purchase_date, days_to_first_purchase, total_amount_before_first_purchase_window.
Tables
users(user_id INT, signup_date DATE, channel VARCHAR(50))
sessions(session_id INT, user_id INT, session_start TIMESTAMP, country VARCHAR(50))
orders(order_id INT, user_id INT, order_ts TIMESTAMP, amount DECIMAL(10,2))
Hints
- Use ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_ts) to identify the first order per user.
- A cumulative SUM(...) OVER with a window ending at the previous row lets you compute the amount strictly before the first purchase.
Channel-level 7-day ARPU with Active Users
As of 2025-09-01, compute the top 3 acquisition channels by 7-day ARPU over the window [2025-08-26, 2025-09-01] (inclusive). ARPU is defined as total order amount in that window divided by the number of active users from that channel in that window, where an active user is any user with at least one session whose UTC date (from session_start) falls in [2025-08-26, 2025-09-01]. Use the users, sessions, and orders tables below, joining sessions and orders to users to get channels. Output columns: channel, active_users_7d, revenue_7d, arpu_7d. Order the result by arpu_7d in descending order and return only the top 3 channels. Ensure that channels with zero active users are not included (to avoid division by zero), and handle users with orders but no sessions, or sessions but no orders, correctly.
Tables
users(user_id INT, signup_date DATE, channel VARCHAR(50))
sessions(session_id INT, user_id INT, session_start TIMESTAMP, country VARCHAR(50))
orders(order_id INT, user_id INT, order_ts TIMESTAMP, amount DECIMAL(10,2))
Hints
- First compute active users per channel from sessions, then revenue per channel from orders, and finally join those aggregates.
- Make sure to filter the window by casting timestamps to DATE and to exclude channels where the active user count is zero before dividing.