Query editor
WITH All_Actions AS (SELECTDATE_TRUNC('hour', block_time) AS Hour, 'ID-111' AS Market, CASE WHEN contract_address = 0x172a687c397E315DBE56ED78aB347D7743D0D4fa THEN 'xUSD'WHEN contract_address = 0xA1627a0E1d0ebcA9326D2219B84Df0c600bed4b1 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--, CAST(varbinary_to_int256(bytearray_ltrim(bytearray_substring(data,1 + 32 * 1, 32)))/1e18 AS DOUBLE) AS shares, CASE WHEN 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 sonic.logsWHERE topic0 IN ( 0xdcbc1c05240f31ff3ad067ef1ee35ce4997762752e3a095284754544f4c709d7 -- Deposit, 0xfbde797d201c681b91056529119e0b02407c7bb96a4a2c75c01fc9667232c8db -- Withdraw, 0x96558a334f4759f0e7c423d68c84721860bd8fbf94ddc4e55158ecb125ad04b5 -- Borrow, 0xe4a1ae657f49cb1fb1c7d3a94ae6093565c4c8c0e03de488f79c377c3c3a24e0 -- Repay, 0x1ad348f5de3e19e23d88e34248ceb6ab09804a35a3abc0e7758045b7b9036acd -- DepositProtected, 0x2dac01ea07cfa812db5881eaad96ba201c018c042b8f9ea7278cdf6f29e65f24 -- WithdrawProtected) AND contract_address IN (0x172a687c397E315DBE56ED78aB347D7743D0D4fa -- xUSD, 0xA1627a0E1d0ebcA9326D2219B84Df0c600bed4b1 -- USDC)),All_Data AS (SELECTCOUNT(tx_hash) AS Nb_Txn, COUNT(CASE WHEN Action = 'Deposit' THEN tx_hash ELSE NULL END) AS Cum_Deposit, COUNT(CASE WHEN Action = 'Withdraw' THEN tx_hash ELSE NULL END) AS Cum_Withdraw, COUNT(CASE WHEN Action = 'Borrow' THEN tx_hash ELSE NULL END) AS Cum_Borrow, COUNT(CASE WHEN Action = 'Repay' THEN tx_hash ELSE NULL END) AS Cum_RepayFROM All_Actions),Silo_Stats AS (SELECTday AS Hour, MAX(CASE WHEN asset = 'xUSD' THEN total_deposites ELSE NULL END) AS Silo0_Supply, MAX(CASE WHEN asset = 'xUSD' THEN total_borrows ELSE NULL END) AS Silo0_Borrow, MAX(CASE WHEN asset = 'xUSD' THEN available_to_borrow ELSE NULL END) AS Silo0_Available, MAX(CASE WHEN asset = 'xUSD' THEN utilization * 100 ELSE NULL END) AS Silo0_Utilization, MAX(CASE WHEN asset = 'USDC' THEN total_deposites ELSE NULL END) AS Silo1_Supply, MAX(CASE WHEN asset = 'USDC' THEN total_borrows ELSE NULL END) AS Silo1_Borrow, MAX(CASE WHEN asset = 'USDC' THEN available_to_borrow ELSE NULL END) AS Silo1_Available, MAX(CASE WHEN asset = 'USDC' THEN utilization * 100 ELSE NULL END) AS Silo1_UtilizationFROM dune.silo_finance.result_silo_finance_sonic_markets_by_silo_assetWHERE ID = 'ID112'