flipside

    Small OP
    bergSmall OP
    Updated 2024-07-31
    with tab1 as (
    select
    block_timestamp::Date AS date,tx_hash,
    amount_in_usd AS amountUSD,
    origin_from_address as userAddress,
    case when date>='2023-10-17' then 'after fee changes' else 'before fee changes' end as TimePeriod
    from optimism.defi.ez_dex_swaps
    where amount_in_usd<power(10,6)
    and tx_hash in (select distinct(tx_hash) from optimism.core.ez_decoded_event_logs where origin_to_address in ('0xcb1355ff08ab38bbce60111f1bb2b784be25d7e8','0x1ba16f6bb1ca9d9b32346e9d51966a606ef21c60','0xb555edf5dcf85f42ceef1f3630a52a108e55a654'))
    and date>='2023-01-01'
    and platform = 'uniswap-v3'
    ), TopUsers AS (
    SELECT
    UserAddress,VolumeUsd
    FROM (
    SELECT
    SUM(AmountUsd) AS VolumeUsd,
    UserAddress
    FROM
    tab1
    GROUP BY UserAddress
    )
    WHERE VolumeUsd>=10000
    )

    SELECT
    DATE_TRUNC('WEEK',date) AS DATE,
    TimePeriod,
    COUNT(DISTINCT TX_HASH) AS Txns,
    COUNT(DISTINCT UserAddress) AS Users,
    SUM(AmountUsd) AS VolumeUsd,
    AVG(AmountUsd) AS AverageAmountUsd
    FROM
    tab1
    WHERE
    UserAddress NOT IN (SELECT UserAddress FROM TopUsers)
    QueryRunArchived: QueryRun has been archived