Gaps in the blocks

[Vahid-flipside]

Hi MDAO team, I have discovered the following gaps in the stg_blocks.sql model. Could it be fixed?

WITH source AS (SELECT
block_id, block_timestamp, LAG( block_id, 1 ) over (

    ORDER BY
        block_id ASC
) AS prev_block_id

FROM (select * from ..\stg_blocks where BLOCK_TIMESTAMP < CURRENT_DATE - 1) dbt_subquery)

SELECT
prev_block_id, block_id, block_timestamp, block_id - prev_block_id

• 1 AS gap FROM source WHERE block_id - prev_block_id <> 1 ORDER BY gap DESC

[antonyip]

Hello @Vahid-flipside,

WITH source AS (
  SELECT 
    BLOCK_ID, 
    LAG(BLOCK_ID, 1) over (
      ORDER BY 
        BLOCK_ID ASC
    ) AS prev_BLOCK_ID 
  FROM 
    (
      select 
        distinct block_id 
      from 
        chainwalkers.prod.harmony_blocks 
      where 
        BLOCK_TIMESTAMP < CURRENT_DATE - 1
    )
) 
SELECT 
  prev_BLOCK_ID, 
  BLOCK_ID, 
  BLOCK_ID - prev_BLOCK_ID - 1 AS gap 
FROM 
  source 
WHERE 
  BLOCK_ID - prev_BLOCK_ID <> 1 
ORDER BY 
  gap DESC

The issue we have is that the chainwalkers from flipside do not give us all the blocks...