E-commerce Sessionization: Time Between Purchases
An e-commerce platform wants to understand customer purchasing behavior. They need to identify distinct shopping sessions for each customer and calculate the duration between the end of one session and the start of the next. A session is defined as a sequence of purchases where the time between any two consecutive purchases by the same customer is no more than 30 minutes.
Tables:
purchases(purchase_id INT PRIMARY KEY, customer_id INT, purchase_time TIMESTAMP, amount NUMERIC(10,2))
Return the following columns, ordered by customer_id and then by session_start_time:
customer_id: The ID of the customer.session_start_time: The timestamp of the first purchase in the session.session_end_time: The timestamp of the last purchase in the session.time_to_next_session: The time difference betweensession_end_timeof the current session andsession_start_timeof the next session for the same customer. If this is the last session for the customer, this value should be NULL.
Handle NULLs: amount can be NULL. Ties in customer_id and purchase_time are handled by the natural ordering of the purchase_id for tie-breaking within the same timestamp.
Empty results: If a customer has no purchases, they should not appear in the output. If a customer has only one purchase, they will have one session, and time_to_next_session will be NULL.
Tables
CREATE TABLE purchases (
purchase_id SERIAL PRIMARY KEY,
customer_id INT NOT NULL,
purchase_time TIMESTAMP NOT NULL,
amount NUMERIC(10,2)
);Example data
INSERT INTO purchases (purchase_id, customer_id, purchase_time, amount) VALUES (1, 101, '2023-01-01 10:00:00', 50.00), (2, 101, '2023-01-01 10:15:00', 75.00), (3, 101, '2023-01-01 10:45:00', 25.00), (4, 102, '2023-01-01 11:00:00', 100.00), (5, 101, '2023-01-01 11:30:00', 60.00), (6, 103, '2023-01-01 12:00:00', 30.00), (7, 102, '2023-01-01 12:10:00', 90.00), (8, 101, '2023-01-01 13:00:00', 55.00);
Your query is also checked on hidden datasets with different data.
Hints
-- Write your query here
SELECT
Your query runs on the example tables shown with the problem, then on hidden datasets when you submit.
Write standard SQL. MySQL habits work too: IFNULL, IF, YEAR(), DATE_FORMAT, GROUP_CONCAT, `backticks` and LIMIT 5, 10. It runs on PostgreSQL, so integer division keeps whole numbers (5 / 2 = 2): write 5.0 / 2 for 2.5.