For each user, find their longest streak of consecutive days on which they placed at least one trade. Return user_id and max_consecutive_days, ordered by max_consecutive_days descending.
Schema
trades
trade_idINTEGER
user_idINTEGER
cryptoVARCHAR
quantityDECIMAL
priceDECIMAL
traded_atTIMESTAMP
Target output
Target output
| user_id | max_consecutive_days |
|---|
| 3 | 7 |
| 1 | 5 |
| 4 | 4 |
| 2 | 3 |
What this question tests
The classic gaps-and-islands problem: turning runs of consecutive days into groups, then measuring the longest run. It is a hard-tier staple across interview loops; deriving the grouping trick under pressure is the test, and candidates who have it find most other window-function questions easy.
More SQL interview questions
