Cart to purchase within 30 minutes

SQL data engineering interview problem. Difficulty: advanced. Pattern: Funnel. About 20 minutes. Part of the Pro drill bank.

web_events records view, cart and purchase events with user_id, step and event_ts. A funnel is a path of steps a user moves through. A user enters it at their first cart event. They convert if they have a purchase at or after that moment and no more than 30 minutes later. A purchase before the cart, or more than 30 minutes after it, does not count. A user with several purchases counts once. For each day on which users entered the funnel (the date of their first cart) return: cart_date cart_users: users who entered that day converted_users: how many of them converted conversion_rate: converted_users / cart_users, rounded to 2 decimals Order by cart_date.

Requirements

• Return the columns cart_date, cart_users, converted_users, conversion_rate.
• Order by cart_date.

Constraints

• step is one of view, cart, purchase.
• A user can have several carts and several purchases.
• The window includes exactly 30 minutes.

Examples

Input: web_events user_id | step | event_ts a | view | 2024-05-01 09:00:00 a | cart | 2024-05-01 09:05:00 a | purchase | 2024-05-01 09:35:00 b | cart | 2024-05-01 10:00:00 b | purchase | 2024-05-01 10:31:00 c | purchase | 2024-05-01 10:50:00 c | cart | 2024-05-01 11:00:00 d | view | 2024-05-01 11:30:00 e | cart | 2024-05-01 12:00:00 e | cart | 2024-05-01 12:20:00 e | purchase | 2024-05-01 12:45:00 f | cart | 2024-05-01 13:00:00 f | purchase | 2024-05-01 13:10:00 g | cart | 2024-05-02 09:00:00 g | purchase | 2024-05-02 09:05:00 g | purchase | 2024-05-02 09:10:00 h | cart | 2024-05-02 23:50:00 h | purchase | 2024-05-03 00:10:00 i | cart | 2024-05-02 15:00:00 Output: cart_date | cart_users | converted_users | conversion_rate 2024-05-01 | 5 | 2 | 0.4 2024-05-02 | 3 | 2 | 0.67 Why this passes: On 1 May 5 users entered: a (purchase exactly 30 minutes later) and f (10 minutes) converted; b is 31 minutes late, c bought before the cart and e bought 45 minutes after their first cart. On 2 May g converted, h converted across midnight and i never bought.

Input: web_events user_id | step | event_ts p | cart | 2024-06-01 10:00:00 p | purchase | 2024-06-01 10:05:00 p | purchase | 2024-06-01 10:06:00 q | cart | 2024-06-01 10:30:00 Output: cart_date | cart_users | converted_users | conversion_rate 2024-06-01 | 2 | 1 | 0.5 Why this passes: Two purchases by p count as one converted user. q never bought.

Topics: lakebench, sql, funnel, time window, conversion.

More SQL interview questions · All interview problems · Learn data engineering