Как получить количество просроченных займов?

Question

[mr_Try]

Есть таблица с данными о заёмщиках

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)
 );

Нужно написать запрос, чтобы получить вывод следующего формата за период с 01.07.2021 по 15.07.2021:


На данный момент написал такой запрос, он выводит все поля, кроме последнего.

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

Вопрос в том, как вывести столбец с информацией о должниках? Должниками считать тех, кто на момент окончания договора о займе не перевёл всю сумму займа

Буду премного благодарен!!!

Comments

[Akina]

1. Укажите точно СУБД. Включая версию.
   
2. Приведите пример данных. В виде INSERT INTO, не более 10 записей на таблицу.
   
3. Покажите требуемый результат. Для именно этих данных.
   
4. Не скупитесь на пояснения.
   

[mr_Try]

Akina,
1) PostgreSQL 13
2)

insert into customers values(1, 73, '2021-05-18');
insert into regions values(73, '4+');
insert into applications values(101, '2021-05-18', 1, '2021-05-28', 4000);
insert into payments values(101, '2021-05-28', 4000);

insert into customers values(2, 73, '2021-06-10');
insert into regions values(73, '4+');
insert into applications values(102, '2021-07-10', 2, '2021-08-01', 12000);
insert into applications values(1021, '2021-07-12', 2, '2021-08-03', 12000);
insert into payments values(102, '2021-07-17', 4000);
insert into payments values(102, '2021-07-25', 4000);
insert into payments values(102, '2021-07-29', 4000);

insert into customers values(3, 73, '2021-07-11');
insert into regions values(73, '4+');
insert into applications values(103, '2021-07-11', 3, '2021-08-03', 7000);
insert into applications values(1031, '2021-07-11', 3, '2021-08-07', 12000);
insert into payments values(103, '2021-07-29', 4000);
insert into payments values(1031, '2021-07-31', 4000);

insert into customers values(4, 63, '2021-07-11');
insert into regions values(63, '4+');
insert into applications values(104, '2021-07-11', 4, '2021-08-11', 5000);

3) Примерно так


4) Вообще это просто упражнение с кривоватым условием специально на подумать, как ты сам разберёшься в схеме данных.

Как это понимаю я: в таблице customers хранятся данные о заёмщиках (id, код его региона и дата регистрации в системе); в таблице regions хранится код региона и часовой пояс; в таблице applications хранятся данные о номере займа, дате его получения, id заёмщика, предполагаемая дата уплаты займа (по условному договору о займе) и размер займа; в таблице payments хранятся данные о транзакциях заёмщика по уплате его долга (номер займа, дата взноса для уплаты займа и размер этого взноса).

Просроченный займ– это тот займ, на который не были переведены средства в полном объёме, т.е. если занял 12000, а сделал 2 перевода по 5000 и в БД нет данных о других переводах на этот займ, то его можно считать просроченным.

Ну и собственно надо получить выше указанную таблицу за период с 1.07.2021 по 15.07.2021, я не понимаю как уместить условие о просрочке в запросе

[Akina]

Счас немного не до того. Вот тебе для затравки - агрегаты, с которыми надо работать: fiddle. Минимальные пояснения там есть... тебе надо совместить график погашения с фактическими платежами.

[mr_Try]

Akina, сижу с самого утра, но так и не родил толкового решения. Если у вас будет время вечером на помощь с решением, то будет очень здорово

Answer 1

[rPman]

понять задание сложно, информации мало, нужны примеры данных с пояснениями

applications.back_date скорее всего содержит дату погашения задолжности, значит если она null то applications.customer_id ее будет ссылаться на должника? вот и считай эти count(applications.id) для group by applications.customer_id с условием applications.back_date is null

Comments

[mr_Try]

На самом деле, это просто упражнение, но задание не очень четко сформулировано, согласен, у меня даже данных для её решения нет, сам некоторые строки для проверки добавлял в онлайн-бд.
Я подумал, что applications.back_date – это предполагаемая дата погашения займа, т.е. если после неё остаётся некоторая непогашенная сумма, то она считается долгом, поэтому здесь ещё есть таблица payments. Скорее всего величина долга считается как

(applications.credit_sum - payments.payment * count(payments.payment))

и если это выражение больше 0, то это можно считать задолженностью, а количество посчитается с помощью count(). Но как раз в этом у меня и затуп, т.к. я не знаю как это выражение запихнуть в запрос.

Но запрос по вашей наводке тоже напишу, может я просто усложняю всё. Спасибо!

[rPman]

у тбя в одном результате будут несколько таблиц application, одна - чтобы получить первые две колонки, остальные, связанные по customer_id с group by, отдельно для количества задолжностей и количество невыплаченных задолжностей

[mr_Try]

rPman, можете на пальцах объяснить? У меня джуновский уровень sql, многого не знаю ещё