WITH deposits AS ( SELECT block_timestamp, origin_from_address AS depositor, ethereum.public.udf_hex_to_int(SUBSTR(data, 0, 66)) AS amount FROM ethereum.core.fact_event_logs WHERE contract_address = lower('0x3bdc69c4e5e13e52a65f5583c23efb9636b469d6') AND topics[0] = '0xbf25d6cb74c97a403cfab1c4c0abc9ffe3edd964de9924de0565f5ffe3d6ca79' ), deposits_per_depositor AS ( SELECT depositor, SUM(amount) / POWER(10, 18) AS total_deposit_amount, COUNT(*) AS total_deposits, CASE WHEN total_deposit_amount < 5 THEN '< 5 ETH' WHEN total_deposit_amount < 20 THEN '< 20 ETH' WHEN total_deposit_amount < 100 THEN '< 100 ETH' WHEN total_deposit_amount < 1000 THEN '< 1000 ETH' ELSE '1000+ ETH' END AS amount_class, CASE WHEN total_deposit_amount < 5 THEN 0 WHEN total_deposit_amount < 20 THEN 1 WHEN total_deposit_amount < 100 THEN 2 WHEN total_deposit_amount < 1000 THEN 3 ELSE 4 END AS amount_class_id, CASE WHEN total_deposits = 1 THEN 'Single Deposit' WHEN total_deposits < 5 THEN '< 5 Deposits' ELSE '5+ Deposits' END AS count_class, CASE WHEN total_deposits = 1 THEN 0 WHEN total_deposits < 5 THEN 1 ELSE 2 END AS count_class_id FROM deposits GROUP BY depositor ), temp AS ( SELECT 'ETH Deposited' AS x, amount_class, amount_class_id, SUM(total_deposit_amount) AS value FROM deposits_per_depositor GROUP BY amount_class, amount_class_id UNION SELECT 'Depositors' AS x, amount_class, amount_class_id, COUNT(DISTINCT depositor) AS value FROM deposits_per_depositor GROUP BY amount_class, amount_class_id UNION SELECT 'Deposits' AS x, amount_class, amount_class_id, SUM(total_deposits) AS value FROM deposits_per_depositor GROUP BY amount_class, amount_class_id ) SELECT * FROM temp ORDER BY amount_class_id

Rocket Pool Overview