SELECT trunc(hour, 'day') as date,

AVG(price) AS price_usd

FROM ethereum.price.ez_prices_hourly

WHERE token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'

GROUP BY date

),

MainBasename AS (

SELECT DATE_TRUNC('day', Block_timestamp) AS day,

Tx_hash,

from_address AS miner,

origin_function_signature,

value * price_usd AS amount_usd

FROM base.core.fact_transactions ft

JOIN Ethprice ep

ON ep.date = DATE_TRUNC('day', ft.Block_timestamp)

WHERE to_address = '0x4ccb0bb02fcaba27e82a56646e81d8c5bc4119a5'

AND block_timestamp >= '2024-08-20'

AND origin_function_signature IN ('0xc7c79676', '0xe0093eda')

AND STATUS = 'SUCCESS'

),

UserVolume AS (

SELECT

miner,

SUM(amount_usd) AS total_volume_usd

FROM MainBasename

GROUP BY miner

),

UserDistribution AS (

SELECT

CASE

WHEN total_volume_usd < 4 THEN '< $4'

WHEN total_volume_usd BETWEEN 4 AND 10 THEN '$4-$10'