Referrer Revenue: Compute Total Revenue Attributed to Each Referral Source

Question

## Problem

You have a `referrals` table and an `orders` table. For each referral code, compute the total revenue it generated, the number of distinct users who converted, and the average order value. Return results sorted by total revenue descending.

```sql
-- Schema
referrals(referral_id, referral_code, referrer_user_id, referred_user_id, created_at)
orders(order_id, user_id, amount, created_at)
```

**Example output:**
```
referral_code | total_revenue | converted_users | avg_order_value
REF_A         | 4500.00       | 12              | 375.00
REF_B         | 2100.00       | 7               | 300.00
```

## Follow-ups

1. A referred user places multiple orders — should all orders count or only the first? How would you change the query?
2. How would you attribute revenue if a user was referred by multiple codes (last-touch vs. first-touch)?
3. Write a query to find referrers whose referred users have a 30-day retention rate above 50%.
4. If the referrals table has 500M rows, how would you optimize this query in a columnar store like BigQuery?

Referrer Revenue: Compute Total Revenue Attributed to Each Referral Source

Question Details

Full Details

Topics

More from Uber