cloudr3nopti dex aggregator curve 3pool
Updated 2023-06-12Copy 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
›
⌄
-- 0x30dF229cefa463e991e29D42DB0bae2e122B2AC7
with tb1 as (
select block_timestamp, tx_hash, contract_address, 'yes' as aggregator
from optimism.core.fact_event_logs
where contract_address in ('0xb7c1cca934641da45f84eda0cf06af73f4a5a523','0x1111111254fb6c44bac0bed2854e76f90643097d', '0x6352a56caadc4f1e25cd6c75970fa768a3304e64',
'0x00c0184c0b5d42fba6b7ca914b31239b419ab80b','0xdef171fe48cf0115b1d80b88dc8eab59176fee57','0xdef1abe32c034e558cdd535791643c58a13acc10')
),
swaps as (
select
date(block_timestamp) as date_time, tx_hash, origin_from_address, origin_to_address, contract_address,
case when event_inputs:bought_id='0' then event_inputs:tokens_bought*pow(10,-18)
when event_inputs:sold_id='0' then event_inputs:tokens_sold*pow(10,-18)
else 0 end as dai_traded,
case when event_inputs:bought_id='1' then event_inputs:tokens_bought::integer*pow(10,-6)
when event_inputs:sold_id='1' then event_inputs:tokens_sold::integer*pow(10,-6)
else 0 end as usdc_traded,
case when event_inputs:bought_id='2' then event_inputs:tokens_bought::integer*pow(10,-6)
when event_inputs:sold_id='2' then event_inputs:tokens_sold::integer*pow(10,-6)
else 0 end as usdt_traded,
(dai_traded + usdc_traded+ usdt_traded)/2 as vol_usd, event_inputs
from optimism.core.fact_event_logs
where 1=1 AND
contract_address=lower('0x1337BedC9D22ecbe766dF105c9623922A27963EC') and -- 3pool 0: dai 1: usdc2: usdt
event_name in ('TokenExchangeUnderlying','TokenExchange')
),
combine as (
select s.tx_hash, s.date_time, origin_from_address, origin_to_address, s.contract_address as swap_contract, t.contract_address as router_address, aggregator,vol_usd,
case when router_address='0xb7c1cca934641da45f84eda0cf06af73f4a5a523' then 'ZeroSwap'
when router_address='0x1111111254fb6c44bac0bed2854e76f90643097d' then '1inch'
when router_address='0x6352a56caadc4f1e25cd6c75970fa768a3304e64' then 'OpenOcean'
when router_address='0x00c0184c0b5d42fba6b7ca914b31239b419ab80b' then 'SlingShot'
Run a query to Download Data