WITH AVG_AMOUNTS AS (

SELECT

sender_id,

AVG(CASE WHEN actions = 'borrow' THEN amount_usd ELSE 0 END) AS avg_loan_size_usd,

AVG(CASE WHEN actions IN ('deposit', 'deposit_to_reserve') THEN amount_usd ELSE 0 END) AS avg_deposit_size_usd,

MAX(CASE WHEN actions = 'borrow' THEN amount_usd ELSE 0 END) AS max_loan_size_usd,

MAX(CASE WHEN actions IN ('deposit', 'deposit_to_reserve') THEN amount_usd ELSE 0 END) AS max_deposit_size_usd,

MEDIAN(CASE WHEN actions = 'borrow' THEN amount_usd ELSE 0 END) AS med_loan_size_usd,

MEDIAN(CASE WHEN actions IN ('deposit', 'deposit_to_reserve') THEN amount_usd ELSE 0 END) AS med_deposit_size_usd

FROM

near.defi.ez_lending

GROUP BY 1

AVG(avg_loan_size_usd) AS average_loan_size_usd,

AVG(avg_deposit_size_usd) AS average_deposit_size_usd,

MEDIAN(med_loan_size_usd) AS median_loan_size_usd,

MEDIAN(med_deposit_size_usd) AS median_deposit_size_usd,

MAX(max_loan_size_usd) AS maximum_loan_size_usd,

MAX(max_deposit_size_usd) AS maximum_deposit_size_usd

AVG_AMOUNTS;