Find Product Recommendation Pairs - Practice Coding Problems

Find Product Recommendation Pairs - Problem

Database Medium

Amazon's "Customers who bought this item also bought" feature is one of the most powerful recommendation systems in e-commerce! 🛒

You're tasked with building the core algorithm that identifies product pairs that are frequently purchased together by the same customers. This classic market basket analysis problem helps Amazon suggest relevant products and increase sales.

Given:

ProductPurchases table: Contains user purchases with (user_id, product_id, quantity)
ProductInfo table: Contains product details with (product_id, category, price)

Goal: Find all product pairs where at least 3 different customers have purchased both products. For each qualifying pair, count how many customers bought both items.

Output Requirements:

Return pairs as (product1_id, product2_id) where product1_id < product2_id
Include the customer count for each pair
Sort by customer_count DESC, then product1_id ASC, then product2_id ASC

Input & Output

example_1.sql — Basic Product Pairs

$ Input: ProductPurchases: | user_id | product_id | quantity | |---------|------------|-----------| | 1 | 101 | 2 | | 1 | 102 | 1 | | 2 | 101 | 1 | | 2 | 102 | 3 | | 3 | 101 | 1 | | 3 | 102 | 2 | | 4 | 103 | 1 | ProductInfo: | product_id | category | price | |------------|-------------|-------| | 101 | Electronics | 299.99| | 102 | Electronics | 149.99| | 103 | Books | 29.99 |

› Output: | product1_id | product2_id | customer_count | |-------------|-------------|----------------| | 101 | 102 | 3 |

💡 Note: Products 101 and 102 were purchased together by users 1, 2, and 3 (exactly 3 customers), so this pair qualifies. Product 103 was only bought by user 4, so it doesn't form any qualifying pairs.

example_2.sql — Multiple Qualifying Pairs

$ Input: ProductPurchases: | user_id | product_id | quantity | |---------|------------|-----------| | 1 | 101 | 1 | | 1 | 102 | 1 | | 1 | 103 | 2 | | 2 | 101 | 2 | | 2 | 102 | 1 | | 3 | 101 | 1 | | 3 | 102 | 3 | | 4 | 101 | 1 | | 4 | 103 | 1 | | 5 | 102 | 2 | | 5 | 103 | 1 |

› Output: | product1_id | product2_id | customer_count | |-------------|-------------|----------------| | 101 | 102 | 3 | | 101 | 103 | 2 | | 102 | 103 | 2 |

💡 Note: Only pair (101,102) qualifies with 3 customers (users 1,2,3). Pairs (101,103) and (102,103) each have only 2 customers, falling short of the 3-customer minimum requirement.

example_3.sql — Edge Case with Sorting

$ Input: ProductPurchases: | user_id | product_id | quantity | |---------|------------|-----------| | 1 | 201 | 1 | | 1 | 202 | 1 | | 1 | 301 | 1 | | 2 | 201 | 2 | | 2 | 202 | 1 | | 2 | 301 | 2 | | 3 | 201 | 1 | | 3 | 202 | 1 | | 3 | 301 | 1 | | 4 | 201 | 1 | | 4 | 301 | 1 | | 5 | 201 | 2 | | 5 | 301 | 1 |

› Output: | product1_id | product2_id | customer_count | |-------------|-------------|----------------| | 201 | 301 | 5 | | 201 | 202 | 3 |

💡 Note: Both pairs qualify, but (201,301) has higher customer count (5) so appears first. When counts are equal, we'd sort by product1_id ASC, then product2_id ASC. Here (201,301) comes before (201,202) due to higher customer count.

Visualization

Tap to expand

Understanding the Visualization

Collect Shopping Data

Gather all customer purchase records with user IDs and product IDs

Generate Product Pairs

For each customer, create all possible product combinations from their purchases

Count Co-purchases

Group identical product pairs and count how many different customers bought both

Filter & Rank

Keep pairs with 3+ customers and rank by popularity for recommendations

Key Takeaway

🎯 Key Insight: Self-join approach efficiently generates product pairs per customer and leverages SQL's GROUP BY to count co-purchases, making it perfect for real-world recommendation systems like Amazon's 'customers who bought this also bought' feature.

Time & Space Complexity

Time Complexity

⏱️

O(N×P_avg²)

N customers, P_avg² for product pairs per customer on average

✓ Linear Growth

Space Complexity

O(P²)

Storage for unique product pairs in result

✓ Linear Space

Constraints

1 ≤ number of users ≤ 10⁴
1 ≤ number of products ≤ 10³
1 ≤ quantity ≤ 100
At least 3 different customers must purchase both products for a pair to qualify
Product pairs must satisfy product1_id < product2_id condition

Asked in

a Amazon 45 G Google 38 f Meta 32 ⊞ Microsoft 28 N Netflix 25

The optimal approach uses a self-join on ProductPurchases table joined on user_id to generate product pairs, filters for product1_id < product2_id, groups by product pairs, and counts customers. This leverages SQL's native optimization and handles the co-purchase requirement efficiently in O(N×P_avg²) time complexity.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Multiple Nested Queries)	O(P²×N)	O(P²)	Check each product pair individually with separate queries
Self-Join with Aggregation (Optimal)	O(N×P_avg²)	O(P²)	Use self-join to generate product pairs and count customers in one efficient query

Brute Force (Multiple Nested Queries) — Algorithm Steps

Get all unique product IDs from purchases
Generate all possible pairs where product1_id < product2_id
For each pair, count customers who bought both products
Filter pairs with at least 3 customers
Sort results by customer count and product IDs

Visualization

Tap to expand

Step-by-Step Walkthrough

Generate All Pairs

Create all possible combinations of products where product1_id < product2_id

Check Each Pair

For each pair, scan all purchases to count customers who bought both

Filter Results

Keep only pairs with 3+ customers and sort by criteria

Code -

solution.c — C

// Brute Force SQL Solution
#include <stdio.h>
#include <string.h>

char* find_product_pairs_brute_force() {
    static char query[] = 
        "SELECT "
        "    p1.product_id as product1_id,"
        "    p2.product_id as product2_id,"
        "    COUNT(DISTINCT pp1.user_id) as customer_count"
        "FROM "
        "    ProductInfo p1,"
        "    ProductInfo p2,"
        "    ProductPurchases pp1,"
        "    ProductPurchases pp2"
        "WHERE "
        "    p1.product_id < p2.product_id"
        "    AND pp1.product_id = p1.product_id"
        "    AND pp2.product_id = p2.product_id"
        "    AND pp1.user_id = pp2.user_id"
        "GROUP BY "
        "    p1.product_id, p2.product_id"
        "HAVING "
        "    COUNT(DISTINCT pp1.user_id) >= 3"
        "ORDER BY "
        "    customer_count DESC,"
        "    product1_id ASC,"
        "    product2_id ASC";
    return query;
}

Time & Space Complexity

Time Complexity

⏱️

O(P²×N)

P² for all product pairs, N for scanning purchases per pair

✓ Linear Growth

Space Complexity

O(P²)

Storage for all possible product pairs

✓ Linear Space

Constraints

1 ≤ number of users ≤ 10⁴
1 ≤ number of products ≤ 10³
1 ≤ quantity ≤ 100
At least 3 different customers must purchase both products for a pair to qualify
Product pairs must satisfy product1_id < product2_id condition

34.2K Views

High Frequency

~25 min Avg. Time

1.5K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Brute Force (Multiple Nested Queries) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler