A as(

select 'ethereum' as chain, block_timestamp, event_type, platform_name, price_usd as volume from ethereum.nft.ez_nft_sales union all

select 'arbitrum' as chain, block_timestamp, event_type, platform_name, price_usd as volume from arbitrum.nft.ez_nft_sales union all

select 'optimism' as chain, block_timestamp, event_type, platform_name, price_usd as volume from optimism.nft.ez_nft_sales union all

select 'base' as chain, block_timestamp, event_type, platform_name, price_usd as volume from base.nft.ez_nft_sales union all

select 'avalanche' as chain, block_timestamp, event_type, platform_name, price_usd as volume from avalanche.nft.ez_nft_sales union all

select 'bsc' as chain, block_timestamp, event_type, platform_name, price_usd as volume from bsc.nft.ez_nft_sales union all

select 'polygon' as chain, block_timestamp, event_type, platform_name, price_usd as volume from polygon.nft.ez_nft_sales

),

B as( select chain, platform_name,

sum(case when date(block_timestamp) BETWEEN '2022-12-18' and '2023-12-18' then volume else 0 end) as volume_2023,

sum(case when date(block_timestamp) BETWEEN '2021-12-18' and '2022-12-18' then volume else 0 end) as volume_2022

from A

group by 1,2),

SS as (

select date(RECORDED_HOUR) as day, symbol, avg(close) as price

from solana.price.fact_token_prices_hourly

where SYMBOL = 'SOL'

group by 1,2),

SA as(

select 'solana' as chain,

case when lower(marketplace) like '%magic eden%' then 'Magic Eden'

when lower(marketplace) like '%solana monkey business marketplace%' then 'SMB'

else marketplace end as platform_name, date(block_timestamp) as day,

sum(sales_amount*price) as volume

from solana.nft.fact_nft_sales as a

join SS as b on date(a.block_timestamp) = b.day

where SUCCEEDED = TRUE