with ulrp as
(
select date_trunc('day',BLOCK_TIMESTAMP) as date1,count(distinct BORROWER) as user_liquid_and_repay
from
(with a as(select block_timestamp,borrower
from aave.liquidations
),
b as (select block_timestamp,payer
from aave.repayments
)
select a.block_timestamp, a.borrower
from A
inner join b on a.borrower=b.payer
order by a.block_timestamp asc
)
group by date1 order by date1
),
urp as (select date_trunc('day',block_timestamp) as date2 ,count(distinct payer) as user_repay
from aave.repayments
group by date2 order by date2
)
select ulrp.date1 as date3, ulrp.user_liquid_and_repay , urp.user_repay,(urp.user_repay-ulrp.user_liquid_and_repay) as user_repay_usng_external
from urp
inner join ulrp on ulrp.date1=urp.date2