with flow_price as

(

select date_trunc('{{granularity}}',timestamp) as time,token_contract, avg(price_usd) as usd_price

from flow.core.fact_prices

group by time, token_contract

),

flow_nfts as

(

select

'flow' as chain,

date_trunc('{{granularity}}',block_timestamp) as time,

nft.tx_id as tx_hash,

ad.contract_name as marketplace,

seller,

buyer,

ad2.contract_name as project_name,

nft_id as token_id,

currency,

case when currency='A.ead892083b3e2c6c.DapperUtilityCoin' then 1*nft.price

else nft.price*p.usd_price

end as price

from flow.core.ez_nft_sales nft left join flow_price p

on (nft.currency=p.token_contract and date_trunc('{{granularity}}',block_timestamp) = p.time )

left join flow.core.dim_contract_labels ad on nft.marketplace=ad.event_contract

left join flow.core.dim_contract_labels ad2 on nft.nft_collection =ad2.event_contract

where time between '{{start_date}}' and '{{end_date}}'

-- where nft.tx_id='1e3803ea99b5b3a5795bb49ddf007449ff9a0c90256258989ae40b9f4c8d2a44'

-- limit 500

)

select * from flow_nfts

-- where tx_hash='6a76ebae62ff34956703b6f60e91c68a67a6cee2f80b4fc482dc40d18119dc4c'