WITH token_info AS (

SELECT * FROM

(VALUES ('SolvBTC', 18),

('BTC.b', 8),('WETH.e', 18),

('JOE', 18),('ggAVAX', 18),

('AVAX', 18),('USDC', 6)

) AS tokens(symbol, decimals)

),

weekly_borrows AS (

SELECT

DATE_TRUNC('week', e.BLOCK_TIMESTAMP) AS week,

t.symbol AS token,

t.decimals,

SUM(e.DECODED_LOG:principalPaid/POWER(10, t.decimals)) AS weekly_borrowed_amount,

COUNT(DISTINCT e.TX_HASH) AS transaction_count

FROM avalanche.core.ez_decoded_event_logs e

JOIN (

SELECT

tx_hash,

tt.symbol,

m.decimals

FROM avalanche.core.ez_token_transfers tt

JOIN token_info m

ON UPPER(tt.symbol) = UPPER(m.symbol)

WHERE tt.symbol IN (SELECT symbol FROM token_info)

UNION ALL

SELECT

tx_hash,

'AVAX' AS symbol,

18 AS decimals

FROM avalanche.core.ez_native_transfers

WHERE 'AVAX' IN (SELECT symbol FROM token_info)

) t ON e.TX_HASH = t.TX_HASH

WHERE e.TOPIC_0 = '0x05f1e09b11562173f6f8e925cc154b90bb330906d16ca09cef7a8defd4939d6e'