Дано

Untitled

Необязательно отвечать на все вопросы, можно выбрать 2.

  1. Получить уникальный состав заявок с их статусом в рамках расcмотрения которых подписаны договоры займов с суммой более 10000.
  2. Получить статистику выполненных заявок на заключение договоров займа за каждый день.
  3. Получить информацию о том, с какими страховщиками были заключены договора за месяц, если в рамках заявки оформлен и договор займа (т.е. страховка оплачивается за счет займа).
  4. Выявить, есть ли выполненные заявки, в рамках которых есть заключенные договоры займа, но займ еще не выдан.

Ответ

Получившиеся запросы:

Запрос 1: Получить уникальный состав заявок с их статусом в рамках рассмотрения которых подписаны договоры займов с суммой более 10000.

SELECT DISTINCT 
		a.application_id, 
		a.status
FROM application a
JOIN contract c ON a.application_id = c.application_id
WHERE c.type = 'LOAN'
	AND c.status = 'COMPLETED'
  AND CAST(c.detail ->> 'creditCost' AS DECIMAL) > 10000;

Я исхожу из того, что под составом заявки достаточно понимать её ID, но исправить на совершенно любой её вид не составляет никаких проблем, достаточно изменить вторую и третью строчки.

Запрос 2: Получить статистику выполненных заявок на заключение договоров займа за каждый день.

SELECT 
    DATE(c.concluded_at) AS contract_concluded_date,
    COUNT(*) AS completed_loans
FROM 
    contract c
WHERE 
    c.type = 'LOAN'
    AND c.status = 'CONCLUDED'
GROUP BY 
    contract_concluded_date
ORDER BY 
    contract_concluded_date;

Важный поинт - группировка и сортировка сделана на основе даты (вторая строчка), не даты и времени :)

Запрос 3: Получить информацию о том, с какими страховщиками были заключены договора за месяц, если в рамках заявки оформлен и договор займа.

SELECT DISTINCT c_ins.detail ->> 'insurer' AS insurer
FROM contract c_ins
JOIN contract c_loan ON c_ins.application_id = c_loan.application_id
WHERE c_ins.type = 'INSURANCE'
  AND c_loan.type = 'LOAN'
  AND DATE_TRUNC('month', c_ins.created_at) = DATE_TRUNC('month', CURRENT_DATE);
  AND DATE_TRUNC('year', c_ins.created_at) = DATE_TRUNC('year', CURRENT_DATE);

Важный момент - информация получается для текущего месяца. Поменять месяц, аналогично, не составляет никаких проблем - достаточно изменить последние две строчки.

Запрос 4: Выявить, есть ли выполненные заявки, в рамках которых есть заключенные договоры займа, но займ еще не выдан.

SELECT a.application_id
FROM application a
JOIN contract c ON a.application_id = c.application_id
WHERE a.status = 'COMPLETED'
  AND c.type = 'LOAN'
  AND c.status = 'CONCLUDED'
  AND c.detail ->> 'loanIssuedAt' IS NULL;

Важный момент - под “займ ещё не выдан” мы подразумеваем, что вместо даты выдачи займа стоит NULL, так как прямого статуса выдачи займа в данной структуре нет. Если предполагается другой критерий, нужно изменить последнюю строчку.

**

Проверено на сгенерированных данных и с использованием ресурса **https://onecompiler.com/postgresql/

Тестовые данные можно добавить при помощи запроса:

-- Создание типов перечислений
CREATE TYPE enum_application_status AS ENUM ('CREATED', 'WAIT_DECISION', 'SIGNING', 'COMPLETED');
CREATE TYPE enum_contract_status AS ENUM ('PREPARED', 'CONCLUDED', 'TERMINATED');
CREATE TYPE enum_contract_type AS ENUM ('LOAN', 'INSURANCE');

-- Создание таблицы application
CREATE TABLE application (
    application_id UUID PRIMARY KEY,
    created_at TIMESTAMP,
    status enum_application_status
);

-- Создание таблицы contract
CREATE TABLE contract (
    contract_id UUID PRIMARY KEY,
    application_id UUID REFERENCES application(application_id),
    created_at TIMESTAMP,
    concluded_at TIMESTAMP,
    status enum_contract_status,
    type enum_contract_type,
    detail JSONB
);

