split(nft_collection, '.')[2] as collection1,

sum(price) as usd_volume,

avg(price) as avg_volume1,

count(distinct(tx_id)) as tx_count1

from flow.core.ez_nft_sales

where tx_succeeded = 'TRUE'

and block_timestamp >= '2022-01-01'

and nft_collection in ('A.329feb3ab062d289.RaceDay_NFT', 'A.1d007eed492fdbbe.OlympicPin', 'A.81e95660ab5308e1.TFCItems') --Raceday NFT, nWayPlay, The Football Club

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

group by 1,2),

table_2 as (select date_trunc('{{Periodical}}', block_timestamp) as day,

split(nft_collection, '.')[2] as collection2,

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

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

count(distinct(tx_id)) as tx_count2

from flow.core.ez_nft_sales a

join flow.core.fact_prices c on a.block_timestamp::date = c.timestamp::date

where tx_succeeded = 'TRUE'

and block_timestamp >= '2022-01-01'

and nft_collection in ('A.329feb3ab062d289.RaceDay_NFT', 'A.1d007eed492fdbbe.OlympicPin', 'A.81e95660ab5308e1.TFCItems') --Raceday NFT, nWayPlay, The Football Club

and currency = 'A.1654653399040a61.FlowToken'

and token_contract = 'A.1654653399040a61.FlowToken'

group by 1,2),

table_3 as (select a.{{Periodical}},

ifnull(collection1, b.collection2) as collection,

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

sum(volume_usd) over (partition by collection order by a.{{Periodical}}) as cumu_volume_usd,

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

sum(tx_count) over (partition by collection order by a.{{Periodical}}) as cumu_tx_count

from table_1 a

left join table_2 b on a.day = b.day and a.collection1 = b.collection2),

table_4 as (select {{Periodical}},