Write SQL for profit, growth, retention
Company: Roku
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Write ANSI-SQL (PostgreSQL preferred) for the tasks below. Assume all timestamps are UTC and that "today" = 2025-09-01.
Schema:
- products(product_id INT, name TEXT, category TEXT)
- users(user_id INT, signup_date DATE, country TEXT)
- orders(order_id INT, user_id INT, order_ts TIMESTAMP, status TEXT, is_test BOOLEAN)
- order_items(order_id INT, product_id INT, qty INT, unit_price_cents INT, discount_cents INT, cogs_cents INT)
- refunds(refund_id INT, order_id INT, product_id INT, qty_refunded INT, refund_cents INT, refund_ts TIMESTAMP)
- events(user_id INT, event_ts TIMESTAMP, event_name TEXT)
Small sample data (for clarity only):
products
product_id | name | category
1 | Pod Mini | Speaker
2 | Beans 1lb | Grocery
3 | Mug | Merch
4 | Pod Max | Speaker
users
user_id | signup_date | country
101 | 2025-08-20 | US
102 | 2025-08-25 | US
103 | 2025-08-28 | CA
104 | 2025-08-30 | US
orders
order_id | user_id | order_ts | status | is_test
1001 | 101 | 2025-08-26 10:00:00 | placed | false
1002 | 101 | 2025-08-27 12:00:00 | shipped | false
1003 | 102 | 2025-08-27 15:00:00 | cancelled | false
1004 | 103 | 2025-08-30 09:00:00 | shipped | false
1005 | 104 | 2025-08-31 20:00:00 | shipped | true
order_items
order_id | product_id | qty | unit_price_cents | discount_cents | cogs_cents
1001 | 1 | 1 | 4999 | 0 | 3000
1002 | 2 | 2 | 1299 | 100 | 600
1002 | 3 | 1 | 999 | 0 | 400
1004 | 4 | 1 | 8999 | 0 | 5500
1005 | 2 | 1 | 1299 | 0 | 600
refunds
refund_id | order_id | product_id | qty_refunded | refund_cents | refund_ts
1 | 1002 | 2 | 1 | 1299 | 2025-09-01 11:00:00
2 | 1004 | 4 | 1 | 8999 | 2025-09-02 10:00:00
events
user_id | event_ts | event_name
101 | 2025-08-26 09:00:00 | app_open
101 | 2025-08-27 10:00:00 | purchase
102 | 2025-08-26 08:00:00 | app_open
102 | 2025-08-27 08:30:00 | app_open
103 | 2025-08-30 09:15:00 | app_open
104 | 2025-08-31 21:00:00 | app_open
104 | 2025-09-01 21:05:00 | app_open
Tasks:
1) For each calendar day d in [2025-08-26, 2025-09-01], compute the 7-day rolling gross profit by category bucket ('Speaker' vs 'Other'). Daily gross profit uses orders where order_date = d and status not in ('cancelled') and is_test = false: sum over items of qty*(unit_price_cents - discount_cents - cogs_cents). Treat refunds as negative profit on refund_ts date (reduce revenue and associated COGS proportionally to refunded qty). Output: d, category_bucket, rolling_7d_gross_profit_cents.
2) Among non-test, non-cancelled orders, find the top 2 products by week-over-week revenue growth for week [2025-08-26..2025-09-01] versus the prior 7 days; treat refunds as negative revenue on refund_ts. Output: product_id, name, wow_growth_pct.
3) For users whose first event_date is between 2025-08-26 and 2025-08-31 inclusive, compute D1 retention (had any event exactly one day later) by country. Output: country, cohort_start_date, d1_retention_rate.
Use window functions and calendar tables; do not assume dense dates.
Overview: This question evaluates a candidate's ability to perform advanced SQL data manipulation — specifically rolling-window aggregations, revenue and gross profit computations that account for refunds, order status and test flags, plus cohort retention and week-over-week growth analysis.
Read the full Roku Data Scientist interview experience this question came from
7-day rolling gross profit by day and category bucket (with refunds)
Compute the 7-day rolling gross profit (in cents) for each calendar day d from 2025-08-26 to 2025-09-01 (inclusive), split into category buckets: 'Speaker' vs 'Other'.
Definitions:
- Include only orders where status NOT IN ('cancelled') and is_test = false.
- Daily gross profit on an order date is: SUM over items of qty * (unit_price_cents - discount_cents - cogs_cents).
- Treat refunds as negative profit on refund_ts::date, reducing revenue and associated COGS proportionally to refunded quantity:
refund_profit_cents = - qty_refunded * (unit_price_cents - discount_cents - cogs_cents)
(use the original order_items values for that order_id + product_id).
- Rolling 7-day gross profit for day d is the sum of daily gross profit from (d - 6 days) through d (inclusive).
Output columns:
- d (DATE)
- category_bucket (VARCHAR): 'Speaker' or 'Other'
- rolling_7d_gross_profit_cents (BIGINT)
Notes:
- Use a calendar table / date series so missing dates still appear.
- Ensure both buckets appear for every day in the output window, even if the value is 0.
Tables
products(product_id INT, name TEXT, category TEXT)
users(user_id INT, signup_date DATE, country VARCHAR(2))
orders(order_id INT, user_id INT, order_ts TIMESTAMP, status TEXT, is_test BOOLEAN)
order_items(order_id INT, product_id INT, qty INT, unit_price_cents INT, discount_cents INT, cogs_cents INT)
refunds(refund_id INT, order_id INT, product_id INT, qty_refunded INT, refund_cents INT, refund_ts TIMESTAMP)
events(user_id INT, event_ts TIMESTAMP, event_name TEXT)
Hints
- Build daily profit from two sources: order item profit on order date, and refunded profit (negative) on refund date.
- Use a date series and cross join to the two buckets so you return rows for missing dates/buckets.
Top 2 products by week-over-week net revenue growth (with refunds)
Among non-test, non-cancelled orders, find the top 2 products by week-over-week net revenue growth comparing:
- Current week: 2025-08-26 through 2025-09-01 (inclusive)
- Prior week: 2025-08-19 through 2025-08-25 (inclusive)
Definitions:
- Order revenue is computed on order_ts::date as SUM(qty * (unit_price_cents - discount_cents)).
- Refunds are treated as negative revenue on refund_ts::date, using refunds.refund_cents.
- Include only orders where status <> 'cancelled' and is_test = false (both for order revenue and refunds tied to those orders).
- Week-over-week growth percent:
wow_growth_pct = 100 * (curr_week_net_revenue - prior_week_net_revenue) / prior_week_net_revenue
Output columns:
- product_id
- name
- wow_growth_pct (rounded to 2 decimals)
Return the top 2 products by wow_growth_pct (descending).
Tables
products(product_id INT, name TEXT, category TEXT)
users(user_id INT, signup_date DATE, country VARCHAR(2))
orders(order_id INT, user_id INT, order_ts TIMESTAMP, status TEXT, is_test BOOLEAN)
order_items(order_id INT, product_id INT, qty INT, unit_price_cents INT, discount_cents INT, cogs_cents INT)
refunds(refund_id INT, order_id INT, product_id INT, qty_refunded INT, refund_cents INT, refund_ts TIMESTAMP)
events(user_id INT, event_ts TIMESTAMP, event_name TEXT)
Hints
- Compute net revenue by unioning order revenue (positive) with refund revenue (negative) at the product+date grain.
- Aggregate net revenue into two 7-day windows using conditional SUM.
D1 retention by country for cohorts based on first event date
For users whose first-ever event date is between 2025-08-26 and 2025-08-31 (inclusive), compute D1 retention by country and cohort_start_date.
Definitions:
- A user's cohort_start_date is MIN(event_ts::date) across all their events.
- A user is D1 retained if they have any event on (cohort_start_date + 1 day).
- D1 retention rate = retained_users / cohort_users.
Output columns:
- country
- cohort_start_date
- d1_retention_rate (as a decimal)
Notes:
- Do not assume events are dense; compute retention via date arithmetic.
Tables
products(product_id INT, name TEXT, category TEXT)
users(user_id INT, signup_date DATE, country VARCHAR(2))
orders(order_id INT, user_id INT, order_ts TIMESTAMP, status TEXT, is_test BOOLEAN)
order_items(order_id INT, product_id INT, qty INT, unit_price_cents INT, discount_cents INT, cogs_cents INT)
refunds(refund_id INT, order_id INT, product_id INT, qty_refunded INT, refund_cents INT, refund_ts TIMESTAMP)
events(user_id INT, event_ts TIMESTAMP, event_name TEXT)
Hints
- First compute each user's cohort_start_date as MIN(event_date).
- D1 retention can be computed with an EXISTS subquery for an event exactly one day after the cohort start.