Query editor
with prices AS (SELECTa.contract_address,a.decimals,a.symbolFROM prices.usd ainner join (selectcontract_address,max(minute) as minuteFROM prices.usdwhere symbol in ('WETH', 'USDC', 'DAI')and decimals IS NOT NULLand minute >= current_dategroup by contract_address) bon a.contract_address=b.contract_address and a.minute=b.minutewhere a.minute >= current_date),loans_initiated as (SELECTvarbinary_to_int256(bytearray_ltrim(bytearray_substring(b.data,1 ,32))) AS loanId, varbinary_to_int256(bytearray_ltrim(bytearray_substring(b.data,1 ,32))) - 1 as prev_loanId, substr(b.data, 12 + 1 + 32 * 4,20) AS Borrower, cast(varbinary_to_bigint(bytearray_ltrim(bytearray_substring(b.data,1+ 32 * 5,32))) as bigint) AS nft_token_id, substr(b.data, 12 + 1 + 32 * 6,20) AS nft_collection, substr(b.data,12 + 1+ 32 * 7,20) AS Principal_Contract, varbinary_to_int256(bytearray_ltrim(bytearray_substring(b.data,1+ 32 * 8,32))) AS Principal0, CAST(from_unixtime(CAST(varbinary_to_int256(bytearray_ltrim(bytearray_substring(b.data,1+ 32 * 9,32))) AS DOUBLE)) AS TIMESTAMP) AS loan_start, CAST(varbinary_to_int256(bytearray_ltrim(bytearray_substring(b.data,1+ 32 * 10,32))) AS DOUBLE) / ( 60 * 60 * 24 ) AS duration, substr(b.data, 12 + 1+ 32 * 14,20) AS Lender, varbinary_to_int256(bytearray_ltrim(bytearray_substring(b.data,1+ 32 * 15,32))) AS Principal1, varbinary_to_int256(bytearray_ltrim(bytearray_substring(b.data,1+ 32 * 16,32))) AS Origination_Fee, CAST(from_unixtime(CAST(varbinary_to_int256(bytearray_ltrim(bytearray_substring(b.data,1+ 32 * 17,32))) AS DOUBLE)) AS TIMESTAMP) AS Due_Date, varbinary_to_int256(bytearray_ltrim(bytearray_substring(b.data,1+ 32 * 18,32)))/1e4 AS APR, b.tx_hash, (case whendate_add('day',cast(CAST(varbinary_to_int256(bytearray_ltrim(bytearray_substring(b.data,1+ 32 * 10,32))) AS DOUBLE) / ( 60 * 60 * 24 ) as bigint),CAST(from_unixtime(CAST(varbinary_to_int256(bytearray_ltrim(bytearray_substring(b.data,1+ 32 * 9,32))) AS DOUBLE)) AS TIMESTAMP)) < now()--loan_start + durationthen 'Repaid/Liquidated'else 'Active' end) as status, null as loan_end, false as refinanceFROM ethereum.logs bWHERE topic0 = 0x4b41203b879da1aacbde87e7250643734daa0f530eb7b581f5758f245e968e92and block_time > cast('2023-07-01' as date)--unix time style loan_start),loans_refinanced as (SELECTblock_time,varbinary_to_int256(bytearray_ltrim(bytearray_substring(b.data,1+ 32 * 2,32))) AS loanId, varbinary_to_int256(bytearray_ltrim(bytearray_substring(b.data,1+ 32 * 1,32))) AS prev_loanId, substr(b.data, 12 + 1+ 32 * 5,20) AS Borrower, cast(varbinary_to_bigint(bytearray_ltrim(bytearray_substring(b.data,1+ 32 * 6,32))) as bigint) AS nft_token_id