uncv_path_list.sql

-- Hive
SELECT user, TD_X_RANK(user) AS node_id, page_id, category, time
FROM
(
  SELECT
    raw_data.user, page_id, category, raw_data.time AS time
  FROM
  (
    SELECT user, page_id, category, time
    FROM raw_data_201501
    GROUP BY user, page_id, category, time
    HAVING TD_TIME_RANGE(time,'2015-01-01','2015-02-01')
  ) raw_data
  LEFT OUTER JOIN
  (
    SELECT user, cv_id, cv_category, cv_level, time
    FROM  itmd_conversion_history
    GROUP BY user, cv_id, cv_category, cv_level, time
    -- signupのみをコンバージョンとみなす --
    HAVING cv_category='signup'
    AND TD_TIME_RANGE(time,'2015-01-01','2015-02-01')
  ) cv_history
  ON
  ( raw_data.user=cv_history.user )
  -- conversion history に存在しなかったユーザーを絞り込み --
  WHERE cv_history.user IS NULL
  ORDER BY user, time
) t
WHERE user IS NOT NULL
ORDER BY user, time