Finding the gaps in data-job-postings

Finding the gaps in data-job-postings comes up often enough to be worth a note. data-job-postings is a convenient thing to try it on — 3,500 rows, one row per posting.

with numbered as (
  select seniority, posted_on,
         row_number() over (partition by seniority order by posted_on) as rn
  from data_job_postings
)
select seniority,
       min(posted_on) as island_start,
       max(posted_on) as island_end,
       count(*)   as readings
from (
  select *, date_trunc('day', posted_on) - (rn * interval '1 day') as grp_key
  from numbered
) t
group by seniority, grp_key
order by seniority, island_start;

The trick is that subtracting a dense row number from a dense date gives a constant inside a run and a different constant across a gap. Once you have seen it the query is obvious; before that it looks like a magic trick.

Run it against the real file in the SQL playground — the engine there is enough of a SQL implementation to execute this as written, and the dataset is already loaded. The pattern page has the version with the failure modes spelled out.

Window functions are the difference between SQL that describes rows and SQL that describes sequences. Almost every "we exported it to pandas to do this bit" turns out to be one of these five.