SQLize Online / PHPize Online  /  SQLtest Online

create table users( id serial, name text ); insert into users(name) values('Вася'),('Петя'); create table bank_props( id serial, user_id int, bik text, inn text, kpp text, individual_guid text, period timestamp, active boolean ); insert into bank_props(user_id, bik, inn, kpp, individual_guid, period, active) values (1, '123', '123', '123', '123', now() - interval '1 day', true), (1, '123', '123', '123', '123', now() - interval '3 day', true), (1, '123', '123', '123', '123', now() - interval '2 day', false), (2, '124', '124', '124', '124', now() - interval '1 day', true), (2, '124', '124', '124', '124', now() - interval '3 day', true), (2, '124', '124', '124', '124', now() - interval '2 day', false); create table settlements( id serial, user_id int, individual_guid text, period timestamp, status text ); insert into settlements(user_id, individual_guid, period, status) values (1, '123', now() - interval '1 day', 'COMPLETED'), (1, '124', now() - interval '2 day', 'COMPLETED'), (1, '125', now() - interval '3 day', 'NONCOMPLETED'), (2, '126', now() - interval '1 day', 'COMPLETED'), (2, '127', now() - interval '4 day', 'COMPLETED'), (2, '128', now() - interval '5 day', 'NONCOMPLETED'); select users.name, bank_props.bik, bank_props.inn, bank_props.kpp, bank_props.individual_guid, settlements.period from users join (select max(period) as date, user_id from bank_props where active = true group by user_id) as t_bank on(users.id = t_bank.user_id) join bank_props on t_bank.user_id = bank_props.user_id and bank_props.period = t_bank.date join (select max(period) as date, user_id from settlements where status = 'COMPLETED' group by user_id) as t_settlements on(users.id = t_settlements.user_id) join settlements on t_settlements.user_id = settlements.user_id and settlements.period = t_settlements.date;