SELECT

t.receiver AS address,

t.amount,

t.tx_hash

FROM $query('5a97545a-0ff4-4c37-aa3a-d6f5bdfbbe28') t

),

average_price AS (

SELECT

AVG(price) AS avg_price_usd

FROM ethereum.price.ez_prices_hourly

WHERE token_address = LOWER('0x35d8949372d46b7a3d5a56006ae77b215fc69bc0')

AND hour >= '2024-12-01'

AND hour <= '2024-12-31'

),

aggregated_depositors AS (

SELECT

u.address,

SUM(CAST(u.amount AS DECIMAL(38, 0)) / CAST(1e18 AS DECIMAL(38, 18)) * ap.avg_price_usd) AS total_amount_usd,

COUNT(DISTINCT u.tx_hash) AS total_transactions

FROM usuals u

CROSS JOIN average_price ap

GROUP BY u.address

SELECT

address,

total_amount_usd,

total_transactions

FROM aggregated_depositors

ORDER BY total_amount_usd DESC;