Отчёт для отдела маркетинга

report_for_marketing.sql

-- Создаем тестовые данные
CREATE TABLE clients (ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, NAME VARCHAR2(256));
INSERT INTO clients (NAME) VALUES ('ООО Экотранс');
INSERT INTO clients (NAME) VALUES ('ООО Аптека');
INSERT INTO clients (NAME) VALUES ('ИП Колесников');
INSERT INTO clients (NAME) VALUES ('ИП Яшин');
INSERT INTO clients (NAME) VALUES ('ИП Иванов');
INSERT INTO clients (NAME) VALUES ('ИП Нулевой');

CREATE TABLE kontakts (       ID NUMBER GENERATED ALWAYS AS IDENTITY START WITH 100,
                       CLIENT_ID NUMBER, -- FK CLIENT
                          C_TYPE NUMBER, -- Тип контакта 1-телефон 2-email
                          C_INFO VARCHAR2(128), -- Контакт – телефон либо адрес email
                         CREATED DATE, -- Дата внесения в базу
                          ACTIVE CHAR(1), -- Y/N активный или архив
                     FOREIGN KEY (CLIENT_ID)
                      REFERENCES clients (ID));
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(1,1,'+79110110101', SYSDATE,'y');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(1,1,'+79110110102', SYSDATE,'y');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(1,1,'+79110110103', SYSDATE-1,'y');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(1,2,'ekotrans@mail.com', SYSDATE,'y');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(1,2,'eko-trans@mail.com', SYSDATE,'y');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(1,1,'0000000000000', SYSDATE,'n');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(1,2,'ekotrans@', SYSDATE,'n');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(2,1,'+79010110111', SYSDATE-1,'y');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(2,2,'apteka1@mail.com', SYSDATE-2,'y');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(2,2,'apteka@mail.com', SYSDATE,'y');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(2,1,'790101101112', SYSDATE-2,'n');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(3,1,'+79111111101', SYSDATE,'y');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(3,1,'+79110110101', SYSDATE-1,'n');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(3,1,'0000000000000', SYSDATE-2,'n');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(3,2,'ip_kolesnikov@mail.ru', SYSDATE,'y');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(4,1,'+79211121101', SYSDATE,'y');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(4,1,'+79220110101', SYSDATE-2,'n');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(4,1,'0000000000000', SYSDATE-3,'n');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(4,2,'ip_ya@mail.ru', SYSDATE,'y');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(5,1,'+79110000001', SYSDATE,'y');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(5,1,'+79110110101', SYSDATE-1,'y');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(5,1,'0000000000000', SYSDATE-2,'n');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(5,2, NULL, SYSDATE-2,'y');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(6,1,'0000000000000', SYSDATE,'n');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(6,1,NULL, SYSDATE,'y');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(6,2, NULL, SYSDATE-2,'y');
INSERT INTO kontakts (CLIENT_ID,C_TYPE,C_INFO,CREATED,ACTIVE) VALUES(6,2, NULL, SYSDATE,'y');
                                          
CREATE TABLE address (       ID NUMBER GENERATED ALWAYS AS IDENTITY START WITH 200,
                      CLIENT_ID NUMBER,
                         A_TYPE NUMBER, -- Тип адреса 1-домашний 2-регистрации 3- фактический
                           CITY VARCHAR2(128), -- Город
                         STREET VARCHAR2(128), -- Улица
                          HOUSE VARCHAR2(8), -- Дом
                           FLAT VARCHAR2(8), -- Квартира
                        CREATED DATE,
                         ACTIVE CHAR(1), -- Y/N активный или архив
                    FOREIGN KEY (CLIENT_ID)
                     REFERENCES clients (ID));
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (1,1,'Москва','Октября','12','8',SYSDATE,'y');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (1,1,'Москва','Октября','12','8',SYSDATE,'y');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (1,2,'Москва','Октября','12','8',SYSDATE,'y');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (1,3,'Москва','Октября','12','8',SYSDATE,'y');  
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (1,3,'Москва','Октября','12','8',SYSDATE-1,'n');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (1,1,NULL,NULL,'12','8',SYSDATE,'y');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (1,2,'Москва',NULL,'12','8',SYSDATE,'y');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (1,3,NULL,NULL,'12',NULL,SYSDATE,'y');  
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (1,3,'Москва','Октября','12','8',SYSDATE,'n'); 
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (1,3,'Москва','Октября','12','8',SYSDATE-1,'n');                               
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (2,1,'Уфа','Ленина','1','56',SYSDATE,'y');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (2,2,'Санкт-Питербург','Авиаторов','15','732',SYSDATE,'y');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (2,3,'Москва','Новая','23','115',SYSDATE,'y');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (2,3,'Москва','Октября','23','115',SYSDATE,'y');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (2,3,'Москва','Новая','23','115',SYSDATE-1,'y');           
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (2,3,'Москва','Новая','0','0',SYSDATE,'n');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (3,1,'Уфа','Октября','40','73',SYSDATE,'y');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (3,2,'Москва','Ленина','3','603',SYSDATE,'y');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (3,2,'Москва','Октября','12','8',SYSDATE-1,'n');  
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (3,1,'Уфа',NULL,'40','73',SYSDATE,'n');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (3,2,NULL,'Ленина','3',NULL,SYSDATE,'n');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (3,2,NULL,'Октября','12','8',SYSDATE-1,'n'); 
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (3,3,'Москва','Ленина',NULL,'603',SYSDATE,'y'); 
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (4,1,'Стерлитамак','Октября','27','98',SYSDATE,'y');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (4,2,'Стерлитамак','Октября','27','98',SYSDATE,'y');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (4,2,'Стерлитамак','Октября','27','98',SYSDATE-1,'n');  
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (4,1,'Уфа',NULL,'40','73',SYSDATE,'n');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (4,2,NULL,'Октября','3',NULL,SYSDATE,'n');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (4,2,NULL,NULL,NULL,'8',SYSDATE,'y');                   
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (5,1,'Стерлитамак','Октября','27','98',SYSDATE-2,'y');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (5,2,'Стерлитамак','Октября','27','98',SYSDATE-2,'y');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (5,2,'Стерлитамак','Октября','27','98',SYSDATE-2,'n');  
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (5,1,'Уфа',NULL,'40','73',SYSDATE-3,'n');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (5,2,'Стерлитамак','Октября','30','100',SYSDATE,'y');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (5,2,NULL,'Октября','100',NULL,SYSDATE-2,'n');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (5,2,NULL,NULL,NULL,'100',SYSDATE-2,'y');   
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (5,3,NULL,NULL,NULL,'100',SYSDATE-2,'y'); 
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (5,3,'Санкт-Питербург','Ленина','40','700',SYSDATE,'y');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (6,1,NULL,NULL,NULL,'100',SYSDATE,'y');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (6,1,NULL,NULL,NULL,NULL,SYSDATE,'y'); 
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (6,2,NULL,NULL,NULL,NULL,SYSDATE,'y');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (6,3,'Санкт-Питербург',NULL,NULL,NULL,SYSDATE,'y');
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (6,1,'Санкт-Питербург',NULL,NULL,'100',SYSDATE,'y'); 
INSERT INTO address (CLIENT_ID,a_type,city,street,house,flat,created,active) VALUES (6,1,'Санкт-Питербург',NULL,'50','100',SYSDATE-1,'y'); 
                                         
-- Отчет для отела маркетинга
WITH adr AS ( 
              SELECT a.client_id,a.city, a.street, a.house, a.flat
                    ,row_number() OVER(PARTITION BY a.client_id 
                                           ORDER BY a.a_type DESC
                                                    ,(NVL2(a.city,1,0)+NVL2(a.street,1,0)+NVL2(a.house,1,0)+NVL2(a.flat,1,0))DESC
                                                    ,a.created DESC) AS rn
                FROM address a
               WHERE a.active = 'y'
            ),
     tel AS (
              SELECT k.client_id,k.c_info
                     ,row_number() OVER(PARTITION BY k.client_id ORDER BY k.created DESC) AS rn
                FROM kontakts k 
               WHERE k.c_type = 1
                 AND k.active = 'y'
            ),
     eml AS (
              SELECT k.client_id,k.c_info,
                     row_number() OVER(PARTITION BY k.client_id ORDER BY k.created) AS rn
                FROM kontakts k 
               WHERE k.c_type = 2
                 AND k.active = 'y'
            )         
     SELECT c.name AS "Клиент"
            ,adr.city||','||adr.street||','||adr.house||','||adr.flat AS "Адрес"
            ,tel.c_info AS "Телфон"
            ,eml.c_info AS "email"
       FROM clients c
       JOIN adr ON adr.client_id = c.id     
       JOIN tel ON tel.client_id = c.id
       JOIN eml ON eml.client_id = c.id
      WHERE adr.rn = 1 
        AND tel.rn = 1 
        AND eml.rn = 1
   ORDER BY c.id;

Имеется база данных по клиентам, имеющая структуру:

Отделу маркетинга требуется сводная выгрузка по клиентам, с гранулярностью до клиента, при этом для каждого клиента в выборке должны быть «лучшие»  адрес, телефон и адрес электронной почты. То есть, в результирующей выборке по каждому клиенту есть только одна строка. При этом:
1)	Лучший адрес отбирается по приоритету фактический > регистрации > домашний, при наличии нескольких адресов одного приоритета выбирается наиболее  полный (заполнено больше из перечня атрибутов city-street-house-flat, при равенстве по заполненности выбирается последний по дате внесения в базу.
2)	Лучший телефон это последний по дате внесения в базу
3)	Лучший email это первый по дате внесения в базу
4)	Данные по контактам и адресам – не архивные