flipside

    Overtime
    HosseinOvertime
    Updated 2024-01-31
    WITH PRICES AS (
    SELECT
    TRUNC(hour, 'DAY') as DATE_TIME,
    symbol,
    AVG(PRICE) AS PRICE
    FROM crosschain.price.ez_hourly_token_prices
    WHERE symbol like Any('WETH', 'MATIC', 'BNB')
    GROUP BY 1, 2
    )

    SELECT
    TRUNC(t1.block_timestamp, 'WEEK') as WEEKLY,
    CASE
    when platform ilike '%uniswap%' then 'uniswap'
    when platform ilike '%sushiswap%' then 'sushiswap'
    when platform ilike '%curve%' then 'curve'
    when platform ilike '%hashflow%' then 'hashflow'
    when platform ilike '%dodo%' then 'dodo'
    END AS SWAP_PROGRAM,
    COUNT(DISTINCT t1.tx_hash) as TRANSACTIONS,
    COUNT(DISTINCT origin_from_address) as TOTAL_USERS,
    SUM(amount_in_usd) as VOLUME_USD,
    AVG(amount_in_usd) as VOLUME_USD_AVG,
    SUM(tx_fee * PRICES.PRICE) as FEE_USD,
    AVG(tx_fee * PRICES.PRICE) as FEE_USD_AVG,
    SUM(VOLUME_USD) OVER (PARTITION BY SWAP_PROGRAM ORDER BY WEEKLY) AS VOLUME_CUM
    FROM ethereum.defi.ez_dex_swaps t1
    JOIN ethereum.core.fact_transactions t2
    ON t1.tx_hash = t2.tx_hash
    JOIN PRICES ON TRUNC(t1.block_timestamp, 'DAY') = DATE_TIME AND symbol = 'WETH'
    WHERE platform ilike Any ('%uniswap%', '%sushiswap%', '%curve%', '%hashflow%', '%dodo%')
    AND status ilike 'success'
    AND t1.block_timestamp >= '2023-01-01'
    AND SWAP_PROGRAM NOT ilike 'balancer'
    GROUP BY SWAP_PROGRAM, WEEKLY
    ORDER BY SWAP_PROGRAM, WEEKLY
    Last run: about 1 year ago
    WEEKLY
    SWAP_PROGRAM
    TRANSACTIONS
    TOTAL_USERS
    VOLUME_USD
    VOLUME_USD_AVG
    FEE_USD
    FEE_USD_AVG
    VOLUME_CUM
    1
    		2022-12-26 00:00:00.000curve102357776612439.2164003.708613214956.23448480411.99377264276612439.21
    2
    		2023-01-02 00:00:00.000curve876130081370573434.15140730.40703871143447.25413570314.218183581447185873.36
    3
    		2023-01-09 00:00:00.000curve1603145122277744950.14127305.217423429394958.12550649321.3064749153724930823.5
    4
    		2023-01-16 00:00:00.000curve2230654062177705527.5786595.575297042550201.00912358520.9464731085902636351.07
    5
    		2023-01-23 00:00:00.000curve2019645552074953799.2293571.760956933493473.55110613921.4116176127977590150.29
    6
    		2023-01-30 00:00:00.000curve1595745391243299427.8870851.346471393467310.10560610125.3945280739220889578.17
    7
    		2023-02-06 00:00:00.000curve1787441031480866859.2176836.344067348527326.90302902726.08463113510701756437.38
    8
    		2023-02-13 00:00:00.000curve2007043121982443621.9590180.758856844644386.87458310528.8897948712684200059.33
    9
    		2023-02-20 00:00:00.000curve142793527814223235.1252106.952202739461068.58452140628.71627955413498423294.45
    10
    		2023-02-27 00:00:00.000curve134633424830560643.5855975.242187626365936.14039344624.20372646314328983938.03
    11
    		2023-03-06 00:00:00.000curve529791152111385008201.53181599.3524241944044266.082620463.76856376625713992139.56
    12
    		2023-03-13 00:00:00.000curve3293761154796256156.95128755.0980362941190491.7442059831.66117242130510248296.51
    13
    		2023-03-20 00:00:00.000curve1732339231535744005.2578374.279420771493724.83325993624.88657862132045992301.76
    14
    		2023-03-27 00:00:00.000curve104113016937992615.7181295.945199341348785.14967707229.64599657332983984917.47
    15
    		2023-04-03 00:00:00.000curve111082945778450577.9864054.190568584389346.01962366831.01123214833762435495.45
    16
    		2023-04-10 00:00:00.000curve118213305982303483.6476088.573481022449526.64217942633.88306641934744738979.09
    17
    		2023-04-17 00:00:00.000curve80712612876129186.6799481.002233451494186.73456471254.77574091835620868165.76
    18
    		2023-04-24 00:00:00.000curve83672479913554829.2299995.055737741415048.22399041144.32855110436534422994.98
    19
    		2023-05-01 00:00:00.000curve52811757696339390.43121567.631010824553647.07007715394.15766497937230762385.41
    20
    		2023-05-08 00:00:00.000curve59232136758729580.06117888.374776259518849.43062006378.38788799237989491965.47
    ...
    290
    34KB
    303s