Query editor
with prices AS (SELECTcontract_address, decimals, symbolFROM prices.usdwhere symbol in ('WETH', 'USDC', 'DAI')and decimals IS NOT NULLGROUP BY 1,2,3),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, varbinary_to_int256(bytearray_ltrim(bytearray_substring(b.data,1+ 32 * 5,32))) AS Collection_Item, substr(b.data, 12 + 1 + 32 * 6,20) AS NFT_Contract, 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)) < current_date--loan_start + durationthen 'Repaid/Liquidated'else 'Active' end) as status, null as loan_end, false as refinanceFROM ethereum.logs bWHERE topic0 = 0x4b41203b879da1aacbde87e7250643734daa0f530eb7b581f5758f245e968e92and CAST(varbinary_to_int256(bytearray_ltrim(bytearray_substring(b.data,1+ 32 * 9,32))) AS DOUBLE) >= to_unixtime(timestamp '2023-11-01 00:00')--unix time style loan_start),loans_refinanced as (SELECTvarbinary_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, varbinary_to_int256(bytearray_ltrim(bytearray_substring(b.data,1+ 32 * 6,32))) AS Collection_Item, substr(b.data,12 + 1+ 32 * 7,20) AS NFT_Contract, substr(b.data,12 + 1+ 32 * 8,20) AS Principal_Contract, varbinary_to_int256(bytearray_ltrim(bytearray_substring(b.data,1+ 32 * 9,32))) AS Principal0, CAST(from_unixtime(CAST(varbinary_to_int256(bytearray_ltrim(bytearray_substring(b.data,1+ 32 * 10,32))) AS DOUBLE)) AS TIMESTAMP) AS loan_start, CAST(varbinary_to_int256(bytearray_ltrim(bytearray_substring(b.data,1+ 32 * 11,32))) AS DOUBLE) / ( 60 * 60 * 24 ) AS duration, substr(b.data,12 + 1+ 32 * 15,20) AS Lender, varbinary_to_int256(bytearray_ltrim(bytearray_substring(b.data,1+ 32 * 16,32))) AS Principal1, 0 as Origination_fee, CAST(from_unixtime(CAST(varbinary_to_int256(bytearray_ltrim(bytearray_substring(b.data,1+ 32 * 18,32))) AS DOUBLE)) AS TIMESTAMP) AS Due_Date, varbinary_to_int256(bytearray_ltrim(bytearray_substring(b.data,1+ 32 * 19,32)))/1e4 AS APR