WITH toDate('2025-02-04') AS change_date, today() AS current_date, 100 AS days_since_change, change_date - toIntervalDay(days_since_change) AS pre_start_date, change_date + toIntervalDay(days_since_change) AS post_end_date, -- Pre-change stats ( SELECT avg(reinterpretAsUInt256(effective_gas_price) / 1e9) AS pre_avg, stddevPop(reinterpretAsUInt256(effective_gas_price) / 1e9) AS pre_stddev, count() AS pre_total_tx FROM agnostic__blockchain__ethereum_mainnet__transactions WHERE date BETWEEN change_date - toIntervalDay(days_since_change) AND change_date - toIntervalDay(1) AND effective_gas_price IS NOT NULL ) AS pre_stats, -- Post-change stats ( SELECT avg(reinterpretAsUInt256(effective_gas_price) / 1e9) AS post_avg, stddevPop(reinterpretAsUInt256(effective_gas_price) / 1e9) AS post_stddev, count() AS post_total_tx FROM agnostic__blockchain__ethereum_mainnet__transactions WHERE date BETWEEN change_date AND change_date + toIntervalDay(days_since_change) AND effective_gas_price IS NOT NULL ) AS post_stats SELECT period, std_dev_bucket, count() AS tx_count, round(100.0 * count() / total_tx, 2) AS percent_of_period FROM ( -- Subquery: create clean columns first SELECT multiIf(date < change_date, 'Pre-Change Period', 'Post-Change Period') AS period, multiIf( date < change_date, CASE WHEN abs(reinterpretAsUInt256(effective_gas_price) / 1e9 - pre_stats.pre_avg) <= 0.05 * pre_stats.pre_stddev THEN 'Within 0.05 Std Dev' WHEN abs(reinterpretAsUInt256(effective_gas_price) / 1e9 - pre_stats.pre_avg) <= 0.1 * pre_stats.pre_stddev THEN 'Within 0.1 Std Dev' WHEN abs(reinterpretAsUInt256(effective_gas_price) / 1e9 - pre_stats.pre_avg) <= 0.2 * pre_stats.pre_stddev THEN 'Within 0.2 Std Dev' WHEN abs(reinterpretAsUInt256(effective_gas_price) / 1e9 - pre_stats.pre_avg) <= 0.3 * pre_stats.pre_stddev THEN 'Within 0.3 Std Dev' ELSE 'Beyond 0.3 Std Dev' END, CASE WHEN abs(reinterpretAsUInt256(effective_gas_price) / 1e9 - post_stats.post_avg) <= 0.05 * post_stats.post_stddev THEN 'Within 0.05 Std Dev' WHEN abs(reinterpretAsUInt256(effective_gas_price) / 1e9 - post_stats.post_avg) <= 0.1 * post_stats.post_stddev THEN 'Within 0.1 Std Dev' WHEN abs(reinterpretAsUInt256(effective_gas_price) / 1e9 - post_stats.post_avg) <= 0.2 * post_stats.post_stddev THEN 'Within 0.2 Std Dev' WHEN abs(reinterpretAsUInt256(effective_gas_price) / 1e9 - post_stats.post_avg) <= 0.3 * post_stats.post_stddev THEN 'Within 0.3 Std Dev' ELSE 'Beyond 0.3 Std Dev' END ) AS std_dev_bucket, multiIf(date < change_date, pre_stats.pre_total_tx, post_stats.post_total_tx) AS total_tx FROM agnostic__blockchain__ethereum_mainnet__transactions WHERE ( (date BETWEEN pre_start_date AND change_date - toIntervalDay(1)) OR (date BETWEEN change_date AND post_end_date) ) AND effective_gas_price IS NOT NULL ) AS subquery GROUP BY period, std_dev_bucket, total_tx ORDER BY period ASC, std_dev_bucket ASC;