'Flow' as network,

date(dte) as daily,

count(*) as sales_count,

count(distinct buyer) as buyers_count,

sum(usd_amount) as sales_volume,

count(distinct dte) as days_count,

count(distinct marketplace) as marketplaces_count,

(sales_count/buyers_count) as avg_sales_per_buyer,

(sales_volume/buyers_count) as avg_sales_amount_per_buyer,

(sales_volume/sales_count) as avg_sales_amount_per_sale

from ((with sale as (select

date(BLOCK_TIMESTAMP) as dte,

HOUR(block_timestamp) as time_hour,

minute(block_timestamp) as time_minute,

TX_ID,

BUYER,

NFT_COLLECTION,

MARKETPLACE,

PRICE

from flow.core.ez_nft_sales

where currency = 'A.1654653399040a61.FlowToken'),

price as (select date(TIMESTAMP) as dte1, hour(TIMESTAMP) as hour1,minute(TIMESTAMP) as time_minute1, PRICE_USD as flow_price

from flow.core.fact_prices

where TOKEN_CONTRACT = 'A.1654653399040a61.FlowToken')

select

dte,

TX_ID,

BUYER,

NFT_COLLECTION,

MARKETPLACE,

PRICE,

(PRICE*flow_price) as usd_amount

from sale

inner join price on dte1=dte and time_hour=hour1 and time_minute=time_minute1)