with price as ( select trunc(TIMESTAMP,'week') as weekly_price , avg(price_usd) as price

from flow.core.fact_prices

where SYMBOL = 'FLOW'

group by weekly_price)

,

t2 as (

select trunc(block_timestamp,'week') as weekly , count(DISTINCT BUYER) as minter ,count(DISTINCT seller) as seller, count(DISTINCT tx_id) as transactions

,sum(price) as total_amount

from flow.core.fact_nft_sales

where marketplace = 'A.c1e4f4f4c4257510.TopShotMarketV3'

and TX_SUCCEEDED = 'TRUE'

group by 1

select weekly, minter , seller, total_amount , price , total_amount*price as total_volume, transactions

from t2 x left outer join price y on x.weekly = y.weekly_price