cloudr3nopti dex aggregator curve 3pool
    Updated 2023-06-12
    -- 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