unconversion_path_list.sql

-- Hive --
SELECT
  user,
  TD_FIRST(category, node_id) AS landing_category,
  TD_LAST (category, node_id) AS last_category,
  COLLECT_ALL( category )     AS category_set,
 
  TD_FIRST(page_id, node_id) AS landing_node,
  TD_LAST (page_id, node_id) AS last_node,
  COLLECT_ALL( page_id )     AS node_set,
 
  MAX(node_id) AS path_length,
  MIN(time) AS landing_time,
  MAX(time) AS last_time
FROM  (
  SELECT user, node_id, page_id, category, time FROM itmd_unconversion_path ORDER BY user, node_id
) itmd_unconversion_path
GROUP BY user
ORDER BY user