Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment