Apr-2-1

CREATE TABLE sandbox.yurbasov_apr_v2_1
-- Вычислить ID района для списка человек. (ответ вида: Вешняки)
AS
WITH tsk AS (
  SELECT CAST(phone AS BIGINT) AS k, hash AS h FROM sandbox.yurbasov_lexqueryh
), rich1 AS (
  SELECT t.k AS UID, '(null)' AS DISTRICT_ID,
  m.start_distrcit_name AS DISTRICT_NAME, m.sourcename AS TYPE,
  1 AS MAIN_START, 0 AS MAIN_END, 0 AS MAIN_INTERIM,
  1 AS SECONDARY_START, 0 AS SECONDARY_END, 0 AS SECONDARY_INTERIM FROM tsk t LEFT JOIN sandbox.mgtpart1 m ON m.hash = t.h
), res1 AS (
  SELECT UID, DISTRICT_ID, DISTRICT_NAME, TYPE, SUM(MAIN_START) AS MAIN_START, 0 AS MAIN_END, 0 AS MAIN_INTERIM,
  SUM(SECONDARY_START) AS SECONDARY_START, 0 AS SECONDARY_END, 0 AS SECONDARY_INTERIM FROM rich1 GROUP BY UID, DISTRICT_ID, DISTRICT_NAME, TYPE
), rich2 AS (
  SELECT t.k AS UID, '(null)' AS DISTRICT_ID,
  m.end_distrcit_name AS DISTRICT_NAME, m.sourcename AS TYPE,
  0 AS MAIN_START, 1 AS MAIN_END, 0 AS MAIN_INTERIM,
  0 AS SECONDARY_START, 1 AS SECONDARY_END, 0 AS SECONDARY_INTERIM FROM tsk t LEFT JOIN sandbox.mgtpart1 m ON m.hash = t.h
), res2 AS (
  SELECT UID, DISTRICT_ID, DISTRICT_NAME, TYPE, 0 AS MAIN_START, SUM(MAIN_END) AS MAIN_END, 0 AS MAIN_INTERIM,
  0 AS SECONDARY_START, SUM(SECONDARY_END) AS SECONDARY_END, 0 AS SECONDARY_INTERIM FROM rich2 GROUP BY UID, DISTRICT_ID, DISTRICT_NAME, TYPE
), res AS (
  SELECT UID, DISTRICT_ID, DISTRICT_NAME, TYPE, MAIN_START, MAIN_END, MAIN_INTERIM,
  SECONDARY_START, SECONDARY_END, SECONDARY_INTERIM FROM rich1
UNION ALL
  SELECT UID, DISTRICT_ID, DISTRICT_NAME, TYPE, SUM(MAIN_START) AS MAIN_START, MAIN_END, MAIN_INTERIM,
  SECONDARY_START, SECONDARY_END, SECONDARY_INTERIM FROM rich2
) SELECT UID, DISTRICT_ID, DISTRICT_NAME, TYPE,
  SUM(MAIN_START) AS MAIN_START, SUM(MAIN_END) AS MAIN_END, SUM(MAIN_START) + SUM(MAIN_END) AS MAIN_INTERIM,
  SUM(SECONDARY_START) AS SECONDARY_START, SUM(SECONDARY_END) AS SECONDARY_END, SUM(SECONDARY_START) + SUM(SECONDARY_END)
 AS SECONDARY_INTERIM FROM res GROUP BY UID, DISTRICT_ID, DISTRICT_NAME, TYPE