get_conversion_path.sql

SELECT 
  td_client_id,
  cv_id,
  ROW_NUMBER( ) OVER( PARTITION BY td_client_id, cv_id ORDER BY time ) AS node_id,
  cv_flag, td_title, time, cv_time
FROM
(
  SELECT
    td_client_id, cv_id, cv_flag, td_title, time, cv_time
  FROM
  (
    SELECT
      td_client_id, td_title, time,
      MIN(cv_id) AS cv_id,
      MIN(cv_time) AS cv_time,
      IF((MIN(cv_time)-time)=0,1,0) AS cv_flag
    FROM
    (
      SELECT /*+ MAPJOIN(cv_history) */
        raw_data.td_client_id, td_title, cv_id, raw_data.time AS time, cv_history.time AS cv_time
      FROM
      (
        SELECT td_client_id, td_title, time
        FROM treasuredata_homepage
      ) raw_data
      JOIN
      (
        SELECT td_client_id, cv_id, time
        FROM  itmd_conversion_history
      ) cv_history
      ON
      ( raw_data.td_client_id=cv_history.td_client_id )
      WHERE raw_data.time <= cv_history.time
    ) t1
    GROUP BY td_client_id, td_title, time
    ORDER BY td_client_id, time
  ) t2
  ORDER BY td_client_id, cv_id, time
)t3
ORDER BY td_client_id, cv_id, node_id