Query editor
-- ============================================================================-- CRYPTO VOLUME SEASONALITY INDEX (Dune Trino engine)-- v3: adds median alongside mean to handle outlier-heavy distributions-- ============================================================================WITH daily_volume AS (SELECTDATE_TRUNC('day', block_time) AS day,SUM(amount_usd) AS volume_usdFROM dex.tradesWHERE block_time >= DATE '2021-01-01'AND block_time < DATE_TRUNC('year', CURRENT_DATE)AND amount_usd IS NOT NULLAND amount_usd BETWEEN 1 AND 1e9GROUP BY 1),monthly_volume AS (SELECTYEAR(day) AS yr,MONTH(day) AS mo,AVG(volume_usd) AS avg_daily_volumeFROM daily_volumeGROUP BY 1, 2),normalised AS (SELECTyr,mo,avg_daily_volume,100.0 * avg_daily_volume/ AVG(avg_daily_volume) OVER (PARTITION BY yr) AS index_valueFROM monthly_volume)SELECTmo AS month,ROUND(AVG(index_value), 1) AS volume_index_mean,ROUND(APPROX_PERCENTILE(index_value, 0.5), 1) AS volume_index_median,ROUND(STDDEV(index_value), 1) AS index_stddev,ROUND(MIN(index_value), 1) AS index_min,ROUND(MAX(index_value), 1) AS index_max,COUNT(*) AS years_in_sampleFROM normalisedGROUP BY moORDER BY mo