theericstoneFees
Updated 2021-08-05
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
›
⌄
WITH addys AS (
SELECT distinct address as address, l1_label FROM public.ethereum_address_labels eals WHERE l1_label IN ('defi', 'dex', 'cex')),
txns as (select distinct tx_id, contract_address from gold.ethereum_events where contract_address IN ('0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48', '0x8e870d67f660d95d5be530380d0ec0bd388289e1', '0x6b175474e89094c44da98b954eedeac495271d0f', '0xdac17f958d2ee523a2206206994597c13d831ec7')
AND block_timestamp >= getdate() - interval '9 months')
SELECT date_trunc('day', block_timestamp) as metric_date,
CASE
WHEN from_address IN(select address from addys where l1_label IN('dex', 'defi')) THEN 'DeFi + DEX'
WHEN from_address IN(select address from addys where l1_label IN('cex')) THEN 'CEX'
ELSE 'Other'
END AS from_address_tag,
CASE
WHEN contract_address = '0xdac17f958d2ee523a2206206994597c13d831ec7' THEN 'USDT'
WHEN contract_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' THEN 'USDC'
WHEN contract_address = '0x6b175474e89094c44da98b954eedeac495271d0f' THEN 'DAI'
WHEN contract_address = '0x8e870d67f660d95d5be530380d0ec0bd388289e1' THEN 'PAX'
END AS symbol,
sum(fee_usd) as fee_usd
FROM gold.ethereum_transactions
JOIN txns on txns.tx_id = ethereum_transactions.tx_id
WHERE
metric_date >= getdate() - interval '9 months' AND from_address_tag <> 'Other'
GROUP BY 1,2,3
ORDER BY 1 DESC
Run a query to Download Data