with flow_price as ( select date_trunc('day', timestamp) as day, avg(price_usd) as flow_price from flow.price.fact_prices where token_contract = 'A.1654653399040a61.FlowToken' group by 1 ), revv_price as ( select date_trunc('day', timestamp) as day, avg(price_usd) as revv_price from flow.price.fact_prices where token_contract = 'A.d01e482eb680ec9f.REVV' group by 1 ), flow_usd as ( select case when currency = 'A.d01e482eb680ec9f.REVV' then price * revv_price when currency in ('A.ead892083b3e2c6c.FlowUtilityToken', 'A.1654653399040a61.FlowToken') then price * flow_price when currency in ('A.3c5959b568896393.FUSD', 'A.ead892083b3e2c6c.DapperUtilityCoin', 'A.b19436aae4d94622.FiatToken') then price else null end as volume_usd, * from flow.nft.ez_nft_sales join flow_price f on block_timestamp::date = f.day join revv_price r on block_timestamp::date = r.day where tx_succeeded = true and volume_usd is not null ), TX AS ( SELECT blocK_timestamp, tx_hash, tx:receipt as receipt, tx:public_key as public_key, tx:signer_id as signer_id, tx:receiver_id as receiver_id FROM near.core.fact_transactions where block_timestamp::date < CURRENT_DATE ), JSON_PARSING AS ( SELECT block_timestamp, tx_hash, public_key, signer_id, receiver_id, seq,key,path,index, replace(value:outcome:logs[0], '\\') as logs, -- remove // | convert variant | parse json check_json(logs) as checks FROM tx, table(flatten(input => receipt)) ), nft_tx_log AS ( SELECT --PARSE_JSON(LOGS):PARAMS block_timestamp, tx_hash, public_key, signer_id, receiver_id, try_parse_json(logs) as parse_logs, parse_logs:type as type, parse_logs:params:buyer_id as buyer_id, parse_logs:params:owner_id as owner_id, parse_logs:params:is_offer as is_offer, parse_logs:params:is_auction as is_auction, parse_logs:params:nft_contract_id as nft_contract_id, parse_logs:params:token_id as token_id, parse_logs:params:ft_token_id as ft_token_id, parse_logs:params:price/pow(10,24) as near FROM JSON_PARSING WHERE checks is null and logs is not null -- filter out json_parse AND type is not null ), near_prise as ( select date_trunc('day', timestamp) as day, avg(price_usd) as near_usd from near.price.fact_prices where symbol = 'wNEAR' and day >= '{{start_date}}' and day <= '{{end_date}}' group by 1 ), sol_prise as ( select date_trunc('day', hour) as day, avg(price) as sol_usd from ethereum.price.ez_hourly_token_prices where token_address = '0xd31a59c85ae9d8edefec411d448f90841571b89c' and day >= '{{start_date}}' and day <= '{{end_date}}' group by 1 ), algo_price as ( select date_trunc('day', block_hour) as day, avg(price_usd) as algo_usd from algorand.defi.ez_price_pool_balances where asset_name = 'ALGO' and day >= '{{start_date}}' and day <= '{{end_date}}' group by 1 ), algo_nfts as ( select block_timestamp, tx_group_id, purchaser, nft_asset_id, 'FIFA collection' as collection_name, number_of_nfts, total_sales_amount_usd from algorand.nft.ez_nft_sales_fifa where block_timestamp::date >= '{{start_date}}' and block_timestamp::date <= '{{end_date}}' union all select block_timestamp, tx_group_id, purchaser, nft_asset_id, collection_name, number_of_nfts, total_sales_amount * algo_usd as total_sales_amount_usd from algorand.nft.ez_nft_sales join algo_price on block_timestamp::date = day where block_timestamp::date >= '{{start_date}}' and block_timestamp::date <= '{{end_date}}' ) select date_trunc('day', block_timestamp) as date, 'Ethereum' as blockchain, sum(price_usd) as sales_volume, avg(price_usd) as avg_sales_price, count(distinct buyer_address) as unique_buyer, -- count(distinct seller_address) as unique_seller, count(distinct tx_hash) as tx_count, sum(sales_volume) over (order by date) as cum_sales_volume, sum(tx_count) over (order by date) as cum_tx_count from ethereum.nft.ez_nft_sales where event_type = 'sale' and price_usd is not null and date >= '{{start_date}}' and date <= '{{end_date}}' group by 1 union all select date_trunc('day', block_timestamp) as date, 'Optimism' as blockchain, sum(price_usd) as sales_volume, avg(price_usd) as avg_sales_price, count(distinct buyer_address) as unique_buyer, -- count(distinct seller_address) as unique_seller, count(distinct tx_hash) as tx_count, sum(sales_volume) over (order by date) as cum_sales_volume, sum(tx_count) over (order by date) as cum_tx_count from optimism.nft.ez_nft_sales where event_type = 'sale' and price_usd is not null and date >= '{{start_date}}' and date <= '{{end_date}}' group by 1 union all select date_trunc('day', block_timestamp) as date, 'Flow' as blockchain, sum(volume_usd) as sales_volume, avg(volume_usd) as avg_sales_price, count(distinct buyer) as unique_buyer, -- count(distinct seller) as unique_seller, count(distinct tx_id) as tx_count, sum(sales_volume) over (order by date) as cum_sales_volume, sum(tx_count) over (order by date) as cum_tx_count from flow_usd where date >= '{{start_date}}' and date <= '{{end_date}}' group by 1 union all SELECT date_trunc('day', block_timestamp) as date, 'Near' as blockchain, sum(near * near_usd) as sales_volume, avg(near * near_usd) as avg_sales_price, count(distinct buyer_id) as unique_buyer, -- count(distinct owner_id) as unique_seller, count(distinct tx_hash) as tx_count, sum(sales_volume) over (order by date) as cum_sales_volume, sum(tx_count) over (order by date) as cum_tx_count FROM nft_tx_log l join near_prise n on block_timestamp::date = day where type = 'resolve_purchase' and date >= '{{start_date}}' and date <= '{{end_date}}' group by 1 union all select date_trunc('day', block_timestamp) as date, 'Solana' as blockchain, sum(sales_amount * sol_usd) as sales_volume, avg(sales_amount * sol_usd) as avg_sales_price, count(distinct purchaser) as unique_buyer, -- count(distinct seller) as unique_seller, count(distinct tx_id) as tx_count, sum(sales_volume) over (order by date) as cum_sales_volume, sum(tx_count) over (order by date) as cum_tx_count from solana.nft.fact_nft_sales s join sol_prise p on block_timestamp::date = day where succeeded = true and date >= '{{start_date}}' and date <= '{{end_date}}' group by 1 union all select date_trunc('day', block_timestamp) as date, 'Algorand' as blockchain, sum(total_sales_amount_usd) as sales_volume, avg(total_sales_amount_usd) as avg_sales_price, count(distinct purchaser) as unique_buyer, -- count(distinct seller) as unique_seller, sum(number_of_nfts) as tx_count, sum(sales_volume) over (order by date) as cum_sales_volume, sum(tx_count) over (order by date) as cum_tx_count from algo_nfts where date >= '{{start_date}}' and date <= '{{end_date}}' group by 1

Query	Creator	Created
Weekly Active Fee Payers ethereum solana evm	pedro	Jan 26, 2023
Weekly Active Fee Payers copy ethereum solana evm	pedro	Jun 16, 2023
daily sales volume, sales count, and buyers flow near prices price nft	pedro	Nov 9, 2023

pedro