Microsoft: Days Between Windows Insider Flights per Channel

Medium
785.

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.

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

ColumnType
build_numberint
channelvarchar
released_ondate
is_pulledbool

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.

Example

The rows must come back in the order shown.

InsiderFlight

build_numberchannelreleased_onis_pulled
27764Canary2025-01-080
27768Canary2025-01-101
27774Canary2025-01-220
26120Dev2025-01-090
26200Dev2025-01-240
26212Dev2025-02-070
22635Beta2025-01-160

Output

channelbuild_numberreleased_ondays_since_previous
Beta226352025-01-16NULL
Canary277642025-01-08NULL
Canary277742025-01-2214
Dev261202025-01-09NULL
Dev262002025-01-2415
Dev262122025-02-0714
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
build_numberint
channelvarchar
released_ondate
is_pulledbool

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_numberchannelreleased_onis_pulled
27764Canary2025-01-080
27768Canary2025-01-101
27774Canary2025-01-220
26120Dev2025-01-090
26200Dev2025-01-240
26212Dev2025-02-070
22635Beta2025-01-160

Output

channelbuild_numberreleased_ondays_since_previous
Beta226352025-01-16NULL
Canary277642025-01-08NULL
Canary277742025-01-2214
Dev261202025-01-09NULL
Dev262002025-01-2415
Dev262122025-02-0714

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_on

Another 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 →