How to display all client_ids that have not made any delays for the entire term of the loan?

Question

Oracle

There is a table of clients, clients, which indicates the mandatory fixed monthly payment that the client must pay on the mortgage.
Example data:

client_id payment_need
1 20000
2 25000
3 50000
... ...

And there is a principals table, in which for each client for each month the principal amount is indicated. The debt is not always reduced by the amount of the monthly payment, as some customers may pay either more or less, or skip the monthly payment altogether.
Example data:

client_id report_date principal
1 01/01/2019 2,000,000
1 02/01/2019 1,980,000
1 03/01/2019 1,960,000
… … …
2 01/01/2019 2,000,000
2 02/01/2019 1,990 00
2 03/01/2019 1,500,000
… … …
3 10/01/2019 5,000,000
3 11/01/2019 5,000,000
3 12/01/2019 4,950,000
… … …

Answer 1

I would try something like this:

select *
  from clients c
 where exists (
 select null
          from (select abs(principal - lag(principal) over(partition by client_id order by report_date)) diff 
                  from principals p
                 where p.client_id = c.client_id
                 )
         where diff < c.payment_need
            and diff is not null)

The key in this query is lag and exists. Exists should search up to the first discrepancy found, which speeds up the selection on average, and lag is the value for the last month.