--Таблица остатков на счетах за дату. В таблице отражены даты изменения остатков.
--В промежуточные даты статок продляется до даты следующего изменения.
CREATE TABLE account_balance (
dt date,--дата
account_id numeric,--ИД счета
deal_id numeric,--ИД договора
balance numeric--остаток
);
insert into account_balance values ('2025-01-01'::date,10001,1001,0);
insert into account_balance values ('2025-01-15'::date,10001,1001,10000);
insert into account_balance values ('2025-01-17'::date,10001,1001,9000);
insert into account_balance values ('2025-01-20'::date,10001,1001,1000);
insert into account_balance values ('2025-01-01'::date,10002,1002,0);
insert into account_balance values ('2025-01-20'::date,10002,1002,70000);
insert into account_balance values ('2025-01-21'::date,10002,1002,13000);
insert into account_balance values ('2025-01-31'::date,10002,1002,4000);
insert into account_balance values ('2025-01-01'::date,10003,1003,0);
insert into account_balance values ('2025-01-05'::date,10003,1003,800);
insert into account_balance values ('2025-01-16'::date,10003,1003,23000);
insert into account_balance values ('2025-01-25'::date,10003,1003,3000);
insert into account_balance values ('2025-01-01'::date,10004,1001,500);
--Таблица счетов
CREATE TABLE account (
account_id numeric,--ИД счета
date_open date,--дата открытия счета
account_number varchar);--номер счета
insert into account values (10001,'2025-01-01'::date,'45201810000000010001');
insert into account values (10002,'2025-01-01'::date,'45201810000000010002');
insert into account values (10003,'2025-01-01'::date,'45201810000000010003');
insert into account values (10004,'2025-01-01'::date,'47427810000000010001');
--Таблица договоров
CREATE TABLE deal (
deal_id numeric,--ИД договора
deal_number varchar,--номер договора
date_open date,--дата начала действия договора
date_close date--дата окончания действия договора
);
insert into deal values (1001,'ВКЛ1-001/581','2025-01-01'::date,null);
insert into deal values (1002,'ВКЛ1-001/582','2025-01-01'::date,null);
insert into deal values (1003,'ВКЛ1-001/583','2025-01-01'::date,'2025-01-31'::date);
--Задание 1. Получить остатки по сделкам за 24.01.2025
with cte as
(
select *, row_number() over (partition by account_id order by dt desc) rn
from account_balance a
where a.dt <= '20250124'
)
select deal_id, sum(balance) balance
from cte
where rn = 1
group by deal_id;
--Задание 2. Получить подневные остатки без пробелов
with recursive calendar as
(
select '20250101'::date as dt
union all
select calendar.dt+1 as dt
from calendar
where calendar.dt+1 < '20250201'::date
)
,account_balance_next_dt as
(
select a.*, lead(a.dt,1,'99990101') over (partition by a.account_id order by a.dt) next_dt
from account_balance a
)
select c.dt as calendar_dt, a.*
from account_balance_next_dt a
left join calendar c on c.dt >= a.dt and c.dt < a.next_dt
