SELECT

hour::DATE AS DATEE,

TOKEN_ADDRESS,

AVG(PRICE) AS USDPRICE

FROM

aptos.price.ez_prices_hourly

GROUP BY

1, 2

),

base AS (

SELECT

DISTINCT ACCOUNT_ADDRESS AS USERS,

SUM(AMOUNT / POW(10, DECIMALS) * USDPRICE) AS USD

FROM

aptos.core.fact_transfers A

JOIN aptos.core.fact_events USING(TX_HASH)

JOIN price P ON BLOCK_TIMESTAMP::DATE = DATEE

AND A.TOKEN_ADDRESS = P.TOKEN_ADDRESS

LEFT JOIN aptos.core.dim_tokens T ON A.TOKEN_ADDRESS = T.TOKEN_ADDRESS

WHERE

PAYLOAD_FUNCTION = '0xc6bc659f1649553c1a3fa05d9727433dc03843baac29473c817d06d39e7621ba::scripts::borrow'

AND EVENT_RESOURCE = 'BorrowEvent'

AND EVENT_MODULE = 'lending'

AND TRANSFER_EVENT = 'DepositEvent'

AND BLOCK_TIMESTAMP::DATE >= CURRENT_DATE - 30

GROUP BY

1

CASE

WHEN USD < 50 THEN 'Less Than 50$'

WHEN USD >= 50 AND USD < 500 THEN 'Between 50$ - 500$'

WHEN USD >= 500 AND USD < 5000 THEN 'Between 500$ - 5000$'

WHEN USD >= 5000 AND USD < 20000 THEN 'Between 5000$ - 20000$'

ELSE 'More Than 20000$'