Interview Query

True Item Rate

Have you seen this question before?

You’re given a table of event data on an e-commerce site. Specifically the data that you’re analyzing is user purchasing behavior.

For example, when an item is viewed the website fires an item_viewed event and when the item is added to their cart an item_added_to_cart event fires.

A PM tells you that there’s a bug in the event tracking. Items that were not in stock, and could not be added to a user’s cart, showed up as listing in stock. The item_added_to_cart event would still then fire even though nothing would happen if the user tried to add the event to their cart.

events table

column type
id int
user_id int
created_at datetime
action string
url string
platform string

You don’t know the date that the item was listed in stock. But the PM wants you to figure out what the add-to-cart click-through-rate is for each item.

Write a query to determine the true click-through rate of each item.

Next question: Median probability
MySQL 8.0.17
Loading editor