Drawdown from the peak
hardDrawdown from the peak
Coinbase SQL Interview Question
Coinbase shows how far an asset sits below its own running high, because that is the number a holder actually feels. Someone who bought at the top does not care that the coin is up over the year. They care that it is well below the best price they have seen.
Return the symbol, the priced_on date, the close_usd price, and the highest close seen for that asset up to and including that day as peak_to_date. Add how far below that peak the day closed as drawdown_pct, a percentage rounded to 2 decimal places. Sort by symbol and then by priced_on.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
- Data Engineer
- Data Scientist
coinbase_daily_pricesTable24 rows
| Column Name | Type |
|---|---|
| symbol | VARCHAR |
| priced_on | DATE |
| close_usd | DOUBLE |
coinbase_daily_pricesExample Input
| symbol | priced_on | close_usd |
|---|---|---|
| BTC | 2024-05-01 | 58000 |
| BTC | 2024-05-02 | 59200 |
| BTC | 2024-05-03 | 60500 |
| BTC | 2024-05-04 | 61100 |
| BTC | 2024-05-05 | 59800 |
| ETH | 2024-05-01 | 2900 |
| ETH | 2024-05-02 | 2860 |
| ETH | 2024-05-03 | 2945 |
| ETH | 2024-05-04 | 3010 |
| ETH | 2024-05-05 | 3080 |
Example Output
| symbol | priced_on | close_usd | peak_to_date | drawdown_pct |
|---|---|---|---|---|
| BTC | 2024-05-01 | 58000 | 58000 | 0 |
| BTC | 2024-05-02 | 59200 | 59200 | 0 |
| BTC | 2024-05-03 | 60500 | 60500 | 0 |
| BTC | 2024-05-04 | 61100 | 61100 | 0 |
| BTC | 2024-05-05 | 59800 | 61100 | -2.13 |
| ETH | 2024-05-01 | 2900 | 2900 | 0 |
| ETH | 2024-05-02 | 2860 | 2900 | -1.38 |
| ETH | 2024-05-03 | 2945 | 2945 | 0 |
| ETH | 2024-05-04 | 3010 | 3010 | 0 |
| ETH | 2024-05-05 | 3080 | 3080 | 0 |
Explanation
BTC rises for four days, and on each of them the peak so far is the day it closed at, so there is no drawdown. On the fifth day it closes at 59800 while the peak so far is 61100, which puts it 2.13 percent below. ETH dips on its second day and sets a new peak on the third.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Coinbase
Difficulty
hard
Topic
running totals
Language
SQL
Your query
Starting the SQL engine...