DiamondWeekly Comparison copy
    Updated 2024-03-15
    -- forked from Masi / Weekly Comparison @ https://flipsidecrypto.xyz/Masi/q/V2ickNzI8HEv/weekly-comparison

    with tb1 as ( select trunc(RECORDED_HOUR,'day') as day,
    avg(open) as avg_price
    from flow.price.fact_hourly_prices
    where token = 'Flow'
    group by 1
    )
    ,
    tb2 as ( select tx_id,
    trunc(block_timestamp,'day') as day,
    marketplace,
    case when marketplace = 'A.85b075e08d13f697.OlympicPinMarket' then 'Olympic Pin'
    when marketplace = 'A.921ea449dffec68a.FlovatarMarketplace' then 'Flovatar'
    when marketplace = 'A.a49cc0ee46c54bfb.MotoGPNFTStorefront' then 'MotoGP'
    when marketplace = 'A.097bafa4e0b48eef.FindMarketSale' then 'Find Market'
    when marketplace = 'A.62b3063fbe672fc8.ZeedzMarketplace' then 'Zeedz'
    when marketplace = 'A.64f83c60989ce555.ChainmonstersMarketplace' then 'Chainmonsters' end as market_name,
    buyer,
    currency,
    case when currency ilike '%flow%' then price*avg_price else price end as volume
    from flow.nft.ez_nft_sales a join tb1 b on a.block_timestamp::date = b.day
    where TX_SUCCEEDED = 'true'
    and marketplace in ('A.85b075e08d13f697.OlympicPinMarket','A.a49cc0ee46c54bfb.MotoGPNFTStorefront',
    'A.921ea449dffec68a.FlovatarMarketplace','A.62b3063fbe672fc8.ZeedzMarketplace',
    'A.097bafa4e0b48eef.FindMarketSale','A.64f83c60989ce555.ChainmonstersMarketplace'))
    ,
    -- Credited to Adria
    tb3 as ( select DISTINCT tx_id as hash,
    block_timestamp
    from flow.core.fact_events
    where event_type = 'ListingCompleted'
    and event_data:customID = 'flowverse-nft-marketplace'
    ),

    tb4 as (select DISTINCT tx_id
    QueryRunArchived: QueryRun has been archived