Notes

ALTER TABLE SANDBOX.DR_LTEUSIM_201610
DROP device,
DROP usim,
DROP attach,
DROP paired,
DROP sdn,
DROP quadrant;


DROP TABLE SANDBOX.LTE_MAP_201610;

CREATE TABLE SANDBOX.LTE_MAP_201610 AS (
SELECT DISTINCT msisdn FROM sandbox.de_cb_pre_post_201610
) WITH DATA;



ALTER TABLE SANDBOX.LTE_MAP_201610
ADD device VARCHAR(2),
ADD usim VARCHAR(2),
ADD attach VARCHAR(2),
ADD paired VARCHAR(2),
ADD sdn VARCHAR(2),
ADD quadrant VARCHAR(25);

--4g
UPDATE SANDBOX.LTE_MAP_201610 SET device = 'y'
WHERE msisdn IN (SELECT DISTINCT msisdn FROM sandbox.imei_handset_201610 WHERE data_capable='y' AND network='4G')
;

--usim
UPDATE SANDBOX.LTE_MAP_201610 SET usim = 'y' WHERE msisdn IN (
SELECT msisdn FROM sandbox.usim_reff_201610
WHERE msisdn IN (SELECT msisdn FROM SANDBOX.DE_CB_PRE_POST_201610 ) AND act_date BETWEEN '2014-12-01' AND '2016-10-31'
GROUP BY 1
);

--attach
UPDATE SANDBOX.LTE_MAP_201610 SET attach = 'y' WHERE msisdn IN (
SELECT msisdn FROM sandbox.data_user_201610 WHERE COALESCE(vol_4g,0) > 0
UNION
SELECT DISTINCT b.msisdn FROM (SELECT msisdn FROM sandbox.data_user_201610 WHERE COALESCE(vol_4g,0) > 0
UNION SELECT msisdn FROM sandbox.data_user_201610 WHERE msisdn IN (SELECT msisdn FROM sandbox.z_tmp_4g UNION SELECT msisdn FROM sandbox.z_tmp_4g_upcc)) a INNER JOIN sandbox.de_cb_pre_post_201610 b
ON a.msisdn = b.msisdn
);

--paired
UPDATE SANDBOX.LTE_MAP_201610 SET paired = 'y'
WHERE msisdn IN (SELECT DISTINCT msisdn FROM SANDBOX.LTE_MAP_201610 WHERE device='y' AND usim='y')
;

------------------- sampai siniiiiiiiiiiiiiiiiii
--sdn
UPDATE SANDBOX.LTE_MAP_201610 SET sdn = 'y'
WHERE msisdn IN (SELECT DISTINCT msisdn FROM SANDBOX.LTE_MAP_201610
WHERE (device='y' AND usim='y' AND attach='y') OR (usim='y' AND attach='y') OR (attach='y') OR (device='y' AND attach='y')
)
-- sama aja dengan attach ='y' but just make sure
;

--Quadrant
UPDATE SANDBOX.LTE_MAP_201610 SET quadrant = 'usim_device'
WHERE msisdn IN (SELECT DISTINCT msisdn FROM SANDBOX.LTE_MAP_201610
WHERE (paired='y' OR sdn='y')
)
;

UPDATE SANDBOX.LTE_MAP_201610 SET quadrant = 'usim_nondevice'
WHERE msisdn IN (SELECT DISTINCT msisdn FROM SANDBOX.LTE_MAP_201610
WHERE usim='y' AND device IS NULL AND attach IS NULL
)
;

UPDATE SANDBOX.LTE_MAP_201610 SET quadrant = 'nonusim_device'
WHERE msisdn IN (SELECT DISTINCT msisdn FROM SANDBOX.LTE_MAP_201610
WHERE device='y' AND usim IS NULL AND attach IS NULL
)
;

UPDATE SANDBOX.LTE_MAP_201610 SET quadrant = 'nonusim_non_device'
WHERE msisdn IN (SELECT DISTINCT msisdn FROM SANDBOX.LTE_MAP_201610
WHERE device=IS NULL AND usim IS NULL AND attach IS NULL
)
;