SQL Quest › SQL Interview Questions › Conditional Logic
Referred vs Organic: First-Month Spend
Do referred users behave differently? A question that lives on NULL handling. users.referred_by holds the referrer's user_id, or NULL for organic signups. For every user, sum the amount_gbp of completed transactions within 30 days of signup — and give users with none a spend of 0, not NULL, so they count in the average.
Label each user source as referred or organic. Return source, users, avg_first_month_gbp (rounded to 2 decimals) and never_transacted (users whose first-month spend is 0). Order by avg_first_month_gbp descending.
Solve it in the browser editor →
Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect".
Schema
users
| user_id | signup_date | country | home_currency | plan | plan_since | kyc_verified_at | referred_by | birth_year |
|---|---|---|---|---|---|---|---|---|
| 1 | 2025-06-01 | IE | EUR | standard | 2025-06-01 | 2025-06-03 16:00:00 | NULL | 1972 |
| 2 | 2025-06-02 | FR | EUR | standard | 2025-06-02 | 2025-06-04 16:00:00 | NULL | 1979 |
| 3 | 2025-06-03 | GB | GBP | standard | 2025-06-03 | 2025-06-08 04:00:00 | NULL | 1977 |
transactions
| txn_id | user_id | ts | type | amount | currency | amount_gbp | fee_gbp | merchant_category | merchant_country | counterparty_user_id | status | decline_reason |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 2025-06-14 11:01:51 | fx_exchange | 32271.15 | JPY | 167.81 | 1.68 | NULL | NULL | NULL | completed | NULL |
| 2 | 1 | 2025-06-14 11:17:45 | bill_payment | 59.53 | EUR | 50.6 | 0 | utilities | IE | NULL | completed | NULL |
| 3 | 7 | 2025-06-16 10:06:38 | card_payment | 122.27 | EUR | 103.93 | 0 | shopping | ES | NULL | completed | NULL |
Expected output: source=referred, users=41, avg_first_month_gbp=287.04, never_transacted=11; source=organic, ...
Hint
Concepts
SELECT CASE COALESCE LEFT JOIN CTE julianday CASE + COALESCE
Practise the topic: SQL practice questions · CASE WHEN practice · NULL handling practice · JOIN practice · CTE practice
In these company practice sets
A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.
Related questions
Where would this cost you points in an interview?
Ten questions, no signup: a Skillmap across nine SQL skills and the one to fix first.
Take the readiness test