forgashAurora Block Check Enhanced copy
Updated 2023-07-27
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
›
⌄
-- forked from TheLaughingMan / Aurora Block Check Enhanced @ https://flipsidecrypto.xyz/TheLaughingMan/q/KuqFXHlXqWHQ/aurora-block-check-enhanced
-- forked from Aurora Block Check @ https://flipsidecrypto.xyz/edit/queries/e63bd8c0-dab3-491c-b31a-353c602f4d98
-- forked from Aurora Base @ https://flipsidecrypto.xyz/edit/queries/430db199-f894-42e3-96c0-950babe16171
with base as (
SELECT
b.BLOCK_NUMBER
, t.BLOCK_NUMBER as t_block_no
, b.tx_count as b_txns
, b.block_timestamp::date as ddate
, CASE WHEN t_block_no IS NULL AND b_txns!=0 THEN 1 ELSE 0 END as missing
FROM aurora.core.fact_blocks b
LEFT JOIN aurora.core.fact_transactions t ON b.block_number = t.block_number
ORDER BY BLOCK_NUMBER ASC
)
SELECT
SUM(missing) as missing
, ddate
from base
GROUP BY ddate ORDER BY ddate DESC
Run a query to Download Data