-- Вставка данных в таблицу application
INSERT INTO application (application_id, created_at, status) VALUES
('00000000-0000-4000-a000-000000000001', '2023-01-01 10:00:00', 'COMPLETED'),
('00000000-0000-4000-a000-000000000002', '2023-01-02 11:30:00', 'WAIT_DECISION'),
('00000000-0000-4000-a000-000000000003', '2023-01-03 09:15:00', 'SIGNING'),
('00000000-0000-4000-a000-000000000004', '2023-01-04 16:45:00', 'COMPLETED'),
('00000000-0000-4000-a000-000000000005', '2023-01-05 14:00:00', 'CREATED'),
('00000000-0000-4000-a000-000000000006', '2023-01-06 18:20:00', 'WAIT_DECISION'),
('00000000-0000-4000-a000-000000000007', '2023-01-07 08:00:00', 'SIGNING'),
('00000000-0000-4000-a000-000000000008', '2023-01-08 19:15:00', 'COMPLETED'),
('00000000-0000-4000-a000-000000000009', '2023-01-09 12:30:00', 'CREATED'),
('00000000-0000-4000-a000-000000000010', '2023-01-10 13:45:00', 'WAIT_DECISION'),
('00000000-0000-4000-a000-000000000011', '2023-01-11 18:00:00', 'SIGNING'),
('00000000-0000-4000-a000-000000000012', '2023-01-12 15:30:00', 'COMPLETED'),
('00000000-0000-4000-a000-000000000013', '2023-01-13 09:15:00', 'CREATED'),
('00000000-0000-4000-a000-000000000014', '2023-01-14 20:00:00', 'WAIT_DECISION'),
('00000000-0000-4000-a000-000000000015', '2023-01-15 07:30:00', 'SIGNING'),
('00000000-0000-4000-a000-000000000016', '2023-01-16 16:00:00', 'COMPLETED'),
('00000000-0000-4000-a000-000000000017', '2023-01-17 10:45:00', 'COMPLETED'),
('00000000-0000-4000-a000-000000000018', '2023-01-18 13:00:00', 'WAIT_DECISION'),
('00000000-0000-4000-a000-000000000019', '2023-01-19 18:45:00', 'SIGNING'),
('00000000-0000-4000-a000-000000000020', '2023-01-20 11:15:00', 'COMPLETED'),
('00000000-0000-4000-a000-000000000021', '2023-01-21 10:00:00', 'CREATED'),
('00000000-0000-4000-a000-000000000022', '2023-01-22 14:30:00', 'WAIT_DECISION'),
('00000000-0000-4000-a000-000000000023', '2023-01-23 09:00:00', 'SIGNING'),
('00000000-0000-4000-a000-000000000024', '2023-01-24 16:45:00', 'COMPLETED'),
('00000000-0000-4000-a000-000000000025', '2023-01-25 11:00:00', 'CREATED'),
('00000000-0000-4000-a000-000000000026', '2023-01-26 17:30:00', 'WAIT_DECISION'),
('00000000-0000-4000-a000-000000000027', '2023-01-27 08:00:00', 'SIGNING'),
('00000000-0000-4000-a000-000000000028', '2023-01-28 19:15:00', 'COMPLETED'),
('00000000-0000-4000-a000-000000000029', '2023-01-29 12:30:00', 'CREATED'),
('00000000-0000-4000-a000-000000000030', '2023-01-30 13:45:00', 'WAIT_DECISION');

