Manipulate data in R with dplyr joins and windows

Q: Manipulate data in R with dplyr joins and windows

This is a Data Manipulation (SQL/Python) interview question from Upstart for Data Scientist roles. View the full question and solution on PracHub.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

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.

Manipulate data in R with dplyr joins and windows

Comments (0)