shadilProfit for AMMs
    Updated 2022-04-16
    with tbl_in_sushiswap as(
    select t3.*
    from (
    select ROW_NUMBER() over(partition by tx_id order by event_index ASC)as rownum, s1.*
    from ethereum.dex_swaps s1
    where direction='IN'
    and date(block_timestamp) BETWEEN '2022-01-01' and CURRENT_DATE - 2
    and platform = 'sushiswap'
    and amount_usd > 0

    ) t3
    where t3.rownum = 1
    ),
    -- fee for sushiswap pools = 0.0025
    sushi as (
    SELECT
    date(block_timestamp) as date, sum(amount_usd) * 0.0025 as total_amount_usd, COUNT(DISTINCT tx_id) as swap_count, avg(amount_usd) as avg_amount_usd, 'sushiswap' as type
    FROM tbl_in_sushiswap
    GROUP BY date
    ),
    tbl_in_uniswap as(
    select t3.*
    from (
    select ROW_NUMBER() over(partition by tx_id order by event_index ASC)as rownum, s1.*
    from ethereum.dex_swaps s1
    where direction='IN'
    and date(block_timestamp) BETWEEN '2022-01-01' and CURRENT_DATE - 2
    and platform like '%uniswap%'
    and amount_usd > 0
    and amount_usd < 100000000 -- there are some huge swaps amount USD!
    ) t3
    where t3.rownum = 1
    ),
    uniswap as (
    SELECT
    date(block_timestamp) as date, sum(amount_usd) * 0.003 as total_amount_usd, COUNT(DISTINCT tx_id) as swap_count, avg(amount_usd) as avg_amount_usd, 'uniswap' as type
    Run a query to Download Data