Capital Gain/Loss per User
Fintech
Problem Statement
You are analyzing a fintech database for stock market activity. Each record is either a Buy or a Sell transaction.
Write an SQL query to report the Capital Gain/Loss for each user.
Rules:
- Capital Gain/Loss is calculated as the sum of (Sell Price - Buy Price) for all trades.
- Every Buy transaction will eventually have a corresponding Sell transaction (or vice versa depending on the period).
- Output columns: user_id, capital_gain_loss.
- Results must be sorted by user_id in ascending order.
Tests your understanding of
Conditional Aggregation, Financial Logic and Arithmetic
Input Tables
Stocks
| user_id(INTEGER) | operation(VARCHAR) | operation_day(INTEGER) | price(INTEGER) |
|---|---|---|---|
| 1 | Buy | 1 | 1000 |
| 1 | Sell | 5 | 1500 |
| 2 | Buy | 1 | 7000 |
| 2 | Sell | 10 | 1000 |
| 1 | Buy | 12 | 3000 |
| 1 | Sell | 15 | 3500 |
Users
| user_id(INTEGER) | username(VARCHAR) |
|---|---|
| 1 | Trader_Alpha |
| 2 | Trader_Beta |
Audit_Trail
| transaction_id(INTEGER) | user_id(INTEGER) | verified(BOOLEAN) |
|---|---|---|
| 99 | 1 | true |
Expected Output
| user_id(INTEGER) | capital_gain_loss(INTEGER) |
|---|---|
| 1 | 1000 |
| 2 | -6000 |
Tags
MediumConditional AggregationFinancial LogicArithmetic
15-20 min
78%