catalina_id_mapping_fpid.sql

-- INSERT INTO id_mapping -- Presto
With ssuid_table as 
(
select 
  fp_id
 ,max(ssuid) as ssuid
from tmp_id_mapping
group by fp_id
), 
 cluid_table as 
(
select 
  fp_id
 ,max(cluid) as cluid
from tmp_id_mapping
group by fp_id
),
 tmp_id_mapping as
(
  SELECT 
    (CASE WHEN b.ssuid is NULL and b.referrer_ssuid is not NULL THEN b.referrer_ssuid ELSE b.ssuid END) as ssuid
    ,b.fp_id as fp_id
    ,b.cluid as cluid
    ,b.td_global_id as td_global_id
    ,b.referrer_ssuid as referrer_ssuid
  FROM
  (
    SELECT 
       a.ssuid as ssuid
      ,a.fp_id as fp_id
      ,a.cluid as cluid
      ,a.td_global_id as td_global_id
      ,a.referrer_ssuid as referrer_ssuid
    FROM
    (
      SELECT 
         ssuid
        ,fp_id
        ,cluid
        ,td_global_id
        ,replace(regexp_extract(url_extract_query(td_referrer),'ssuid=\d+'),'ssuid=','') as referrer_ssuid -- presto
        ,parse_url(td_referrer, 'QUERY', 'ssuid') as referrer_ssuid -- Hive 
        ,count(1)
      FROM
       catalina_glico
      GROUP BY 
         1,2,3,4,5
      ) as a 
  ) as b

)

INSERT INTO TABLE id_mapping -- Hive
SELECT
   a.fp_id as fp_id
  ,a.cluid as cluid
  ,b.ssuid as ssuid
FROM 
  cluid_table a
JOIN
  ssuid_table b
ON a.fp_id= b.fp_id
ORDER BY 1,2,3