recorded_hour::date AS dates,

currency,

symbol,

avg (price) AS prices

FROM osmosis.price.ez_prices

GROUP BY 1,2,3

SELECT

DISTINCT symbol,

COUNT (DISTINCT tx_id) AS txes,

sum (amount),

sum (amount / pow(10, decimal)) AS adj_amount,

sum ((amount / pow(10, decimal)) * prices) AS amount_usd

FROM osmosis.core.fact_transfers t JOIN price p

ON t.currency = p.currency and t.block_timestamp::date = p.dates::date

WHERE transfer_type LIKE 'IBC%'

AND decimal IS NOT NULL

AND block_timestamp::date >= current_date - 30

AND tx_succeeded = 'true'

AND amount / pow(10, decimal) * prices <= 1e6

GROUP BY 1

ORDER BY amount_usd DESC

LIMIT 10