How to get the number of overdue loans?

Question

SQL

There is a table with data about borrowers

CREATE TABLE customers
(
  customer_id integer,
  region_id integer,
  created date
 );
 
CREATE TABLE regions
(
  region_id integer,
  timezone varchar(255)
);
  
CREATE TABLE applications
(
   application_id integer,
   credit_date date,
   customer_id integer,
   back_date date,
   credit_sum decimal(8, 2)
);

 CREATE TABLE payments
 (
   application_id integer,
   created_at date,
   payment decimal(8, 2)
 );

You need to write a query to get the output of the following format for the period from 07/01/2021 to 07/15/2021:


At the moment, I wrote such a query, it displays all fields except the last one.

select to_char(applications.credit_date, 'DD.MM.YYYY') as Дата_займа,
       applications.credit_sum as Сумма_займа,
       count(applications.credit_sum) as Количество_займов,
       sum(applications.credit_sum) * count(distinct(applications.credit_sum)) as Сумма_займов_руб,
from applications
group by to_char(applications.credit_date, 'DD.MM.YYYY'), applications.credit_sum
order by to_char(applications.credit_date, 'DD.MM.YYYY'), applications.credit_sum

The question is how to display a column with information about debtors? Consider as debtors those who, at the end of the loan agreement, have not transferred the entire loan amount.

I will be very grateful!!!

Answer 1

it is difficult to understand the task, there is little information, examples of data with explanations are needed
applications.back_date most likely contains the date of repayment of the debt, so if it is null, then applications.customer_id will refer to the debtor? so count these count(applications.id) for group by applications.customer_id with the condition applications.back_date is null