# Transaction Share of User Spend

> Each transaction's share of the whole.

Canonical URL: <https://datadriven.io/problems/transaction_share_of_user_spend>

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

For each transaction, show the transaction ID, username, the transaction amount, and the ratio of that transaction's amount to the user's overall total as a decimal. Each username is unique and each user has at most one transaction per day.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests the ability to combine a join with a window function to compute a per-row ratio relative to a user-level total. Interviewers check whether you understand `SUM() OVER (PARTITION BY ...)` as an alternative to a self-join.

---

### Break down the requirements

#### Step 1: Join users to transactions

`JOIN transactions ON users.user_id = transactions.user_id` enriches each transaction with the username.

#### Step 2: Compute user-level total as a window

`SUM(total_amount) OVER (PARTITION BY t.user_id)` computes each user's overall total without collapsing rows.

#### Step 3: Calculate the ratio

Divide `total_amount` by the user total to get each transaction's share as a decimal.

---

### The solution

**Window function for per-row share of user total**

```sql
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
```

> **Cost Analysis**
>
> The join produces 80M rows (one per transaction for users in both tables). The window function partitions by `user_id` (4M partitions, ~20 rows each on average). No sorting is needed within each partition for SUM.

> **Interviewers Watch For**
>
> Candidates who use a self-join or correlated subquery to compute the user total. The window function approach is cleaner and typically faster because it avoids a second pass over the data.

> **Common Pitfall**
>
> Integer division. If `total_amount` is an integer type, `total_amount / SUM(...)` truncates to 0 for most rows. Multiplying by `1.0` forces decimal division.

---

## Common follow-up questions

- What if a user's total spend is zero? _(Division by zero. You would need a CASE or NULLIF to handle it.)_
- How would you round the share to 4 decimal places? _(Wrap in ROUND(..., 4).)_
- What if you needed the share relative to the category total instead of user total? _(Change PARTITION BY to category, requiring a join to products.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/transaction_share_of_user_spend)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.