Query editor
WITH All_Actions AS (SELECTDATE_TRUNC('day', block_time) AS Hour, 'ID-125' AS Market, CASE WHEN contract_address = 0x28C8Ef0a8A54fA1061032481c7Bde5f7c9Be199F THEN 'savUSD'WHEN contract_address = 0xE0345f66318F482aCCcd67244A921C7FDC410957 THEN 'USDC'ELSE NULL END AS symbol, CASE WHEN topic0 IN( 0xdcbc1c05240f31ff3ad067ef1ee35ce4997762752e3a095284754544f4c709d7, 0x96558a334f4759f0e7c423d68c84721860bd8fbf94ddc4e55158ecb125ad04b5, 0x1ad348f5de3e19e23d88e34248ceb6ab09804a35a3abc0e7758045b7b9036acd) -- Deposit / BorrowTHEN CAST(varbinary_to_int256(bytearray_ltrim(bytearray_substring(data,1 , 32))) AS DOUBLE)WHEN topic0 IN( 0xfbde797d201c681b91056529119e0b02407c7bb96a4a2c75c01fc9667232c8db, 0xe4a1ae657f49cb1fb1c7d3a94ae6093565c4c8c0e03de488f79c377c3c3a24e0, 0x2dac01ea07cfa812db5881eaad96ba201c018c042b8f9ea7278cdf6f29e65f24) -- Withdraw / RepayTHEN - 1 * CAST(varbinary_to_int256(bytearray_ltrim(bytearray_substring(data,1 , 32))) AS DOUBLE)ELSE NULL END AS amount, CASEWHEN topic0 = 0xdcbc1c05240f31ff3ad067ef1ee35ce4997762752e3a095284754544f4c709d7 THEN 'Deposit'WHEN topic0 = 0x1ad348f5de3e19e23d88e34248ceb6ab09804a35a3abc0e7758045b7b9036acd THEN 'Deposit'WHEN topic0 = 0xfbde797d201c681b91056529119e0b02407c7bb96a4a2c75c01fc9667232c8db THEN 'Withdraw'WHEN topic0 = 0x2dac01ea07cfa812db5881eaad96ba201c018c042b8f9ea7278cdf6f29e65f24 THEN 'Withdraw'WHEN topic0 = 0x96558a334f4759f0e7c423d68c84721860bd8fbf94ddc4e55158ecb125ad04b5 THEN 'Borrow'WHEN topic0 = 0xe4a1ae657f49cb1fb1c7d3a94ae6093565c4c8c0e03de488f79c377c3c3a24e0 THEN 'Repay'ELSE NULL END AS Action, substr(topic2, 12 + 1 + 32 * 0, 20) AS Owner, tx_hashFROM avalanche_c.logsWHERE topic0 IN (0xdcbc1c05240f31ff3ad067ef1ee35ce4997762752e3a095284754544f4c709d7 -- Deposit, 0xfbde797d201c681b91056529119e0b02407c7bb96a4a2c75c01fc9667232c8db -- Withdraw, 0x96558a334f4759f0e7c423d68c84721860bd8fbf94ddc4e55158ecb125ad04b5 -- Borrow, 0xe4a1ae657f49cb1fb1c7d3a94ae6093565c4c8c0e03de488f79c377c3c3a24e0 -- Repay, 0x1ad348f5de3e19e23d88e34248ceb6ab09804a35a3abc0e7758045b7b9036acd -- DepositProtected, 0x2dac01ea07cfa812db5881eaad96ba201c018c042b8f9ea7278cdf6f29e65f24 -- WithdrawProtected) AND contract_address IN (0x28C8Ef0a8A54fA1061032481c7Bde5f7c9Be199F -- savUSD, 0xE0345f66318F482aCCcd67244A921C7FDC410957 -- USDC)),All_Data AS (SELECTHour, SUM(CASE WHEN Action IN ('Deposit','Withdraw') AND symbol = 'savUSD' THEN amount/1e18 ELSE NULL END) OVER (PARTITION BY symbol ORDER BY Hour) AS Silo0_Supply, SUM(CASE WHEN Action IN ('Deposit','Withdraw') AND symbol = 'USDC' THEN amount/1e6 ELSE NULL END) OVER (PARTITION BY symbol ORDER BY Hour) AS Silo1_Supply, SUM(CASE WHEN Action IN ('Borrow','Repay') AND symbol = 'savUSD' THEN amount/1e18 ELSE NULL END) OVER (PARTITION BY symbol ORDER BY Hour) AS Silo0_Borrow, SUM(CASE WHEN Action IN ('Borrow','Repay') AND symbol = 'USDC' THEN amount/1e6 ELSE NULL END) OVER (PARTITION BY symbol ORDER BY Hour) AS Silo1_Borrow, COUNT(tx_hash) OVER (ORDER BY Hour) AS Nb_Txn, COUNT(CASE WHEN Action = 'Deposit' THEN tx_hash ELSE NULL END) OVER (ORDER BY Hour) AS Cum_Deposit, COUNT(CASE WHEN Action = 'Withdraw' THEN tx_hash ELSE NULL END) OVER (ORDER BY Hour) AS Cum_Withdraw, COUNT(CASE WHEN Action = 'Borrow' THEN tx_hash ELSE NULL END) OVER (ORDER BY Hour) AS Cum_Borrow, COUNT(CASE WHEN Action = 'Repay' THEN tx_hash ELSE NULL END) OVER (ORDER BY Hour) AS Cum_RepayFROM All_Actions),Adding_Grp AS (SELECT*, COUNT(Silo0_Supply) OVER (ORDER BY Hour) AS Grp, COUNT(Silo1_Supply) OVER (ORDER BY Hour) AS Grp1