-- Вставка данных в таблицу contract для договоров займа и страхования
INSERT INTO contract (contract_id, application_id, created_at, concluded_at, status, type, detail) VALUES
('10000000-0000-4000-a000-000000000001', '00000000-0000-4000-a000-000000000001', '2024-06-01 10:00:00', '2024-06-02 11:00:00', 'CONCLUDED', 'LOAN', '{"creditCost": 12000, "interestRate": 4.5, "loanIssuedAt": null, "numberOfPayments": 12}'),
('10000000-0000-4000-a000-000000000002', '00000000-0000-4000-a000-000000000001', '2024-06-05 14:30:00', '2024-06-06 15:00:00', 'CONCLUDED', 'INSURANCE', '{"insuranceAmount": 5000, "insurer": 12345}'),
('10000000-0000-4000-a000-000000000003', '00000000-0000-4000-a000-000000000003', '2024-07-10 09:00:00', '2024-07-11 10:00:00', 'CONCLUDED', 'LOAN', '{"creditCost": 15000, "interestRate": 5.0, "loanIssuedAt": "2024-02-12T09:00:00", "numberOfPayments": 10}'),
('10000000-0000-4000-a000-000000000004', '00000000-0000-4000-a000-000000000003', '2024-08-15 16:45:00', '2024-08-16 17:00:00', 'TERMINATED', 'INSURANCE', '{"insuranceAmount": 7000, "insurer": 67890}'),
('10000000-0000-4000-a000-000000000005', '00000000-0000-4000-a000-000000000005', '2024-09-20 11:00:00', '2024-09-21 12:00:00', 'PREPARED', 'LOAN', '{"creditCost": 20000, "interestRate": 4.0, "loanIssuedAt": "2024-02-22T11:00:00", "numberOfPayments": 20}'),
('10000000-0000-4000-a000-000000000006', '00000000-0000-4000-a000-000000000006', '2024-02-25 17:30:00', '2024-02-26 18:00:00', 'TERMINATED', 'INSURANCE', '{"insuranceAmount": 6000, "insurer": 23456}'),
('10000000-0000-4000-a000-000000000007', '00000000-0000-4000-a000-000000000007', '2024-03-01 08:00:00', '2024-03-02 09:00:00', 'CONCLUDED', 'LOAN', '{"creditCost": 18000, "interestRate": 3.5, "loanIssuedAt": "2024-03-03T08:00:00", "numberOfPayments": 15}'),
('10000000-0000-4000-a000-000000000008', '00000000-0000-4000-a000-000000000008', '2024-03-05 19:15:00', '2024-03-06 20:00:00', 'CONCLUDED', 'INSURANCE', '{"insuranceAmount": 8000, "insurer": 34567}'),
('10000000-0000-4000-a000-000000000009', '00000000-0000-4000-a000-000000000009', '2024-03-10 12:30:00', '2024-03-11 13:00:00', 'CONCLUDED', 'LOAN', '{"creditCost": 13000, "interestRate": 4.2, "loanIssuedAt": "2024-03-12T12:30:00", "numberOfPayments": 8}'),
('10000000-0000-4000-a000-000000000010', '00000000-0000-4000-a000-000000000010', '2024-03-15 13:45:00', '2024-03-16 14:00:00', 'PREPARED', 'INSURANCE', '{"insuranceAmount": 9000, "insurer": 45678}'),
('10000000-0000-4000-a000-000000000011', '00000000-0000-4000-a000-000000000011', '2024-03-20 18:00:00', '2024-03-21 18:30:00', 'CONCLUDED', 'LOAN', '{"creditCost": 22000, "interestRate": 3.8, "loanIssuedAt": "2024-03-22T18:00:00", "numberOfPayments": 18}'),
('10000000-0000-4000-a000-000000000012', '00000000-0000-4000-a000-000000000012', '2024-03-25 15:30:00', '2024-03-26 16:00:00', 'CONCLUDED', 'INSURANCE', '{"insuranceAmount": 10000, "insurer": 56789}'),
('10000000-0000-4000-a000-000000000013', '00000000-0000-4000-a000-000000000013', '2024-03-30 09:15:00', '2024-03-31 09:45:00', 'CONCLUDED', 'LOAN', '{"creditCost": 14000, "interestRate": 4.6, "loanIssuedAt": "2024-04-01T09:15:00", "numberOfPayments": 12}'),
('10000000-0000-4000-a000-000000000014', '00000000-0000-4000-a000-000000000014', '2024-04-04 20:00:00', '2024-04-05 20:30:00', 'CONCLUDED', 'INSURANCE', '{"insuranceAmount": 7000, "insurer": 67890}'),
('10000000-0000-4000-a000-000000000015', '00000000-0000-4000-a000-000000000015', '2024-04-08 07:30:00', '2024-04-09 08:00:00', 'CONCLUDED', 'LOAN', '{"creditCost": 16000, "interestRate": 3.7, "loanIssuedAt": null, "numberOfPayments": 10}'),
('10000000-0000-4000-a000-000000000016', '00000000-0000-4000-a000-000000000016', '2024-04-13 16:00:00', '2024-04-14 16:30:00', 'CONCLUDED', 'INSURANCE', '{"insuranceAmount": 11000, "insurer": 78901}'),
('10000000-0000-4000-a000-000000000017', '00000000-0000-4000-a000-000000000017', '2024-04-18 10:45:00', '2024-04-14 11:15:00', 'CONCLUDED', 'LOAN', '{"creditCost": 17500, "interestRate": 4.1, "loanIssuedAt": null, "numberOfPayments": 14}'),
('10000000-0000-4000-a000-000000000018', '00000000-0000-4000-a000-000000000018', '2024-04-23 13:00:00', '2024-04-14 13:30:00', 'CONCLUDED', 'INSURANCE', '{"insuranceAmount": 9500, "insurer": 89012}'),
('10000000-0000-4000-a000-000000000019', '00000000-0000-4000-a000-000000000019', '2024-04-28 18:45:00', '2024-04-14 19:15:00', 'CONCLUDED', 'LOAN', '{"creditCost": 19000, "interestRate": 3.9, "loanIssuedAt": "2024-04-30T18:45:00", "numberOfPayments": 16}'),
('10000000-0000-4000-a000-000000000020', '00000000-0000-4000-a000-000000000020', '2024-05-02 11:15:00', '2024-05-03 11:45:00', 'CONCLUDED', 'INSURANCE', '{"insuranceAmount": 10200, "insurer": 90123}');

Очень хотелось бы проверить на реальной базе, но это вряд ли входит в тестовое задание.