split_part(nft_collection , '.' , 3) as collection_name,

sum(price) as sales,

avg(price) as sales_price,

count(distinct tx_id) as tx_count

from flow.core.ez_nft_sales

where currency not ilike 'A.1654653399040a61.FlowToken' and

price is not null and

block_timestamp::date >= '{{start_date}}' and

tx_succeeded = true

group by 1

union all

select

split_part(nft_collection , '.' , 3) as collection_name,

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

avg(price) as sales_price,

count(distinct tx_id) as tx_count

from flow.core.ez_nft_sales join flow.core.fact_prices on block_timestamp::date = timestamp::date

where currency ilike 'A.1654653399040a61.FlowToken' and

price is not null and

token = 'Flow' and

block_timestamp::date >= '{{start_date}}' and

tx_succeeded = true

group by 1

),

TX AS (

blocK_timestamp,

tx_hash,

tx:receipt as receipt,

tx:public_key as public_key,

tx:signer_id as signer_id,