customer_activity.sql

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