Query editor
WITH All_Actions AS (SELECTDATE_TRUNC('hour', block_time) AS Hour, 'ID-20' AS Market, CASE WHEN contract_address = 0x322e1d5384aa4ED66AeCa770B95686271de61dc3 THEN 'USDC'WHEN contract_address = 0xf55902DE87Bd80c6a35614b48d7f8B612a083C12 THEN 'S'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) -- runELSE 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 (0x322e1d5384aa4ED66AeCa770B95686271de61dc3 --Silo Finance Borrowable USDC Deposit, SiloId: 20 (bUSDC-20), 0xf55902DE87Bd80c6a35614b48d7f8B612a083C12-- Silo Finance Borrowable wS Deposit, SiloId: 20 (bwS-20))),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 = 'S' THEN total_deposites ELSE NULL END) AS S_Supply, MAX(CASE WHEN asset = 'S' THEN total_borrows ELSE NULL END) AS S_Borrow, MAX(CASE WHEN asset = 'S' THEN available_to_borrow ELSE NULL END) AS S_Available, MAX(CASE WHEN asset = 'S' THEN utilization * 100 ELSE NULL END) AS S_Utilization, MAX(CASE WHEN asset = 'USDC' THEN total_deposites ELSE NULL END) AS USDC_Supply, MAX(CASE WHEN asset = 'USDC' THEN total_borrows ELSE NULL END) AS USDC_Borrow, MAX(CASE WHEN asset = 'USDC' THEN available_to_borrow ELSE NULL END) AS USDC_Available, MAX(CASE WHEN asset = 'USDC' THEN utilization * 100 ELSE NULL END) AS USDC_UtilizationFROM dune.silo_finance.result_silo_finance_sonic_markets_by_silo_asset