Write SQL for profit, growth, retention
Problem Description
You are tasked with analyzing e-commerce data to derive key business metrics using SQL. Assume all timestamps are in UTC, and the current date ("today") is 2025-09-01.
Database 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 is provided for clarity, but you should write queries that work against the full schema.
Tasks
-
7-Day Rolling Gross Profit by Category For each calendar day
din the range[2025-08-26, 2025-09-01], compute the 7-day rolling gross profit.- Category Bucketing: Group products into 'Speaker' and 'Other' categories.
- Daily Gross Profit Calculation: Consider only orders where the
order_datematchesd,statusis not 'cancelled', andis_testisfalse. Gross profit for an order item is calculated asqty * (unit_price_cents - discount_cents - cogs_cents). - Refunds Impact: Treat refunds as negative profit. Reduce revenue and associated COGS proportionally to the
qty_refundedon therefund_tsdate. - Output:
d,category_bucket,rolling_7d_gross_profit_cents.
-
Top 2 Products by Week-over-Week Revenue Growth Identify the top 2 products with the highest week-over-week revenue growth.
- Order Filtering: Consider only non-test, non-cancelled orders.
- Comparison Period: Compare revenue for the week
[2025-08-26, 2025-09-01]against the prior 7 days (i.e.,[2025-08-19, 2025-08-25]). - Refunds Impact: Treat refunds as negative revenue on their
refund_tsdate. - Output:
product_id,name,wow_growth_pct.
-
D1 Retention by Country For users whose first recorded event occurred between
2025-08-26and2025-08-31(inclusive), compute their D1 retention rate by country.- D1 Retention Definition: A user is D1 retained if they had any event exactly one day after their first event.
- Implementation Guidance: Utilize window functions and calendar tables. Do not assume dense dates in the
eventstable. - Output:
country,cohort_start_date,d1_retention_rate.
AI interview prep
Core dilemma
Accurately deriving complex, time-sensitive business metrics (profit, revenue, retention) from granular transactional data, correctly accounting for edge cases like refunds and sparse data, while optimizing for query performance and maintainability.
3 tradeoffs and 3 stress tests locked
Interview prep
Unlock the interviewer X-Ray rubric, tradeoff matrix, stress tests, clarifying questions, and key concepts.
Write SQL for profit, growth, retention
Problem Description
You are tasked with analyzing e-commerce data to derive key business metrics using SQL. Assume all timestamps are in UTC, and the current date ("today") is 2025-09-01.
Database 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 is provided for clarity, but you should write queries that work against the full schema.
Tasks
-
7-Day Rolling Gross Profit by Category For each calendar day
din the range[2025-08-26, 2025-09-01], compute the 7-day rolling gross profit.- Category Bucketing: Group products into 'Speaker' and 'Other' categories.
- Daily Gross Profit Calculation: Consider only orders where the
order_datematchesd,statusis not 'cancelled', andis_testisfalse. Gross profit for an order item is calculated asqty * (unit_price_cents - discount_cents - cogs_cents). - Refunds Impact: Treat refunds as negative profit. Reduce revenue and associated COGS proportionally to the
qty_refundedon therefund_tsdate. - Output:
d,category_bucket,rolling_7d_gross_profit_cents.
-
Top 2 Products by Week-over-Week Revenue Growth Identify the top 2 products with the highest week-over-week revenue growth.
- Order Filtering: Consider only non-test, non-cancelled orders.
- Comparison Period: Compare revenue for the week
[2025-08-26, 2025-09-01]against the prior 7 days (i.e.,[2025-08-19, 2025-08-25]). - Refunds Impact: Treat refunds as negative revenue on their
refund_tsdate. - Output:
product_id,name,wow_growth_pct.
-
D1 Retention by Country For users whose first recorded event occurred between
2025-08-26and2025-08-31(inclusive), compute their D1 retention rate by country.- D1 Retention Definition: A user is D1 retained if they had any event exactly one day after their first event.
- Implementation Guidance: Utilize window functions and calendar tables. Do not assume dense dates in the
eventstable. - Output:
country,cohort_start_date,d1_retention_rate.