Find the Months a Customer Skipped

The monthly_orders table contains a row for each customer-month with recorded activity. Write a query to report gaps between consecutive recorded months for the same customer.

Return the earlier month as gap_after, the next recorded month as gap_before, and the number of absent calendar months between them as months_missing. Include only gaps containing at least one missing month.

A recorded month with zero orders is present, not missing. Do not report an open-ended gap after a customer’s final recorded month.

Ordering: Sort by customer_id, then gap_after in ascending order.

Returncustomer_idgap_aftergap_beforemonths_missing

Columns

monthly_orders3 columns

columntype

customer_idVARCHAR

monthDATE

ordersBIGINT

Sample data & expected output

8 rows · 3 columns

customer_idmonthorders

c_0012026-02-015

c_0012026-05-012

c_0012026-06-014

c_0022026-01-011

c_0022026-04-017

c_0042026-01-012

c_0042026-02-010

c_0042026-03-016

If you get stuck

Show hint · 3 hints Walkthrough Reveal answer

About this problem

You practise finding what is not in a table, using the rows that are. For each customer you report every stretch of absent months between two months that are present, which is the check you run before trusting any lag-based period comparison on a table like this. The skill is converting a distance between two dates into a count of the things between them.

The off-by-one is the heart of it. January to April is a three-month distance and a two-month hole, and the difference between those two numbers is invisible in the output: 3 is as believable as 2 unless you count the months on your fingers. It is worth doing that once, explicitly, because the same arithmetic shows up every time anyone converts a span into a count. The second thing worth noticing is why this problem exists at all. LAG(revenue, 12) means twelve rows back, and on a table where inactive months are absent, twelve rows back is twelve months back only for customers who ordered every month. c_001 skipped March and April, so a twelve-row lag would compare them against the wrong period and report growth that is an artefact of the missing data. Finding the gaps first is what tells you whether the period-over-period report can be trusted or whether the series needs filling in. And c_004 is there to make the distinction precise: a month with zero orders is present, and treating it as missing would report a gap that nobody skipped.

Read the lesson: LAG, LEAD & Value Functions (P17) →

Your query · runs in your browser⌘ Enter to run

Loading editor…

Output · run to see rows