report_social

DROP TABLE SANDBOX_DEV.REPORT_SOCIAL_01
;

DROP TABLE SANDBOX_DEV.REPORT_SOCIAL_02
;

DROP TABLE SANDBOX_DEV.REPORT_SOCIAL_03
;

DROP TABLE SANDBOX_DEV.REPORT_SOCIAL_04
;

DROP TABLE SANDBOX_DEV.REPORT_SOCIAL_05
;

DROP TABLE SANDBOX_DEV.REPORT_SOCIAL
;

CREATE TABLE SANDBOX_DEV.REPORT_SOCIAL_01
AS
SELECT hash_msisdn AS h, COUNT(DISTINCT id) AS count_vk FROM SANDBOX_DEV.USER_PROFILES
  GROUP BY hash_msisdn
;

CREATE TABLE SANDBOX_DEV.REPORT_SOCIAL_02
AS
SELECT hash_msisdn, count_vk, id, attribute_val, attribute_nm FROM SANDBOX_DEV.REPORT_SOCIAL_01 JOIN SANDBOX_DEV.USER_PROFILES ON h = hash_msisdn
;

CREATE TABLE SANDBOX_DEV.REPORT_SOCIAL_03
AS
SELECT hash_msisdn, count_vk, 'VK' AS soc_name, MAX(id) AS soc_value FROM SANDBOX_DEV.REPORT_SOCIAL_02 WHERE count_vk > 1
GROUP BY hash_msisdn, count_vk
;

CREATE TABLE SANDBOX_DEV.REPORT_SOCIAL_04
AS
SELECT hash_msisdn, count_vk, attribute_nm AS soc_name, attribute_val AS soc_value FROM SANDBOX_DEV.REPORT_SOCIAL_02 WHERE count_vk = 1
GROUP BY hash_msisdn, count_vk, attribute_nm, attribute_val
UNION ALL
SELECT hash_msisdn, count_vk, soc_name, soc_value FROM SANDBOX_DEV.REPORT_SOCIAL_03
;

CREATE TABLE SANDBOX_DEV.REPORT_SOCIAL_05
AS
SELECT phone AS uid, count_vk, soc_name, soc_value FROM SANDBOX_DEV.REPORT_SOCIAL_04 JOIN secret.phones s ON hash_msisdn = phone_hash
;

CREATE TABLE SANDBOX_DEV.REPORT_SOCIAL
AS
SELECT uid, soc_name, soc_value, count_vk FROM SANDBOX_DEV.REPORT_SOCIAL_05
;