WITH gaps AS (

SELECT

block_id,

LAG(

block_id,

1

) OVER (

ORDER BY block_id ASC

) AS previous_block_id

FROM (

SELECT

DISTINCT block_id

FROM osmosis.core.fact_blocks

)

SELECT

DATE_TRUNC(

'd',

b.block_timestamp

) AS _date,

SUM (

A.block_id - A.previous_block_id - 1

) AS gap

FROM gaps A

LEFT OUTER JOIN osmosis.core.fact_blocks b

USING (block_id)

WHERE

A.block_id - A.previous_block_id <> 1

AND

block_timestamp :: date >= '2021-06-18'

GROUP BY 1