Coding questions · Upstart
Manipulate data in R with dplyr joins and windows
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.
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.
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:
- 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.
- 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.
- Return a result with columns (date, channel, net_revenue, rolling3_net_revenue).
Write a single SQL query that produces this result.
Table transactions
| column | type | constraints |
|---|---|---|
| user_id | VARCHAR(10) | NOT NULL |
| order_id | VARCHAR(10) | PRIMARY KEY, NOT NULL |
| order_date | DATE | NOT NULL |
| channel | VARCHAR(20) | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
Sample data:
| amount | channel | order_date | order_id | user_id |
|---|---|---|---|---|
| 120 | web | 2025-08-28 | o1 | u1 |
| 80 | web | 2025-09-03 | o2 | u1 |
| 50 | store | 2025-09-01 | o3 | u2 |
| 60 | web | 2025-09-01 | o4 | u3 |
| 40 | store | 2025-09-10 | o5 | u3 |
Table refunds
| column | type | constraints |
|---|---|---|
| order_id | VARCHAR(10) | NOT NULL |
| refund_date | DATE | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
Sample data:
| amount | order_id | refund_date |
|---|---|---|
| 80 | o2 | 2025-09-05 |
| 20 | o5 | 2025-09-12 |
Table users
| column | type | constraints |
|---|---|---|
| user_id | VARCHAR(10) | PRIMARY KEY, NOT NULL |
| signup_date | DATE | NOT NULL |
Sample data:
| signup_date | user_id |
|---|---|
| 2025-08-20 | u1 |
| 2025-08-30 | u2 |
| 2025-09-01 | u3 |
Expected output
For each calendar date from 2025-09-01 to 2025-09-10 and for each channel present in the transactions table, the result contains the net revenue (purchases minus refunds on that date) and the 3-day rolling sum of net revenue for that channel up to that date. Days without activity for a given channel have net_revenue = 0 but still appear, and rolling3_net_revenue is computed over those zeros.
| channel | date | net_revenue | rolling3_net_revenue |
|---|---|---|---|
| store | 2025-09-01 | 50 | 50 |
| web | 2025-09-01 | 60 | 60 |
| store | 2025-09-02 | 0 | 50 |
| web | 2025-09-02 | 0 | 60 |
| store | 2025-09-03 | 0 | 50 |
| web | 2025-09-03 | 80 | 140 |
| store | 2025-09-04 | 0 | 0 |
| web | 2025-09-04 | 0 | 80 |
| store | 2025-09-05 | 0 | 0 |
| web | 2025-09-05 | -80 | 0 |
| store | 2025-09-06 | 0 | 0 |
| web | 2025-09-06 | 0 | -80 |
| store | 2025-09-07 | 0 | 0 |
| web | 2025-09-07 | 0 | -80 |
| store | 2025-09-08 | 0 | 0 |
| web | 2025-09-08 | 0 | 0 |
| store | 2025-09-09 | 0 | 0 |
| web | 2025-09-09 | 0 | 0 |
| store | 2025-09-10 | 40 | 40 |
| web | 2025-09-10 | 0 | 0 |
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:
- first_purchase_date: the date of their earliest order (by order_date).
- 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).
Table transactions
| column | type | constraints |
|---|---|---|
| user_id | VARCHAR(10) | NOT NULL |
| order_id | VARCHAR(10) | PRIMARY KEY, NOT NULL |
| order_date | DATE | NOT NULL |
| channel | VARCHAR(20) | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
Sample data:
| amount | channel | order_date | order_id | user_id |
|---|---|---|---|---|
| 120 | web | 2025-08-28 | o1 | u1 |
| 80 | web | 2025-09-03 | o2 | u1 |
| 50 | store | 2025-09-01 | o3 | u2 |
| 60 | web | 2025-09-01 | o4 | u3 |
| 40 | store | 2025-09-10 | o5 | u3 |
Table refunds
| column | type | constraints |
|---|---|---|
| order_id | VARCHAR(10) | NOT NULL |
| refund_date | DATE | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
Sample data:
| amount | order_id | refund_date |
|---|---|---|
| 80 | o2 | 2025-09-05 |
| 20 | o5 | 2025-09-12 |
Table users
| column | type | constraints |
|---|---|---|
| user_id | VARCHAR(10) | PRIMARY KEY, NOT NULL |
| signup_date | DATE | NOT NULL |
Sample data:
| signup_date | user_id |
|---|---|
| 2025-08-20 | u1 |
| 2025-08-30 | u2 |
| 2025-09-01 | u3 |
Expected output
One row per user, showing the date of their first purchase and a flag indicating whether that first purchase was fully refunded by 2025-09-10. In the sample data, no user's first purchase is fully refunded by that date, so all flags are 0.
| first_purchase_date | fully_refunded_first_purchase | user_id |
|---|---|---|
| 2025-08-28 | 0 | u1 |
| 2025-09-01 | 0 | u2 |
| 2025-09-01 | 0 | u3 |
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.