Customers who added items to their shopping cart and did not purchase

customersWhoDidNotPurchase.sql

-- Note: peak time is 14; we hardcorded because of the way the sample data is. Normally we would use current timestamp.

SELECT
    ca.CustomerID, ca.CustomerEmail, ca._event_time
FROM
    commons.CustomerActivity ca
WHERE
   NOT EXISTS (
        SELECT
            oi.CustomerID
        FROM
            commons.OrderItem oi
        WHERE
            ca.CustomerID= oi.CustomerID) and ca._event_time > ((PARSE_TIMESTAMP_ISO8601('2021-01-25T16:23:29.000000Z')) - SECONDS(:timeElapsed));