The events table records what users do in an app. Each row has the user_id, the device the user was on, the step (view, cart or pay) and the time of the event. A user can use two devices, and each device is counted on its own: only the events of that device count for that device.
For one device, a user reaches the steps like this:
- step 1, viewed: the user has at least one
view event.
- step 2, carted: the user viewed, and has a
cart event later than their FIRST view event.
- step 3, paid: the user carted, and has a
pay event later than the EARLIEST cart that came after the first view.
"Later" means later by at least one second: an event at the same time does not count. A cart before the first view is ignored, but a cart after the first view still counts. For example, a user with a cart at 08:00, a view at 08:30 and a cart at 08:45 has carted, because of the 08:45 cart. So do not simply take the first cart of the user. A user counts once at each step, and a user who never viewed on the device is in no step, even if the user paid.
Return one row for each device in the table, also a device with no viewers (its numbers are 0): device, viewers, carted, paid (the numbers of users at the three steps), view_to_cart_pct (carted as a percentage of viewers) and cart_to_pay_pct (paid as a percentage of carted). Both percentages are rounded to 1 decimal. If the number you divide by is 0, the percentage is NULL. Sort by device from A to Z.
The order of the rows matters for this question.