0xHaM-dFee Over Time
Updated 2022-11-27Copy Reference Fork
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 AVAX_PRICE AS (
SELECT
BLOCK_TIMESTAMP::date as date_Price,
avg(AMOUNT_IN_USD/AMOUNT_IN) as "PRICE (USD)"
FROM avalanche.sushi.ez_swaps
WHERE symbol_in = 'WAVAX'
and symbol_out = 'USDC'
AND BLOCK_TIMESTAMP::date >= '2022-011-01'
group by 1
ORDER by 1
)
-- , txTb as (
select
block_timestamp::date as date,
'Trader Joe' as platform,
COUNT(DISTINCT from_address) as usr_cnt,
COUNT(DISTINCT TX_HASH) as tx_cnt,
sum(TX_FEE * "PRICE (USD)") as fee_amt,
sum(fee_amt) over (order by date) as cum_fee_amt
from avalanche.core.fact_transactions a join AVAX_PRICE b on a.block_timestamp::date = b.date_Price
where to_address = lower('0x60aE616a2155Ee3d9A68541Ba4544862310933d4')
AND date >= CURRENT_DATE - 30
and date <= CURRENT_DATE - 1
group by 1,2
UNION ALL
select
block_timestamp::date as date,
'Pangolin' as platform,
COUNT(DISTINCT from_address) as usr_cnt,
COUNT(DISTINCT TX_HASH) as tx_cnt,
sum(TX_FEE * "PRICE (USD)") as fee_amt,
sum(fee_amt) over (order by date) as cum_fee_amt
from avalanche.core.fact_transactions a join AVAX_PRICE b on a.block_timestamp::date = b.date_Price
where to_address = lower('0xe54ca86531e17ef3616d22ca28b0d458b6c89106')
Run a query to Download Data