Capital Gain/Loss
Robinhood
Problem Statement
A stock brokerage platform needs to calculate the net profit or loss for its users. You are given a table containing all buy and sell operations.
Write an SQL query to report the Capital gain/loss for each stock.
Rules:
- The capital gain/loss of a stock is the total sum after completing all sell and buy operations for that stock.
- Every Buy operation for a stock has a corresponding Sell operation eventually.
- The result column should be named capital_gain_loss.
- Results must be sorted by capital_gain_loss in descending order.
Tests your understanding of
Basic SQL, Aggregation, CASE WHEN and GROUP BY
Input Tables
stocks
| stock_name(VARCHAR) | operation(VARCHAR) | operation_day(INTEGER) | price(INTEGER) |
|---|---|---|---|
| Leetcode | Buy | 1 | 1000 |
| Corona Masks | Buy | 2 | 10 |
| Leetcode | Sell | 5 | 9000 |
| Handbags | Buy | 17 | 30000 |
| Corona Masks | Sell | 3 | 1010 |
| Corona Masks | Buy | 4 | 1000 |
| Corona Masks | Sell | 5 | 500 |
| Corona Masks | Buy | 6 | 1000 |
| Corona Masks | Sell | 10 | 10000 |
| Handbags | Sell | 29 | 7000 |
| Leetcode | Buy | 11 | 100 |
| Leetcode | Sell | 15 | 100 |
Expected Output
| stock_name(VARCHAR) | capital_gain_loss(INTEGER) |
|---|---|
| Corona Masks | 9500 |
| Leetcode | 8000 |
| Handbags | -23000 |
Tags
MediumBasic SQLAggregationCASE WHENGROUP BY
10-15 min
92%