WITH offer_loans AS (

SELECT

tx_id,

block_timestamp,

decoded_instruction :name :: STRING AS event_name,

decoded_instruction :args :principalLamports :: INT * pow(10, -9):: FLOAT AS loan_sol,

decoded_instruction :accounts [0] :pubkey :: STRING AS lender

FROM

solana.core.fact_decoded_instructions

WHERE

program_id = 'SHARKobtfF1bHhxD2eqftjHBdVSCbKo9JtgK71FhELP'

AND decoded_instruction :name :: STRING = 'offerLoan'

AND block_timestamp :: DATE >= '2023-12-01'

)

block_timestamp :: DATE as "Date",

CASE

WHEN block_timestamp :: DATE >= '2023-12-01'

AND block_timestamp :: DATE < '2024-01-01'

THEN 'Before'

WHEN block_timestamp :: DATE >= '2024-01-01'

THEN 'After'

END AS "Before/After $HARK Announcement",

COUNT(lender) as "# of Lenders",

COUNT(*) AS "# of Loan Offers",

SUM(loan_sol) AS "Total Loan Amount, SOL",

AVG(loan_sol) AS "Average Loan Size, SOL"

offer_loans

GROUP BY 1

;