SELECT

MINER AS miner1 ,COUNT(*) AS TOTAL_BLOCKS

FROM ethereum.core.fact_blocks

GROUP BY 1 ),

t2 AS (

SELECT Miner,

CASE

WHEN TX_COUNT < 250 THEN '1. Less than 250 tx'

WHEN TX_COUNT >= 250 AND TX_COUNT <= 400 then '2. Between 250 and 400'

WHEN TX_COUNT > 400 THEN '3. More than 400'

END AS block_tx_count,

count(*) AS blocks

FROM ethereum.core.fact_blocks

GROUP BY 1,2

),

t3 AS (

SELECT Miner, block_tx_count, Blocks/TOTAL_BLOCKS AS Blocks_pct

FROM t2

JOIN t1 ON t1.miner1 = t2.Miner )

SELECT block_tx_count,COUNT(*)/(SELECT COUNT(DISTINCT MINER) FROM ethereum.core.fact_blocks)*100 AS Miners_PCT

FROM t3

WHERE Blocks_pct = 1

GROUP BY 1

ORDER BY 1