SELECT

symbol,

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS avg_price

FROM

gnosis.price.ez_prices_hourly

GROUP BY

symbol

SELECT

d.depositor,

CASE

WHEN SUM(d.amount * pd.avg_price) >= 1000000000 THEN CONCAT('$', ROUND(SUM(d.amount * pd.avg_price) / 1000000000, 2), 'B')

WHEN SUM(d.amount * pd.avg_price) >= 1000000 THEN CONCAT('$', ROUND(SUM(d.amount * pd.avg_price) / 1000000, 2), 'M')

ELSE CONCAT('$', ROUND(SUM(d.amount * pd.avg_price), 2))

END AS "Total Deposit (USD)"

FROM

gnosis.defi.ez_lending_deposits d

JOIN

price_data pd ON d.token_symbol = pd.symbol

WHERE

d.amount IS NOT NULL

GROUP BY

d.depositor

HAVING

SUM(d.amount * pd.avg_price) > 100000

ORDER BY

SUM(d.amount * pd.avg_price) DESC

LIMIT 10;