with token_prices as (
select
TIMESTAMP::date as day,
TOKEN_CONTRACT,
avg(PRICE_USD) as price_usd
from
flow.core.fact_prices
where
TIMESTAMP >= '2022-01-01'
group by
day, TOKEN_CONTRACT
), flow_daily_price as (
select
timestamp::date as DAY
, avg (price_usd) as flow_price
from
flow.core.fact_prices
where
token_contract = 'A.1654653399040a61.FlowToken'
and TIMESTAMP >= '2022-01-01'
group by day
) , nft_sales as (
select
nft_collection,
split(nft_collection,'.')[2] as name,
date(block_timestamp) as day,
tx_id,
price
-- count(distinct TX_ID) over(partition by day, nft_collection) as transactions_count,
-- sum(PRICE*PRICE_USD) over(partition by nft_collection, day) as volume
-- sum(PRICE) over(partition by nft_collection, day) as volume_flow
from
flow.core.fact_nft_sales -- a join token_prices b on a.currency=b.TOKEN_CONTRACT and date(a.block_timestamp)=b.day
where block_timestamp >= '2022-01-01'