logx_tradeMarketdominance
    Updated 2023-06-24
    -- forked from m-zamani-WmWD3E / Top 50 sales by volume @ https://flipsidecrypto.xyz/m-zamani-WmWD3E/q/kGDcKgzZu0ML/top-50-sales-by-volume

    with p_eth as (select
    HOUR::date as day,
    avg(price) as token_price
    from ethereum.core.fact_hourly_token_prices
    where SYMBOL = 'WETH'
    group by 1
    ),

    decoded_sale as (select
    s.BLOCK_TIMESTAMP,
    s.tx_hash,
    s.CONTRACT_ADDRESS,
    DECODED_LOG:from as seller,
    DECODED_LOG:to as buyer,
    DECODED_LOG:tokenId as TOKENID,
    ETH_value as amount,
    ETH_value*token_price as amount_usd
    from arbitrum.core.fact_decoded_event_logs s
    left join arbitrum.core.fact_transactions a on s.tx_hash=a.tx_hash
    left join p_eth p on s.BLOCK_TIMESTAMP::date=p.day
    where s.EVENT_NAME = 'Transfer'
    and DECODED_LOG:from != '0x0000000000000000000000000000000000000000'
    and ETH_value>0.001
    and a.STATUS='SUCCESS'
    )

    select
    date_trunc('week',BLOCK_TIMESTAMP) as date,
    case
    when contract_address = '0x17f4baa9d35ee54ffbcb2608e20786473c7aa49f' then 'GMX Blueberry Club'
    when contract_address = '0xede855ced3e5a59aaa267abdddb0db21ccfe5072' then 'Diamond Pepes'
    when contract_address = '0x4ee9fe9500e7c4fe849add9b14beec5ec5b7d955' then 'Dopex Bridgoor NFT'
    when contract_address = '0xfae39ec09730ca0f14262a636d2d7c5539353752' then 'Arbitrum Odyssey NFT'
    when contract_address = '0x642ffab2752df3bce97083709f36080fb1482c80' then 'Government Toucans'
    Run a query to Download Data