Microsoft: Days Between Windows Insider Flights per Channel
The Windows Insider programme team tracks release cadence: how many days pass between consecutive builds flighted to the same channel.
- Difficulty: Medium
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #785
Problem statement
The Windows Insider programme team tracks release cadence: how many days pass between consecutive builds flighted to the same channel.
Ignoring builds that were pulled (is_pulled = 1, withdrawn after release), return every remaining build with channel, build_number, released_on and days_since_previous — the days since the previous non-pulled build in the same channel, or NULL for a channel's first. Order by channel, then released_on.
A practice scenario set at Microsoft: the tables and data are invented, and this is not a question from, or endorsed by, Microsoft.
Tables
Table: InsiderFlight
| Column | Type |
|---|---|
| build_number | int |
| channel | varchar |
| released_on | date |
| is_pulled | bool |
Primary key: build_number.
One row per build flighted to Insiders. channel is one of Canary, Dev, Beta, Release Preview; a channel never gets two builds on the same day.
Examples
Example 1
InsiderFlight
| build_number | channel | released_on | is_pulled |
|---|---|---|---|
| 27764 | Canary | 2025-01-08 | 0 |
| 27768 | Canary | 2025-01-10 | 1 |
| 27774 | Canary | 2025-01-22 | 0 |
| 26120 | Dev | 2025-01-09 | 0 |
| 26200 | Dev | 2025-01-24 | 0 |
| 26212 | Dev | 2025-02-07 | 0 |
| 22635 | Beta | 2025-01-16 | 0 |
Output
| channel | build_number | released_on | days_since_previous |
|---|---|---|---|
| Beta | 22635 | 2025-01-16 | NULL |
| Canary | 27764 | 2025-01-08 | NULL |
| Canary | 27774 | 2025-01-22 | 14 |
| Dev | 26120 | 2025-01-09 | NULL |
| Dev | 26200 | 2025-01-24 | 15 |
| Dev | 26212 | 2025-02-07 | 14 |
How to solve Microsoft: Days Between Windows Insider Flights per Channel
Each row needs a value from the row before it in its channel, which is what LAG does: partition by channel, order by released_on, and LAG(released_on) is the previous release date. DATEDIFF of the two gives the gap in days, and for the first build in a channel LAG returns NULL, so the gap is NULL as asked.
Where the pulled builds are removed matters. WHERE runs before window functions, so filtering there means the window only ever sees surviving builds — a pulled build in the middle is skipped and the next build measures its gap from the one before it (27774 measures 14 days from 27764, not 12 from the pulled 27768).
The alternative finds the previous date with a correlated MAX(released_on) over earlier non-pulled builds of the channel — the pre-window-function way, which re-scans the channel per row. The final ORDER BY sorts channel names alphabetically, so Beta comes first.
Reference solution (MySQL)
SELECT channel, build_number, released_on,
DATEDIFF(released_on, LAG(released_on) OVER (PARTITION BY channel ORDER BY released_on)) AS days_since_previous
FROM InsiderFlight
WHERE is_pulled = 0
ORDER BY channel, released_onAnother way
SELECT f.channel, f.build_number, f.released_on,
DATEDIFF(f.released_on, (SELECT MAX(p.released_on) FROM InsiderFlight p
WHERE p.channel = f.channel AND p.is_pulled = 0 AND p.released_on < f.released_on)) AS days_since_previous
FROM InsiderFlight f
WHERE f.is_pulled = 0
ORDER BY f.channel, f.released_on← Microsoft: Paid but Unassigned Microsoft 365 Seats on 30 June · Microsoft: Bing Query Abandonment Rate by Market →