SELECT

borrower,

amount,

token_symbol

FROM

gnosis.defi.ez_lending_borrows

),

repayment_data AS (

SELECT

payer AS borrower,

amount,

token_symbol

FROM

gnosis.defi.ez_lending_repayments

),

price_data AS (

SELECT

symbol,

MAX(price) AS max_price

FROM

gnosis.price.ez_prices_hourly

GROUP BY

symbol

SELECT

b.borrower,

SUM(b.amount * pd_b.max_price) AS total_borrowed_usd,

SUM(r.amount * pd_r.max_price) AS total_repaid_usd,

(SUM(r.amount * pd_r.max_price) / NULLIF(SUM(b.amount * pd_b.max_price), 0)) * 100 AS percent_repaid

FROM

borrow_data b

JOIN

repayment_data r ON b.borrower = r.borrower

JOIN