SQLize Online / PHPize Online  /  SQLtest Online

--https://sqlize.online/sql/oracle21/c3d60ebfbfdb2829afc312408113bf7c/ -- DROP -- DROP TABLE clients; -- DROP TABLE accounts; ------------------------ -- CREATE INSERT CREATE TABLE clients (client_id VARCHAR(1), FIO VARCHAR(255) , Region VARCHAR(50), account_num INT); INSERT INTO clients VALUES ('A','Иванов','Москва',111); INSERT INTO clients VALUES ('A','Иванов','Москва',222); INSERT INTO clients VALUES ('B','Петров','Иваново',333); INSERT INTO clients VALUES ('C','Сидоров','Москва',444); INSERT INTO clients VALUES ('D','Новиков','Москва',555); INSERT INTO clients VALUES ('E','Шварценеггер','Лос-Анджелес',666); INSERT INTO clients VALUES ('D','Новиков','Москва',777); INSERT INTO clients VALUES ('F','Морозов','г.Москва',888); INSERT INTO clients VALUES ('G','Цветков','Москва',999); INSERT INTO clients VALUES ('G','Цветков','Москва',1111); INSERT INTO clients VALUES ('H','Шубин','Монтевидео',2222); INSERT INTO clients VALUES ('K','Крюков','Москва',3333); CREATE TABLE accounts (value_day varchar(25), Summa_USD int, Account_num INT); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-01-01',15000,111); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-02-01',10000,111) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-03-01',5000,222) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-03-01',30000,333) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-04-01',20000,444) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-01-01',200000,666); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-02-01',400000,666); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-03-01',200000,666); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-04-01',198000,666) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-01-01',5000,555); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-02-01',15000,555); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-03-01',10000,555) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-01-01',2000,777); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-03-01',20000,777); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-08-01',10000,777) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-02-01',10000,888); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-04-01',2000,888); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-07-01',20500,888) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-01-01',45000,999); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-02-01',545000,999); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-03-01',0,999) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-01-01',1000,1111); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-02-01',10000,1111); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-03-01',21000,1111); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-04-01',19500,1111) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-01-01',10000,2222); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-02-01',20000,2222); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-03-01',60000,2222) ; COMMIT; ------------------------ -- SELECT * FROM clients; -- SELECT * FROM accounts; ------------------------ ------------------------ -- Задание 1 -- Вывести список клиентов из Москвы с суммарными остатками по клиенту -- и проставить флаг превышения средств свыше 20 000 -- на последнюю дату. with acc_last_sum as ( select distinct Account_num, last_value(Summa_USD) over (partition by Account_num order by value_day rows between unbounded preceding and unbounded following) "last_sum" from accounts ) select FIO ,sum("last_sum") "сумм ост" ,case -- when sum("last_sum") > 20000 then true else false when sum("last_sum") > 20000 then 1 else 0 end "флаг" from clients cl join acc_last_sum ac on ac.Account_num = cl.Account_num where -- lower(Region) like '%м%с%к%' --ilike без в постгре например -- replace(Region, 'г.') like 'Москва' Region in ('Москва','г.Москва') group by FIO order by "флаг" desc ; Explain plan for with acc_last_sum2 as ( select Account_num ,max(value_day) "last_day" from accounts group by Account_num ) select FIO ,sum(Summa_USD) "сумм ост" ,case when sum(Summa_USD) > 20000 then 1 else 0 end "флаг" from acc_last_sum2 a2 join accounts ac on ac.Account_num = a2.Account_num and ac.value_day = a2."last_day" join clients cl on cl.Account_num = ac.Account_num where Region in ('Москва','г.Москва') group by FIO order by 3 desc