Skip to content

Instantly share code, notes, and snippets.

@nfarah86
Last active September 15, 2022 06:47
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save nfarah86/6fc62276e5d68a3b1b7ffe819a0f27d4 to your computer and use it in GitHub Desktop.
WITH lastest_purchase_time as (
SELECT
u.user_id,
_event_time,
u.purchased
FROM
kafkaWorkshop.CustomerPurchaseActivities u
WHERE
u.user_id = :userid
ORDER BY
_event_time desc
LIMIT
1
)
SELECT
u.product_id,
u._event_type,
u.user_id
FROM
lastest_purchase_time l,
kafkaWorkshop.CustomerClickActivities u
WHERE
l._event_time < u._event_time
AND TRY_CAST( u.user_id as string) = :userid
AND (
u._event_type = 'viewed'
or u._event_type = 'added_to_cart'
)
ORDER BY
u._event_time desc
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment