Alexaycollections
    Updated 2022-10-24
    with flow_price as (select TIMESTAMP::date as p_date, avg (price_usd) as flow_price
    from flow.core.fact_prices
    where symbol ='FLOW' and source ='coinmarketcap'
    group by 1),
    nft_price as ( SELECT*, price * b.flow_price as nft_usd_price
    from flow.core.ez_nft_sales a join flow_price b on a.block_timestamp::date = b.p_date ),
    raceday_buyer as (select block_timestamp, tx_id, buyer, nft_collection, nft_usd_price
    from nft_price
    where nft_collection = 'A.329feb3ab062d289.RaceDay_NFT'
    and tx_succeeded = 'TRUE'),
    other_collec_buyer as ( select date_trunc('week', block_timestamp) as date,
    split(nft_collection, '.')[2] as collection,
    COUNT(DISTINCT tx_id) sales_Cnt,
    COUNT(DISTINCT buyer) buyer_Cnt,
    sum(nft_usd_price) as sales_usd_vol,
    row_number() over (partition by sales_Cnt order by date DESC) as rank
    from nft_price a JOIN raceday_buyer b USING(buyer)
    where nft_collection != 'A.329feb3ab062d289.RaceDay_NFT'
    and tx_succeeded = 'TRUE'
    and a.block_timestamp > b.block_timestamp
    group by 1,2
    qualify rank <= 10)
    SELECT date_trunc('week', block_timestamp) as date,
    split(nft_collection, '.')[2] as collection,
    COUNT(DISTINCT tx_id) sales_Cnt,
    COUNT(DISTINCT buyer) buyer_Cnt,
    sum(nft_usd_price) as sales_usd_vol,
    row_number() over (order by date DESC) as rank
    FROM raceday_buyer
    group by 1, 2

    UNION
    Run a query to Download Data