SELECT

date_trunc(day, block_timestamp) as date,

sum(liquidated_amount) as total_liquidated_amt,

sum(liquidated_amount_usd) as total_liquidated_amount_usd,

sum(debt_to_cover_amount) as total_debt_to_cover_amount,

sum(debt_to_cover_amount_usd) as total_debt_to_cover_amount_usd,

liquidator,

borrower,

aave_version,

collateral_token_symbol,

debt_token_symbol,

collateral_asset,

sum(LIQUIDATED_AMOUNT_USD - DEBT_TO_COVER_AMOUNT_USD) as total_liqudation_fees_usd

FROM aave.liquidations

WHERE LIQUIDATED_AMOUNT_USD IS NOT NULL and block_timestamp >= dateadd(year, -1, getdate())

and debt_token_price is not null

group by date,liquidator,

borrower,

aave_version,

collateral_asset,

collateral_token_symbol,

debt_token_symbol

--qualify rank <= 10

ORDER BY date DESC

SELECT * FROM liquidations