Transaction Share of User Spend

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

Etsy

Problem

We measure every purchase in transactions against everything its user has ever spent, yet each purchase keeps its own line instead of collapsing into one row per user. Show the transaction ID, the username from users, the amount and that share as a decimal, with usernames in alphabetical order and each user's purchases by ascending transaction ID.

Each transaction's share of the whole.

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

Expected output

Worked solution and explanation

This is a grain problem dressed up as a spend report. The numerator lives at the transaction grain and the denominator lives at the user grain, and the whole question is whether you can put both on one row without losing either. Everyone knows how to get SUM(total_amount) per user. The reflex is GROUP BY user_id, and the moment you write it, transaction_id and each row's total_amount are folded into 15 buyer rows. You cannot report a share of a purchase you just collapsed away.

Two grains on one row

Step 1: Attach the username

Inner-join transactions to users on user_id. Every buyer exists in users and user_id is unique there, so the join neither drops nor duplicates a purchase: 200 rows in, 200 rows out.

Step 2: Broadcast the user total

SUM(t.total_amount) OVER (PARTITION BY t.user_id) computes each user's all-time spend and hands that number to every one of their rows. There is no ORDER BY inside the window, so the frame is the whole partition. Add one and you silently get a running total instead.

Step 3: Divide at the row

t.total_amount * 1.0 / ... is the share. total_amount is REAL on this seed, so the * 1.0 is insurance here, not a fix. It is the habit that saves you the day the amount is stored as integer cents.

Step 4: Sort by what you return

Order by u.username, then t.transaction_id. Both are output columns, so the order can be checked from the rows alone. The leading space in ' amelia' sorts ahead of every letter, which is why that user opens the result.

SELECT
    t.transaction_id,
    u.username,
    t.total_amount,
    t.total_amount * 1.0 / SUM(t.total_amount) OVER (PARTITION BY t.user_id) AS spend_share
FROM transactions t
JOIN users u ON t.user_id = u.user_id
ORDER BY u.username, t.transaction_id

Collapse first

GROUP BY t.user_id with SUM(t.total_amount) returns 15 rows, one per buyer. The transaction IDs and amounts are gone, and getting them back means joining the totals onto transactions in a second pass.

Broadcast instead

SUM(...) OVER (PARTITION BY t.user_id) returns all 200 rows, each with its own amount next to its user's total. One pass and nothing to reassemble.

Common follow-up questions

• What if a user's purchases net to exactly zero? (Division by zero: guard the denominator with `NULLIF(..., 0)` and decide what the share means then.)
• How would you show the share as a percentage rounded to two places? (Scale by 100 and wrap in `ROUND(..., 2)` without changing the window.)
• What if you wanted each purchase's share of its product category instead? (Join `products` on `product_id`, handle the purchases with a `NULL` product, and partition by `category`.)
• How would you show a cumulative share as each user's spend builds up over time? (Add `ORDER BY transaction_date` inside the window for a running numerator, keeping the full-partition total as the denominator.)