Daily return
mediumDaily return
Coinbase SQL Interview Question
Coinbase shows a percentage move beside every asset, and it has to be measured against that asset's own previous close. Against a fixed starting price, a coin that fell last week would still look like it is falling today, which is how people make bad decisions with real money.
Return the symbol, the priced_on date, the close_usd price, and the percentage change from that asset's previous close as pct_change, rounded to 2 decimal places. The first day of each asset has nothing before it, so leave its change empty. 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 | pct_change |
|---|---|---|---|
| BTC | 2024-05-01 | 58000 | NULL |
| BTC | 2024-05-02 | 59200 | 2.07 |
| BTC | 2024-05-03 | 60500 | 2.2 |
| BTC | 2024-05-04 | 61100 | 0.99 |
| BTC | 2024-05-05 | 59800 | -2.13 |
| ETH | 2024-05-01 | 2900 | NULL |
| ETH | 2024-05-02 | 2860 | -1.38 |
| ETH | 2024-05-03 | 2945 | 2.97 |
| ETH | 2024-05-04 | 3010 | 2.21 |
| ETH | 2024-05-05 | 3080 | 2.33 |
Explanation
BTC opens the example at 58000 with nothing before it, so its change is empty. The next day at 59200 is 2.07 percent higher, and the day after that is 2.2 percent higher again. ETH starts its own comparison at 2900 rather than carrying on from BTC.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Coinbase
Difficulty
medium
Topic
window functions
Language
SQL
Your query
Starting the SQL engine...