date_trunc('day', block_timestamp) AS day,

COUNT(CASE WHEN tx_count BETWEEN 0 AND 1 THEN 1 END) AS count_0_1,

COUNT(CASE WHEN tx_count BETWEEN 2 AND 10 THEN 1 END) AS count_2_10,

COUNT(CASE WHEN tx_count BETWEEN 11 AND 100 THEN 1 END) AS count_11_100,

COUNT(CASE WHEN tx_count BETWEEN 101 AND 1000 THEN 1 END) AS count_101_1000,

COUNT(CASE WHEN tx_count > 1000 THEN 1 END) AS count_1000_more

FROM (

SELECT

block_timestamp,

tx_hash,

COUNT(*) AS tx_count

FROM

ethereum.core.fact_traces

WHERE

date_trunc('day', block_timestamp) >= TIMESTAMP '2015-01-01'

--AND date_trunc('day', block_timestamp) < TIMESTAMP '2024-01-01'

GROUP BY

block_timestamp, tx_hash

) AS subquery

GROUP BY day

ORDER BY day;