Longest rising streak
hardLongest rising streak
Coinbase SQL Interview Question
The line readers quote back from Coinbase's weekly market note is the one about momentum: how many days in a row an asset closed higher than the day before. A single large jump is noise. Five consecutive green days is a trend worth a sentence.
For each asset, return the symbol and the length of its longest unbroken run of days that closed higher than the previous day as rising_days. Count the run as the number of rising days in it.
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 | rising_days |
|---|---|
| BTC | 3 |
| ETH | 3 |
Explanation
BTC rose on three days in a row in the example before turning down, so its longest run is 3. ETH fell on its second day and then rose on each of the three days after it, which is also a run of 3.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Coinbase
Difficulty
hard
Topic
window functions
Language
SQL
Your query
Starting the SQL engine...