ddccnft_vols
    Updated 2022-11-27
    with NFT_tx as (select tx_id, MAX(contract_address) as nft_address
    from ethereum.udm_events t0
    where block_timestamp::date > '2022-01-01'
    and origin_function_name = 'atomicMatch_'
    and event_type = 'erc20_transfer'
    and contract_address in (select contract_address FROM ethereum.nft_events GROUP BY 1)
    GROUP BY 1
    ),

    TRANSFERTS as
    ( select block_timestamp, tx_id, symbol, amount
    from ethereum.udm_events as event_tab
    where block_timestamp::date > '2022-01-01'
    and tx_id in (select tx_id
    from ethereum.udm_events
    where block_timestamp::date > '2022-01-01'
    and origin_function_name = 'atomicMatch_'
    and tx_id is not NULL
    and contract_address in (select contract_address FROM ethereum.nft_events GROUP BY 1)
    GROUP BY 1
    )
    and to_label_subtype = 'marketplace'
    and symbol = 'ETH')
    SELECT date_trunc('week', block_timestamp) AS time_bucket, nft_tab.nft_address, sum(amount), MEDIAN(amount), avg(amount), count(T.tx_id)
    FROM TRANSFERTS as T
    LEFT JOIN NFT_tx as nft_tab ON T.tx_id = nft_tab.tx_id
    GROUP BY 1,2
    ORDER BY 2, 1 DESC