WITH main_query as (
SELECT
play_type,
COUNT(DISTINCT TX_ID) as transactions_number,
SUM(PRICE) as volume
FROM
flow.core.dim_topshot_metadata
JOIN
flow.core.fact_nft_sales
ON
flow.core.fact_nft_sales.NFT_ID = flow.core.dim_topshot_metadata.NFT_ID
GROUP BY play_type ),
avg_query_volume as (
SELECT
avg(volume) as avg_volume
FROM
main_query),
avg_query_transaction as (
SELECT
avg(transactions_number) as avg_transaction
FROM
main_query)
SELECT
*,avg_volume,avg_transaction
FROM
main_query , avg_query_volume , avg_query_transaction