with sales as ( select trunc(block_timestamp,'hour') as hourly, tx_hash, seller_address, buyer_address, platform_name, nft_address, token_id, price from near.nft.ez_nft_sales) , price as ( select hour, price as avg_price from near.price.ez_prices_hourly where symbol = 'NEAR') , volume as ( select hourly, tx_hash, seller_address, buyer_address, platform_name, nft_address, token_id, price, price*avg_price as price_usd, round(price_usd) as "Price (USD)" from sales a join price b on a.hourly = b.hour) , --UWON Credited to MASI uwon as (select DISTINCT tx_hash, from near.core.fact_actions_events_function_call where method_name = 'ft_on_transfer' and receiver_id = 'uwon.hot.tg' and block_timestamp::date >= '2024-05-01' ) , uwon_1 as ( select block_timestamp, a.tx_hash, 'uwon' as platform_name, contract_address as nft_address, from_address as seller_address, to_address as buyer_address, token_id from near.nft.fact_nft_transfers a join uwon b on a.tx_hash = b.tx_hash where block_timestamp::date >= '2024-05-01' and to_address != 'uwon.hot.tg' and from_address != 'uwon.hot.tg') , uwon_price as ( select hour, symbol, avg(price) as price from crosschain.price.ez_prices_hourly where symbol = 'UWON' group by 1,2) , uwon_2 as ( select trunc(a.block_timestamp,'hour') as hourly, a.tx_hash, nft_address, platform_name, seller_address, buyer_address, token_id, AMOUNT_RAW/pow(10,18) as amount, amount*price as price_usd from uwon_1 a join near.core.ez_token_transfers b on a.tx_hash = b.tx_hash left outer join uwon_price c on date_trunc('hour',a.block_timestamp) = c.hour where TRANSFER_TYPE = 'nep141' and TO_ADDRESS = 'uwon.hot.tg' and a.block_timestamp::date >= '2024-05-01') , uwon_near as ( select hour, symbol, avg(price) as avg_price from crosschain.price.ez_prices_hourly where symbol = 'WNEAR' group by 1,2) , uwon_3 as ( select a.*, price_usd/avg_price as price from uwon_2 a join uwon_near b on a.hourly = b.hour) , fnl as ( select hourly, tx_hash, seller_address, buyer_address, platform_name, nft_address, token_id, price, price_usd from volume UNION all select hourly, tx_hash, seller_address, buyer_address, platform_name, nft_address, token_id, price, price_usd from uwon_3) select trunc(hourly,'month') as monthly, case when hourly::date >= '2022-01-01' and hourly::date < '2022-04-01' then 'Q1-2022' when hourly::date >= '2022-04-01' and hourly::date < '2022-07-01' then 'Q2-2022' when hourly::date >= '2022-07-01' and hourly::date < '2022-10-01' then 'Q3-2022' when hourly::date >= '2022-10-01' and hourly::date < '2023-01-01' then 'Q4-2022' when hourly::date >= '2023-01-01' and hourly::date < '2023-04-01' then 'Q1-2023' when hourly::date >= '2023-04-01' and hourly::date < '2023-07-01' then 'Q2-2023' when hourly::date >= '2023-07-01' and hourly::date < '2023-10-01' then 'Q3-2023' when hourly::date >= '2023-10-01' and hourly::date < '2024-01-01' then 'Q4-2023' when hourly::date >= '2024-01-01' and hourly::date < '2024-04-01' then 'Q1-2024' when hourly::date >= '2024-04-01' and hourly::date < '2024-07-01' then 'Q2-2024' when hourly::date >= '2024-07-01' and hourly::date < '2024-10-01' then 'Q3-2024' when hourly::date >= '2024-10-01' and hourly::date < '2025-01-01' then 'Q4-2024' end as type, count(DISTINCT buyer_address) as buyers, count(*) as sales, count(DISTINCT tx_hash) as transactiosn, sum(sales) over (partition by type order by monthly asc) as cum_sales, count(DISTINCT seller_address) as sellers, sum(price_usd) as volume_usd, sum(price) as volume_near, avg(price_usd) as avg_price_usd, median(price_usd) as median_price_usd, max(price_usd) as max_price_usd, sales/buyers as "Avg NFT Per Buyer", volume_usd/buyers as "Avg Volume (USD) per Buyer" from fnl where hourly::date >= '2022-01-01' group by 1,2

NFTs on Near!