SQLize Online / PHPize Online  /  SQLtest Online

------------------------подготовим таблицы------------------------------------ --менеджеры 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; -------------------------------------------- select'1.Отобразить сколько предложений сделал каждый из менеджеров за всю историю (в разрезе логина менеджера).' as title from dual; SELECT m.login as manager_login, count(u.id) as count_contacts from managers m left join upsale_save u on m.id = u.MANAGER_ID group by m.login order by 2 desc; -------------------------------------------- select'2.Отобразить конверсию предложений в подключения для менеджеров за указанный период (в разрезе логина менеджера).' as title from dual; with t as( SELECT m.login as manager_login, count(u.id) as count_contacts, sum(case when RESULT='yes' then 1 else 0 end) as count_sales from managers m left join upsale_save u on m.id = u.MANAGER_ID where 1=1 and u.dt >='01.feb.2017' --начало интересующего периода and u.dt <='02.feb.2017' --конец периода group by m.login ) select t.*, --добавил защиту от деления на 0 если нужно будет посмотреть конверсию по всем менеджерам без указания периода (т.к. есть менеджеры без звонков) case when t.count_contacts=0 then 0 else t.count_sales/t.count_contacts end as conversion from t; -------------------------------------------- select'3.Вывести менеджеров и их конверсии, если было принято больше 100 звонков на указанную дату (в разрезе логина менеджера).' as title from dual; with t as( SELECT m.login as manager_login, count(u.id) as count_contacts, sum(case when RESULT='yes' then 1 else 0 end) as count_sales from managers m left join upsale_save u on m.id = u.MANAGER_ID where 1=1 and u.dt ='04.feb.2017' --интересующая дата group by m.login ) select t.*, case when t.count_contacts=0 then 0 else t.count_sales/t.count_contacts end as conversion from t where t.count_contacts >100; -------------------------------------------- select'4.Вывести офисы, отсортированные в порядке убывания средней конверсии менеджеров из офиса за всю историю.' as title from dual; with t as( SELECT m.OFFICE as OFFICE, count(u.id) as count_contacts, sum(case when RESULT='yes' then 1 else 0 end) as count_sales from managers m left join upsale_save u on m.id = u.MANAGER_ID where 1=1 group by m.OFFICE ) select t.*, round(case when t.count_contacts=0 then 0 else t.count_sales/t.count_contacts end,2) as conversion from t order by 4 desc; -------------------------------------------- select'5.Вывести минимальный порядковый номер звонка клиента с RESULT = ‘yes’, перед которым был RESULT = ‘no’.' as title from dual; with t as( select u.*, lag(u.result) over(order by u.CALL_ID) as prev_result from upsale_save u ), t2 as ( select t.*, case when t.result = 'yes' and t.prev_result = 'no' then t.call_id else null end as yes_after_no_call_id from t ) select min(yes_after_no_call_id) as min_yes_after_no_call_id from t2 ; -------------------------------------------- select'6.Получить для каждого менеджера первое принятое предложение.' as title from dual; with t as( SELECT m.login as manager_login, u.*, row_number()over(partition by u.MANAGER_ID order by u.call_id) as manager_call_id_rn from managers m left join upsale_save u on m.id = u.MANAGER_ID where 1=1 and u.RESULT='yes' ) select * from t where manager_call_id_rn=1 ; -------------------------------------------- select'7.Предложить запрос, показывающий ранее не использованные операторы SQL.' as title from dual; select'Сделаем сводную по кол-ву контактов в разрезе предложений/оффисов используя PIVOT' as description from dual; with t as( SELECT m.OFFICE as OFFICE, u.FACTOR_ID, u.call_id from managers m left join upsale_save u on m.id = u.MANAGER_ID where 1=1 ) SELECT * FROM t PIVOT ( count(call_id) FOR office IN (1 as office_1, 4 as office_4,5 as office_5, 7 as office_7,8 as office_8) ) ORDER BY FACTOR_ID ; -------------------------------------------- select'8.Предложить запрос, который покажет интересную или полезную информацию из этих данных.' as title from dual; select'Сделав свод по менеджерам и предложениям можем оценить насколько широко менеджеры охватывают матрицу предложений (нет ли однобоких предложений из-за которых ряд позиций протухает на складах)' as description from dual; with t as( SELECT m.OFFICE as OFFICE, u.FACTOR_ID, u.call_id, --u.client_id, m.login as manager_login from managers m left join upsale_save u on m.id = u.MANAGER_ID where 1=1 ) SELECT * FROM t PIVOT ( count(call_id) FOR FACTOR_ID IN (1 as factor_1, 2 as factor_2, 3 as factor_3, 4 as factor_4, 5 as factor_5) ) ORDER BY manager_login ;