aabandontemporal_query_v2
    Updated 2024-04-23
    with tab1 as (SELECT
    *
    FROM
    ethereum.core.ez_token_transfers
    order by tx_hash
    limit 100
    ),
    tab3 as (
    SELECT
    *
    ,case when token_out in ('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48', '0xdac17f958d2ee523a2206206994597c13d831ec7') or token_in in ('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48', '0xdac17f958d2ee523a2206206994597c13d831ec7') then 0 else 1
    end as stable_coin
    -- ,CASE
    -- when amount_in_usd is null then 1 else 0
    -- end as usd_valid
    ,row_number() over (partition by tx_hash order by
    -- usd_valid desc,
    - amount_in_usd desc
    , stable_coin) as tx_order

    -- tx_hash) as row_num,
    -- (case
    -- when (token_in = lower('{{token_address}}') and amount_in_usd is not null) or (token_out = lower('{{token_address}}') and amount_out_usd is not null) then row_num
    -- when (token_in = lower('{{token_address}}') and token_out in ('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48', '0xdac17f958d2ee523a2206206994597c13d831ec7')) or (token_out = lower('{{token_address}}') and token_in in ('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48', '0xdac17f958d2ee523a2206206994597c13d831ec7')) then 100 + row_num
    -- else 200
    -- end) as tx_order

    FROM
    ethereum.defi.ez_dex_swaps
    where
    -- tx_hash in valid_hash and
    block_timestamp > '2024-04-13' and
    (token_in = lower('{{token_address}}') or token_out = lower('{{token_address}}'))
    -- and amount_in_usd is null
    order by tx_hash
    )
    QueryRunArchived: QueryRun has been archived