Add-to-Bag Conversion RateCompany: Apple | Difficulty: medium

Apple's Digital Store team measures how often a product page visit leads to the item being added to the shopping bag.

For each product_id that has at least one click, calculate the add-to-bag conversion rate:

A click is converted if the same user added the same product to their bag at a time strictly later than the click_time of that click.
The conversion rate is the number of converted clicks divided by the total number of clicks for that product, expressed as a decimal between 0 and 1 and rounded to 2 decimal places (for example, 0.33).
Every click is evaluated on its own, so a user who clicked a product several times contributes several clicks. Several qualifying bag adds for one click still make that click count only once.
Ignore any bag_adds row where add_time is NULL. Bag adds by users who never clicked the product do not affect the result.

Output product_id and conversion_rate, in that column order. Order the results by conversion_rate descending, then by product_id ascending to break ties.

Table Schema

Table	Column	Type
`clicks`	`click_id`	integer
`clicks`	`product_id`	integer
`clicks`	`user_id`	integer
`clicks`	`click_time`	timestamp
`bag_adds`	`add_id`	integer
`bag_adds`	`product_id`	integer
`bag_adds`	`user_id`	integer
`bag_adds`	`add_time`	timestamp

Example Input: `clicks`

click_id	product_id	user_id	click_time
1	5001	123	2025-06-08 09:00:00
2	6001	456	2025-06-10 14:30:00
3	5001	789	2025-06-18 11:15:00
4	5001	654	2025-07-05 20:45:00
5	6001	321	2025-07-26 08:10:00

Example Input: `bag_adds`

add_id	product_id	user_id	add_time
1	5001	123	2025-06-08 09:02:00
2	6001	456	2025-06-10 14:31:00
3	5001	789	2025-06-18 11:13:00
4	5001	985	2025-07-05 20:50:00

Example Output Explanation

Product 5001 has 3 clicks. Only the click by user 123 is converted, because that user added the product 2 minutes later. User 789 added the product before clicking, and user 654 never added it (user 985 did, but never clicked), so the rate is 1 / 3 = 0.33. Product 6001 has 2 clicks: user 456 added the product 1 minute after clicking, while user 321 did not, so the rate is 1 / 2 = 0.50. Sorting by conversion_rate descending places product 6001 before product 5001.

SchemaDROP TABLE IF EXISTS bag_adds; DROP TABLE IF EXISTS clicks; CREATE TABLE clicks (click_id INT, product_id INT, user_id INT, click_time TIMESTAMP); CREATE TABLE bag_adds (add_id INT, product_id INT, user_id INT, add_time TIMESTAMP); INSERT INTO clicks VALUES (1, 5001, 123, '2025-06-08 09:00:00'), (2, 6001, 456, '2025-06-10 14:30:00'), (3, 5001, 789, '2025-06-18 11:15:00'), (4, 5001, 654, '2025-07-05 20:45:00'), (5, 6001, 321, '2025-07-26 08:10:00'); INSERT INTO bag_adds VALUES (1, 5001, 123, '2025-06-08 09:02:00'), (2, 6001, 456, '2025-06-10 14:31:00'), (3, 5001, 789, '2025-06-18 11:13:00'), (4, 5001, 985, '2025-07-05 20:50:00');← All SQL Challenges · Apple Questions · Learn SQL Free