Madilast 24 hours count
Updated 2022-10-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
29
30
31
32
33
›
⌄
with df as (select
a.BLOCK_TIMESTAMP as BLOCK_TIMESTAMP,
a.TX_HASH as TX_HASH,
b.platform as platform,
a.TX_FEE as TX_FEE,
a.GAS_PRICE as GAS_PRICE,
a.GAS_USED as GAS_USED
from
ethereum.core.fact_transactions a
join ethereum.core.ez_dex_swaps b
on a.tx_hash = b.tx_hash)
SELECT
-- date_trunc('day', BLOCK_TIMESTAMP) as date,
'Uniswap v3' as version,
sum(GAS_PRICE*GAS_USED) as gas_volume,
sum (GAS_USED) as total_gas_used,
count(DISTINCT tx_hash) as total_tx
from df
WHERE platform = 'uniswap-v3' and date_trunc('hour', BLOCK_TIMESTAMP) >= dateadd(hour,-24,getdate())
UNION ALL
SELECT
-- date_trunc('day', BLOCK_TIMESTAMP) as date,
'Uniswap v2' as version,
sum(GAS_PRICE*GAS_USED) as gas_volume,
sum (GAS_USED) as total_gas_used,
count(DISTINCT tx_hash) as total_tx
from df
WHERE platform = 'uniswap-v2' and date_trunc('day', BLOCK_TIMESTAMP) >= dateadd(hour,-24,getdate())
Run a query to Download Data