Updated 2023-01-13
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
34
35
›
⌄
WITH priceTb as (
SELECT
HOUR::date as p_date,
symbol,
avg(price) as price_usd
FROM ethereum.core.fact_hourly_token_prices
WHERE symbol in ('MATIC', 'WETH')
GROUP by 1,2
)
-- swap_tx as (
SELECT
block_timestamp::date as date,
'Etherum' as chain,
count(DISTINCT TX_HASH) as tx_cnt,
sum(TX_FEE * price_usd) as fee_amt_usd,
sum(tx_cnt) over (order by date) as cum_tx_cnt,
sum(fee_amt_usd) over (order by date) as cum_fee_amt_usd
FROM ethereum.core.fact_transactions a join priceTb b on a.block_timestamp::date = b.p_date
WHERE TX_JSON:to = '0xaf0b0000f0210d0f421f0009c72406703b50506b'
and symbol = 'WETH'
group by 1
UNION ALL
SELECT
block_timestamp::date as date,
'Polygon' as chain,
count(DISTINCT TX_HASH) as tx_cnt,
sum(TX_FEE * price_usd) as fee_amt_usd,
sum(tx_cnt) over (order by date) as cum_tx_cnt,
sum(fee_amt_usd) over (order by date) as cum_fee_amt_usd
FROM polygon.core.fact_transactions a join priceTb b on a.block_timestamp::date = b.p_date
WHERE TX_JSON:to = '0xaf0b0000f0210d0f421f0009c72406703b50506b'
and symbol = 'MATIC'
group by 1
Run a query to Download Data