0xHaM-dFee on three platforms
Updated 2022-11-25
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
36
›
⌄
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 ('WETH')
GROUP by 1,2
)
, generatedFee as (
SELECT
block_timestamp::date as date,
'Bebop' as platfrom,
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'
group by 1
-- )
-- , uniswapFee as (
UNION ALL
SELECT
block_timestamp::date as date,
'Uniswap' as platfrom,
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_hash in (SELECT TX_HASH FROM ethereum.core.ez_dex_swaps WHERE PLATFORM = 'uniswap-v3')
AND BLOCK_TIMESTAMP::date >= '2022-09-29'
group by 1
-- )
Run a query to Download Data