ChiefYawww
    Updated 2023-02-16
    with
    fff as (
    SELECT
    inner_instruction,
    date_trunc('day', e.block_timestamp) as date,
    program_id,
    tx_from as lender,
    tx_to borrower,
    mint,
    amount as borrow_amount,
    parse_json(inner_instruction):instructions[0]:parsed:type as init_loan,
    CASE WHEN init_loan = 'createAccount' THEN init_loan END as new_loan,
    parse_json(inner_instruction):instructions[5]:parsed:info:mint as nft
    FROM
    solana.core.fact_events as e
    JOIN solana.core.fact_transfers as t ON e.tx_id = t.tx_id
    WHERE
    succeeded = True
    AND program_id = 'JCFRaPv7852ESRwJJGRy2mysUMydXZgVVhrMLmExvmVp'
    -- AND e.tx_id = '5tAnRjtbwvUqVMoK2bHJ7T1Bit7uutz85EAjaP1Xa2GFSRCwnd3jWSqEXV3wcoWgW75hrpXD4c5i2bskjicBLezq'
    AND e.index = 1
    AND new_loan = 'createAccount' -- without this you are counting all money movement (new loans, ending loans, listings maybe)
    ),
    SELECT
    parse_json(inner_instruction):instructions[3]:parsed:type as end_loan,
    CASE WHEN end_loan = 'thawAccount' THEN end_loan END as end_loan,
    end_loan as (


    )
    SELECT
    date,
    count(distinct lender) as lenders,
    count(distinct borrower) borrowers,
    count(nft) NFTs,
    sum(borrow_amount) amount_borrowed
    Run a query to Download Data