SELECT

user_address,

FROM (

SELECT

borrower as user_address,

MIN(block_timestamp::DATE) as initial_borrow_date,

ROW_NUMBER() OVER (PARTITION BY borrower ORDER BY MIN(block_timestamp::DATE)) as rn

FROM avalanche.defi.ez_lending_borrows

WHERE platform = 'Benqi' AND NOT EXISTS (SELECT 1 FROM avalanche.core.dim_contracts

WHERE address = borrower )

GROUP BY borrower) first_borrows

WHERE initial_borrow_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '12 months'

),

liquidation_activity AS (

SELECT

user_address,

COUNT(DISTINCT b.tx_hash) as borrow_count,

COALESCE(COUNT(DISTINCT l.tx_hash), 0) as liquidation_count,

COALESCE(SUM(l.amount_usd), 0) as total_liquidated_usd

FROM first_time_borrowers ftb

LEFT JOIN avalanche.defi.ez_lending_borrows b

ON ftb.user_address = b.borrower

AND b.block_timestamp >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '12 months'

AND b.platform = 'Benqi'

LEFT JOIN avalanche.defi.ez_lending_liquidations l

ON ftb.user_address = l.borrower

AND l.block_timestamp >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '12 months'

AND l.platform = 'Benqi'

GROUP BY user_address

SELECT

CASE

WHEN liquidation_count > 0 THEN 'Has Been Liquidated'

ELSE 'Never Liquidated'