collection,

USD_volume,

trxs,

buyers,

day_of_activation

from (select a.day,

ifnull(collection_name1, b.collection_name2) as collection,

ifnull(usd_volume,0) + ifnull(b.flow_usd_volume,0) as USD_volume,

ifnull(tx_count1,0) + ifnull(tx_count2,0) as trxs,

ifnull(buyer_count1,0) + ifnull(buyer_count2,0) as buyers,

ifnull(day_count1, day_count2) as day_of_activation--credit to rayk

from (select date_trunc('day', block_timestamp) as day,

case when nft_collection = 'A.87ca73a41bb50ad5.Golazos' then 'LaLiga Golazos'

when nft_collection = 'A.0b2a3299cc857e29.TopShot' then 'NBA TopShot'

when nft_collection = 'A.e4cf4bdc1751c65d.AllDay' then 'NFL AllDay'

end as collection_name1,

sum(price) as usd_volume,

avg(price) as avg_volume1,

count(distinct(tx_id)) as tx_count1,

count(distinct(buyer)) as buyer_count1,

row_number () over (partition by collection_name1 order by day) as day_count1

from flow.core.ez_nft_sales

where tx_succeeded = 'TRUE'

and nft_collection in ('A.87ca73a41bb50ad5.Golazos', 'A.0b2a3299cc857e29.TopShot', 'A.e4cf4bdc1751c65d.AllDay')

and not currency = 'A.1654653399040a61.FlowToken'

group by 1,2) a left join (select date_trunc('day', block_timestamp) as day,

case when nft_collection = 'A.87ca73a41bb50ad5.Golazos' then 'LaLiga Golazos'

when nft_collection = 'A.0b2a3299cc857e29.TopShot' then 'NBA TopShot'

when nft_collection = 'A.e4cf4bdc1751c65d.AllDay' then 'NFL AllDay'

end as collection_name2,

sum(price)*avg(price_usd) as flow_usd_volume,

avg(price)*avg(price_usd) as avg_volume2,

count(distinct(tx_id)) as tx_count2,

count(distinct(buyer)) as buyer_count2,

row_number () over (partition by collection_name2 order by day) as day_count2