sarathminersmoving2
Updated 2022-09-17
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
29
30
›
⌄
WITH
TWO_YEARS AS (
SELECT
DATE_TRUNC(DAY, BLOCK_TIMESTAMP) DATE, COUNT(BLOCK_NUMBER) N_BLOCK
FROM
ethereum.core.fact_blocks
WHERE
-- BLOCK_TIMESTAMP >= DATEADD(MONTH,-6,CURRENT_DATE()) AND BLOCK_TIMESTAMP <= CURRENT_DATE - 1
BLOCK_TIMESTAMP >= '2020-01-01' AND BLOCK_TIMESTAMP <= CURRENT_DATE - 1
GROUP BY DATE
),
SIX_MONTHS AS (
SELECT
DATE_TRUNC(DAY, BLOCK_TIMESTAMP) DATE, COUNT(BLOCK_NUMBER) N_BLOCK
FROM
ethereum.core.fact_blocks
WHERE
BLOCK_TIMESTAMP >= DATEADD(MONTH,-6,CURRENT_DATE()) AND BLOCK_TIMESTAMP <= CURRENT_DATE - 1
-- BLOCK_TIMESTAMP >= '2020-01-01'
GROUP BY DATE
)
SELECT
TWO_YEARS.DATE, TWO_YEARS.N_BLOCK "LAST 2 YEARS", SIX_MONTHS.N_BLOCK "LAST 6 MONTHS"
FROM
TWO_YEARS
FULL JOIN
SIX_MONTHS ON TWO_YEARS.DATE = SIX_MONTHS.DATE
ORDER BY DATE
Run a query to Download Data