Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/* 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment