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

pedro