Upstart·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

Jul 2026

Summary

Upstart data scientist interview with a pretty involved R/dplyr coding problem. One question but it had multiple parts stacked on top of each other, so it felt like a full technical screen compressed into a single prompt.

Questions Asked (1)

Using R and dplyr with provided transactions, refunds, and users tables: (1) compute net revenue per day and channel over a specified date window, where net revenue accounts for refunds occurring within that window; (2) for each user, find their first purchase date and flag whether that first order was fully refunded by the window end; (3) compute a 3-day rolling sum of net revenue by channel; (4) return two tidy data frames with specific column structures. No loops, idiomatic dplyr only.

Product Analytics & MetricsData ModelingAlgorithms & Data Structures

Author's notes

This one took me a while to even parse.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the date window and the definition of net revenue (gross minus refunds). Then, build the solution step-by-step: compute daily net revenue per channel, identify each user's first purchase and refund status, calculate the 3-day rolling sum, and finally assemble the two tidy data frames with the required columns.

Pro tip: Always validate your results with small, hand-checked examples and ensure you handle edge cases like refunds without matching purchases or users with no purchases. Also, use dplyr's window functions and joins efficiently to avoid loops.

1. Clarify requirements and data

Confirm the date window, the definition of net revenue (e.g., refunds subtract from revenue on the refund date or the original purchase date?), and the structure of the transactions, refunds, and users tables. Identify key columns like transaction_id, user_id, channel, amount, date, and refund_id.

2. Compute daily net revenue by channel

Filter transactions and refunds to the date window, then compute daily gross revenue per channel from transactions and daily refunds per channel from refunds. Join these and calculate net revenue as gross minus refunds, ensuring all dates and channels are represented (use complete() or tidyr::complete if needed).

3. Identify first purchase and refund status per user

For each user, find the earliest transaction date (first purchase) within the window. Then, check if that specific transaction was fully refunded by the window end by joining with refunds and comparing refund amounts to the transaction amount.

4. Calculate 3-day rolling sum of net revenue by channel

Using the daily net revenue per channel, apply a rolling sum over a 3-day window (e.g., with zoo::rollsum or slider::slide_dbl) grouped by channel, ensuring the window is based on consecutive dates (fill missing dates with 0 net revenue).

5. Assemble and return tidy data frames

Create two data frames: one with daily net revenue per channel and its 3-day rolling sum, and another with each user's first purchase date and a flag indicating if that first order was fully refunded. Ensure column names and types match the required structure.

Key Points to Mention

Definition of net revenue: gross revenue minus refunds, and whether refunds are attributed to the refund date or original purchase date.
Handling of date windows: inclusive/exclusive boundaries and time zones if applicable.
Use of dplyr verbs: filter, group_by, summarise, mutate, left_join, and window functions like cumsum or rollapply.
Edge cases: users with no purchases, refunds without matching transactions, partial refunds, and missing dates in the time series.
Efficiency: avoiding loops by using vectorized operations and proper joins.
Output format: ensuring the two data frames are tidy with the specified columns (e.g., date, channel, net_revenue, rolling_3day_net_revenue; user_id, first_purchase_date, fully_refunded_flag).

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.