Skip to content

task: #3521 #83

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 1 commit into from
Jul 25, 2025
Merged

task: #3521 #83

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
1 change: 1 addition & 0 deletions README.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -187,6 +187,7 @@ Useful for preparing for technical interviews and improving your SQL skills.
- [3421. Find Students Who Improved](./leetcode/medium/3421.%20Find%20Students%20Who%20Improved.sql)
- [3475. DNA Pattern Recognition](./leetcode/medium/3475.%20DNA%20Pattern%20Recognition.sql)
- [3497. Analyze Subscription Conversion](./leetcode/medium/3497.%20Analyze%20Subscription%20Conversion.sql)
- [3521. Find Product Recommendation Pairs](./leetcode/medium/3521.%20Find%20Product%20Recommendation%20Pairs.sql)
- [3564. Seasonal Sales Analysis](./leetcode/medium/3564.%20Seasonal%20Sales%20Analysis.sql)
- [3601. Find Drivers with Improved Fuel Efficiency](./leetcode/medium/3601.%20Find%20Drivers%20with%20Improved%20Fuel%20Efficiency.sql)
3. [Hard](./leetcode/hard/)
Expand Down
53 changes: 53 additions & 0 deletions leetcode/medium/3521. Find Product Recommendation Pairs.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,53 @@
/*
Question 3521. Find Product Recommendation Pairs
Link: https://leetcode.com/problems/find-product-recommendation-pairs/description/?envType=problem-list-v2&envId=database

Table: ProductPurchases

+-------------+------+
| Column Name | Type |
+-------------+------+
| user_id | int |
| product_id | int |
| quantity | int |
+-------------+------+
(user_id, product_id) is the unique key for this table.
Each row represents a purchase of a product by a user in a specific quantity.
Table: ProductInfo

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| product_id | int |
| category | varchar |
| price | decimal |
+-------------+---------+
product_id is the primary key for this table.
Each row assigns a category and price to a product.
Amazon wants to implement the Customers who bought this also bought... feature based on co-purchase patterns. Write a solution to :

Identify distinct product pairs frequently purchased together by the same customers (where product1_id < product2_id)
For each product pair, determine how many customers purchased both products
A product pair is considered for recommendation if at least 3 different customers have purchased both products.

Return the result table ordered by customer_count in descending order, and in case of a tie, by product1_id in ascending order, and then by product2_id in ascending order.
*/

SELECT
p1.product_id AS product1_id,
p2.product_id AS product2_id,
p1.category AS product1_category,
p2.category AS product2_category,
COUNT(DISTINCT pp1.user_id) AS customer_count
FROM ProductInfo AS p1
CROSS JOIN ProductInfo AS p2
RIGHT JOIN --noqa: CV08
ProductPurchases AS pp1
ON p1.product_id = pp1.product_id
RIGHT JOIN --noqa: CV08
ProductPurchases AS pp2
ON p2.product_id = pp2.product_id AND pp1.user_id = pp2.user_id
WHERE p1.product_id < p2.product_id
GROUP BY p1.product_id, p2.product_id, p1.category, p2.category
HAVING COUNT(DISTINCT pp1.user_id) >= 3 AND COUNT(DISTINCT pp2.user_id) >= 3
ORDER BY customer_count DESC, p1.product_id ASC, p2.product_id ASC