WITH pricetbl AS (

SELECT

hour::date AS date,

symbol,

AVG(price) AS price

FROM near.price.ez_prices_hourly

GROUP BY 1, 2

),

amounttbl AS (

SELECT

block_timestamp,

tx_hash,

sender_id,

actions,

CASE

WHEN a.symbol = 'ETH' THEN 'WETH'

WHEN a.symbol = 'sFRAX' THEN 'FRAX'

ELSE a.symbol

END AS symbol,

CASE

WHEN a.symbol IN ('WBTC', 'DAI', 'USDt', 'FRAX', 'ETH', 'USDC', 'BRRR', 'USN', 'USDT.e', 'AURORA', 'USDC.e', 'sFRAX', 'WOO')

THEN a.amount_raw / POW(10, 18)

ELSE a.amount_raw / POW(10, 24)

END AS amount

FROM near.defi.ez_lending a

WHERE a.symbol IN ('sFRAX', 'WBTC', 'DAI', 'USDt', 'FRAX', 'ETH', 'USDC', 'BRRR', 'USN', 'USDT.e', 'AURORA', 'USDC.e', 'LINEAR', 'WOO', 'wNEAR', 'STNEAR')

SELECT

a.symbol AS "Symbol",

SUM(a.amount * p.price) AS "Total Deposit Volume (USD)",

count (DISTINCT tx_hash) as "Total Deposits"

FROM amounttbl a