Manipulate data in R with dplyr joins and windows
Company: Upstart
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Using R and dplyr, answer the following using these small tables (dates are ISO strings):
transactions(user_id, order_id, order_date, channel, amount)
u1 | o1 | 2025-08-28 | web | 120
u1 | o2 | 2025-09-03 | web | 80
u2 | o3 | 2025-09-01 | store | 50
u3 | o4 | 2025-09-01 | web | 60
u3 | o5 | 2025-09-10 | store | 40
refunds(order_id, refund_date, amount)
o2 | 2025-09-05 | 80
o5 | 2025-09-12 | 20
users(user_id, signup_date)
u1 | 2025-08-20
u2 | 2025-08-30
u3 | 2025-09-01
Tasks: 1) Compute net revenue per day and channel between 2025-09-01 and 2025-09-10 inclusive, where net revenue = purchases minus refunds that occur on or before 2025-09-10. 2) For each user, identify first_purchase_date and a flag fully_refunded_first_purchase (1 if the first order was fully refunded by 2025-09-10). 3) Compute a 3-day rolling sum of net revenue by channel over that window (aligned to the right). 4) Produce a tidy data frame with columns (date, channel, net_revenue, rolling3_net_revenue) and another with (user_id, first_purchase_date, fully_refunded_first_purchase). Write idiomatic dplyr code (no loops), handling joins, windowing, and edge cases correctly.
Overview: This question evaluates proficiency in R data manipulation with dplyr, specifically joins, window functions, date filtering, aggregation, and handling edge cases like refunds and first-purchase identification.
Daily Net Revenue and 3-Day Rolling Sum by Channel
You are given three tables: transactions, refunds, and users. Using standard SQL, compute net revenue per calendar day and channel between 2025-09-01 and 2025-09-10 inclusive, where net revenue is defined as purchase amounts minus refund amounts that occur on or before 2025-09-10. Assume that refunds reduce revenue on the refund_date, and use the channel of the original order for each refund.
Requirements:
1) For each channel that appears in the transactions table, generate a row for every calendar date from 2025-09-01 through 2025-09-10, even if there are no purchases or refunds on that day for that channel. On such days, net_revenue should be 0.
2) Compute a 3-day rolling sum of net revenue by channel, aligned to the right: for each date D and channel C, rolling3_net_revenue is the sum of net_revenue for channel C over dates from max(2025-09-01, D-2 days) through D.
3) Return a result with columns (date, channel, net_revenue, rolling3_net_revenue).
Write a single SQL query that produces this result.
Tables
transactions(user_id VARCHAR(10), order_id VARCHAR(10), order_date DATE, channel VARCHAR(20), amount DECIMAL(10,2))
refunds(order_id VARCHAR(10), refund_date DATE, amount DECIMAL(10,2))
users(user_id VARCHAR(10), signup_date DATE)
Hints
- Aggregate purchases and refunds by date and channel separately, then join them to compute daily net revenue.
- Generate the full date range with a recursive CTE (or date series) and use a window function with ROWS BETWEEN 2 PRECEDING AND CURRENT ROW for the 3-day rolling sum.
First Purchase Date and Fully Refunded Flag per User
Using the same transactions, refunds, and users tables, write a SQL query that, for each user, identifies:
1) first_purchase_date: the date of their earliest order (by order_date).
2) fully_refunded_first_purchase: a flag equal to 1 if that first order was fully refunded by 2025-09-10 (i.e., the sum of refund amounts for that order with refund_date <= 2025-09-10 is greater than or equal to the original order amount), otherwise 0.
Include all users from the users table, even if they have no purchases (in which case first_purchase_date may be NULL and the flag should be 0). Return columns (user_id, first_purchase_date, fully_refunded_first_purchase).
Tables
transactions(user_id VARCHAR(10), order_id VARCHAR(10), order_date DATE, channel VARCHAR(20), amount DECIMAL(10,2))
refunds(order_id VARCHAR(10), refund_date DATE, amount DECIMAL(10,2))
users(user_id VARCHAR(10), signup_date DATE)
Hints
- Use a window function like ROW_NUMBER() partitioned by user to identify each user's first order.
- Aggregate refunds by order_id with a date filter (refund_date <= 2025-09-10) and compare the summed refund amount to the first order amount.