WITH NFTshot AS (
SELECT date_trunc('day', block_timestamp) AS date, play_type, seller, buyer, price, tx_succeeded, play_category, tx_id
FROM flow.core.dim_topshot_metadata AS x
FULL OUTER JOIN flow.core.fact_nft_sales AS y
ON x.nft_id = y.nft_id
WHERE date >= '2022-07-01'
GROUP BY 1,2,3,4,5,6,7,8
)
SELECT
play_type AS category, (COUNT(DISTINCT(buyer))) / (COUNT(DISTINCT(seller))) AS buyer_seller_ratio
FROM NFTshot
WHERE category is not NULL
GROUP BY 1
ORDER BY 2 DESC