SELECT date_trunc('day', timestamp) as date ,

AVG(price_usd) AS Flow_price

FROM flow.core.fact_prices

WHERE token_contract = 'A.1654653399040a61.FlowToken'

AND source = 'coinmarketcap'

GROUP BY 1

),

seles AS (

SELECT date_trunc('day',block_timestamp) AS date1,

COUNT(DISTINCT tx_id)AS sale_count ,

COUNT(DISTINCT buyer) AS unique_buyers ,

COUNT(DISTINCT seller) AS unique_sellers ,

sum(

CASE WHEN currency = 'A.1654653399040a61.FlowToken' THEN price * Flow_price

ELSE price * 1 END

) AS volume ,

min(

CASE WHEN currency = 'A.1654653399040a61.FlowToken' THEN price * Flow_price

ELSE price * 1 END

) AS Floor_price,

median(

CASE WHEN currency = 'A.1654653399040a61.FlowToken' THEN price * Flow_price

ELSE price * 1 END

) AS Median_volume,

sum(sale_count) over (ORDER BY date1) AS cumu_tx,

sum(unique_buyers) over (ORDER BY date1) AS cumu_buyer,

sum(volume) over (ORDER BY date1) AS cumu_volume,

sum(unique_sellers) over (ORDER BY date1) AS cumu_seller,

sale_count/datediff(day,'2022-02-22' , current_date-1) AS average_sale_day,

unique_buyers/datediff(day,'2022-02-22' , current_date-1) AS average_unique_buyers_day,

unique_sellers/datediff(day,'2022-02-22' , current_date-1) AS average_unique_sellers_day,

volume/datediff(day,'2022-02-22' , current_date-1) AS average_volume_day,

volume/sale_count AS "average selling price (ASP) ",

avg(