Skip to content

Instantly share code, notes, and snippets.

@nfarah86
Last active September 15, 2022 06:51
Show Gist options
  • Save nfarah86/27341fa3811cfc4bfec1fec930c8b743 to your computer and use it in GitHub Desktop.
Save nfarah86/27341fa3811cfc4bfec1fec930c8b743 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
), activity as(
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
)
SELECT
u.user_id as other_users,
u.purchased as otherItemsPurchased,
i.user_id as user_id,
i.product_id as productInterest
FROM
kafkaWorkshop.CustomerPurchaseActivities u,
activity i
WHERE
ARRAY_CONTAINS(u.purchased, i.product_id)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment