SQLize | PHPize | SQLtest

------------------------подготовим таблицы------------------------------------ --менеджеры CREATE TABLE managers ( ID INTEGER PRIMARY KEY, --ID менеджера LOGIN VARCHAR2(15) NOT NULL, --Логин менеджера NAME VARCHAR2(15), --ФИО менеджера OFFICE INTEGER --ID офиса ); -- -- insert INSERT INTO managers VALUES(1,'hemp','Hemp A',4); INSERT INTO managers VALUES(2,'jool','Jolly A',5); INSERT INTO managers VALUES(3,'hobert','Hobert F',5); INSERT INTO managers VALUES(4,'frankjr','Gotty F',7); INSERT INTO managers VALUES(5,'aleensr','Allen D',1); INSERT INTO managers VALUES(6,'goofy','Goofy M',8); INSERT INTO managers VALUES(7,'duckduck','Muddy W',8); select * from managers; ----------------------------------------------------------- --продажи CREATE TABLE upsale_save ( ID INTEGER PRIMARY KEY, --ID предложения DT date NOT NULL, --Дата предложения MANAGER_ID INTEGER, --ID менеджера CLIENT_ID INTEGER, --ID клиента CALL_ID INTEGER, --ID звонка FACTOR_ID INTEGER, --ID фактора RESULT VARCHAR2(15) --‘yes’ / ‘no’ (cлучилась конверсия или нет) ); INSERT INTO upsale_save VALUES(1,to_date('01.02.2017','dd.MM.yyyy'),1,45,1,1,'yes'); INSERT INTO upsale_save VALUES(2,to_date('01.02.2017','dd.MM.yyyy'),3,67,2,1,'no'); INSERT INTO upsale_save VALUES(3,to_date('01.02.2017','dd.MM.yyyy'),7,123,3,2,'yes'); INSERT INTO upsale_save VALUES(4,to_date('01.02.2017','dd.MM.yyyy'),1,45,4,4,'no'); INSERT INTO upsale_save VALUES(5,to_date('02.02.2017','dd.MM.yyyy'),5,678,5,4,'yes'); INSERT INTO upsale_save VALUES(6,to_date('02.02.2017','dd.MM.yyyy'),4,890,6,2,'no'); INSERT INTO upsale_save VALUES(7,to_date('03.02.2017','dd.MM.yyyy'),2,123,7,3,'yes'); INSERT INTO upsale_save VALUES(8,to_date('03.02.2017','dd.MM.yyyy'),2,45,8,2,'no'); INSERT INTO upsale_save VALUES(9,to_date('03.02.2017','dd.MM.yyyy'),7,90,9,1,'yes'); INSERT INTO upsale_save VALUES(10,to_date('04.02.2017','dd.MM.yyyy'),1,893,10,4,'yes'); INSERT INTO upsale_save VALUES(11,to_date('04.02.2017','dd.MM.yyyy'),3,678,11,5,'no'); INSERT INTO upsale_save VALUES(12,to_date('04.02.2017','dd.MM.yyyy'),5,734,12,2,'yes'); SELECT * FROM upsale_save; -----------------------------2----------------- WITH a AS ( SELECT m.login AS login, COUNT(us.id) AS offers, --считаем кол-во предложений SUM (CASE WHEN us.result = 'yes' THEN 1 ELSE 0 END) as results --суммируем результаты переговоров FROM managers m LEFT JOIN upsale_save us on m.id = us.manager_id WHERE us.dt >= to_date('01.02.2017','dd.mm.yyyy') --фиксируем начало и конец периода and us.dt <= to_date('03.02.2017','dd.mm.yyyy') GROUP BY m.login) SELECT a.login, ROUND(a.results/a.offers,2) AS conversion from a; -----------------------------3----------------- WITH a AS ( SELECT m.login AS login, COUNT(us.id) AS offers, --считаем кол-во предложений SUM (CASE WHEN us.result = 'yes' THEN 1 ELSE 0 END) as results --суммируем результаты переговоров FROM managers m LEFT JOIN upsale_save us on m.id = us.manager_id WHERE us.dt = to_date('01.02.2017','dd.mm.yyyy') --указать дату GROUP BY m.login) SELECT a.login, ROUND(a.results/a.offers,2) AS conversion from a WHERE a.offers > 100; -----------------------------4----------------- WITH a AS ( SELECT m.office AS office, COUNT(us.id) AS offers, --считаем кол-во предложений SUM (CASE WHEN us.result = 'yes' THEN 1 ELSE 0 END) as results --суммируем результаты переговоров FROM managers m LEFT JOIN upsale_save us on m.id = us.manager_id GROUP BY m.office) SELECT a.office, ROUND(a.results/a.offers,2) AS conversion from a ORDER BY 2 desc; -----------------------------5----------------- WITH a AS( SELECT us.call_id, us.result, LAG(us.result) OVER(ORDER BY us.call_id) AS previous --извлекаем значение из предыдущей строки FROM upsale_save us) SELECT MIN( CASE WHEN a.result = 'yes' AND a.previous = 'no' THEN a.call_id ELSE null END) AS min_call_id from a; -----------------------------6----------------- WITH a AS( SELECT m.login, row_number() OVER(PARTITION BY us.manager_id ORDER BY us.call_id) as sort FROM managers m LEFT JOIN upsale_save us ON m.id = us.manager_id WHERE us.result='yes') select a.login, MIN(a.sort) from a ;