select

timestamp::date as day,

avg(price_usd) as average_price_usd

from flow.core.fact_prices

where token_contract = 'A.1654653399040a61.FlowToken'

group by day

),

initial_data_two as

select

fns.block_timestamp as block_timestamp,

fns.nft_id as nft_id,

id1.average_price_usd * fns.price as total_price

from flow.core.fact_nft_sales fns

left join initial_data_one id1

on id1.day = fns.block_timestamp::date

where nft_collection = 'A.0b2a3299cc857e29.TopShot'

and currency = 'A.1654653399040a61.FlowToken'

and tx_succeeded = 'true'

UNION

select

block_timestamp,

nft_id,

price

from flow.core.fact_nft_sales

where nft_collection = 'A.0b2a3299cc857e29.TopShot'

and currency = 'A.ead892083b3e2c6c.DapperUtilityCoin'

and tx_succeeded = 'true'

id2.block_timestamp::date as day,

dtm.play_type as play_type,

sum(id2.total_price) as volume

from flow.core.dim_topshot_metadata dtm