more info: https://attilatoth.dev/speaking/analyze-nft-transactions/

Analyzing_NFT_transactions.sql

/* Count queries */
SELECT * FROM approximate_row_count('assets') 

SELECT * FROM approximate_row_count('collections')  

SELECT * FROM approximate_row_count('accounts') 

SELECT count(*), MIN(time) AS min_date, MAX(time) AS max_date FROM nft_sales_new


/* Number of chunks */
SELECT hypertable_name, count(*) AS chunk_count FROM timescaledb_information.chunks
WHERE hypertable_name = 'nft_sales_new'
GROUP BY hypertable_name 

/* Time ranges in chunks */
SELECT * FROM timescaledb_information.chunks
WHERE hypertable_name = 'nft_sales_new'

/* Payment symbols */
SELECT payment_symbol, count(*) FROM nft_sales_new
GROUP BY payment_symbol 
ORDER BY count(*) DESC 

/* Auction types */
SELECT auction_type, count(*) FROM nft_sales_new
GROUP BY auction_type 
ORDER BY count(*) DESC 

/* List 10 transactions */
SELECT * FROM nft_sales_new
LIMIT 10

/* Time-series chart: total daily ETH volume of a given collection */
SELECT 
time_bucket('1 day', time) AS bucket, 
count(*) AS total_volume, 
sum(total_price) total_volume_eth,
count(DISTINCT asset_id) AS count_nfts 
FROM nft_sales_new s
INNER JOIN collections c ON c.id = s.collection_id 
WHERE payment_symbol = 'ETH' AND c.slug = 'cryptokitties'
GROUP BY bucket
ORDER BY bucket DESC 


/* Most expensive CryptoKitties NFT sold in each week*/
SELECT time_bucket('1 week', time) AS bucket, 
MAX(total_price) AS price_paid, 
LAST(a.name, total_price) AS asset_name, 
LAST(a.url, total_price) AS url 
FROM nft_sales_new s
INNER JOIN assets a ON a.id = s.asset_id
INNER JOIN collections c ON c.id = s.collection_id 
WHERE payment_symbol = 'ETH' AND c.slug = 'cryptokitties'
GROUP BY bucket
ORDER BY bucket DESC 


/* Collection continuous aggregates */
CREATE MATERIALIZED VIEW collections_daily_new
WITH (timescaledb.continuous) AS
SELECT
collection_id,
time_bucket('1 day', time) AS bucket,
mean(percentile_agg(total_price)) AS mean_price,
approx_percentile(0.5, percentile_agg(total_price)) AS median_price,
COUNT(*) AS volume,
SUM(total_price) AS volume_eth,
LAST(asset_id, total_price) AS most_expensive_nft_id,
MAX(total_price) AS max_price
FROM nft_sales_new
WHERE payment_symbol = 'ETH'
GROUP BY bucket, collection_id;


/* Query the collection aggregates */
SELECT * FROM collections_daily_new


/* Collections with the highest volume? */
SELECT
slug,
SUM(volume) total_volume,
SUM(volume_eth) total_volume_eth
FROM collections_daily_new cagg
INNER JOIN collections c ON cagg.collection_id = c.id
GROUP BY cagg.collection_id, slug
ORDER BY total_volume DESC;


/* Daily number of “CryptoKitties” NFT transactions? */
SELECT bucket, slug, volume
FROM collections_daily_new cagg
INNER JOIN collections c ON cagg.collection_id = c.id
WHERE slug = 'cryptokitties'
ORDER BY bucket DESC


/* Daily number of NFT transactions, "CryptoKitties" vs Ape Gang from past 3 months? */
SELECT bucket, slug, volume
FROM collections_daily_new cagg
INNER JOIN collections c ON cagg.collection_id = c.id
WHERE slug IN ('cryptokitties', 'ape-gang')
ORDER BY bucket DESC, slug;


/* Mean vs median sale price of CryptoKitties? */
SELECT bucket, slug, mean_price, median_price
FROM collections_daily_new cagg
INNER JOIN collections c ON cagg.collection_id = c.id
WHERE slug = 'cryptokitties'
ORDER BY bucket DESC