The Most Frequently Ordered Products for Each Customer

The Most Frequently Ordered Products for Each Customer - Problem

Database Medium

You're tasked with analyzing customer purchasing patterns in an e-commerce database. Given three tables - Customers, Orders, and Products - your job is to identify each customer's most frequently ordered products.

The Challenge: Some customers might have multiple products tied for "most frequently ordered" (same order count), and you need to return all of them. Only consider customers who have placed at least one order.

Tables Structure:

Customers: Contains customer information (customer_id, name)
Orders: Contains order details (order_id, order_date, customer_id, product_id)
Products: Contains product information (product_id, product_name, price)

Goal: Return a result set with customer_id, product_id, and product_name for each customer's most frequently ordered product(s).

Input & Output

example_1.sql — Basic Case

$ Input: Customers: [(1,'Alice'), (2,'Bob'), (3,'Charlie')]\nOrders: [(1,'2023-01-01',1,101), (2,'2023-01-02',1,101), (3,'2023-01-03',1,102), (4,'2023-01-04',2,101), (5,'2023-01-05',2,103), (6,'2023-01-06',2,103)]\nProducts: [(101,'Laptop'), (102,'Mouse'), (103,'Keyboard')]

› Output: [(1,101,'Laptop'), (2,103,'Keyboard')]

💡 Note: Customer 1 ordered Laptop twice and Mouse once, so Laptop is most frequent. Customer 2 ordered Laptop once and Keyboard twice, so Keyboard is most frequent.

example_2.sql — Tie Case

$ Input: Customers: [(1,'Alice'), (2,'Bob')]\nOrders: [(1,'2023-01-01',1,101), (2,'2023-01-02',1,102), (3,'2023-01-03',2,101), (4,'2023-01-04',2,102), (5,'2023-01-05',2,103)]\nProducts: [(101,'Laptop'), (102,'Mouse'), (103,'Keyboard')]

› Output: [(1,101,'Laptop'), (1,102,'Mouse'), (2,101,'Laptop'), (2,102,'Mouse'), (2,103,'Keyboard')]

💡 Note: Customer 1 has a tie between Laptop and Mouse (both ordered once). Customer 2 has a three-way tie (all products ordered once). All tied products are returned.

example_3.sql — Single Product

$ Input: Customers: [(1,'Alice'), (2,'Bob')]\nOrders: [(1,'2023-01-01',1,101), (2,'2023-01-02',1,101), (3,'2023-01-03',1,101), (4,'2023-01-04',2,102)]\nProducts: [(101,'Laptop'), (102,'Mouse')]

› Output: [(1,101,'Laptop'), (2,102,'Mouse')]

💡 Note: Customer 1 ordered only Laptop (3 times), Customer 2 ordered only Mouse (1 time). Each customer's most frequent product is their only product.

Visualization

Tap to expand

Understanding the Visualization

Count Orders

Count how many times each customer ordered each product

Partition by Customer

Group the data by customer to analyze each customer separately

Rank by Frequency

Within each customer group, rank products by order count (highest first)

Select Top Ranked

Filter to keep only products with rank = 1 (most frequent)

Key Takeaway

🎯 Key Insight: Window functions with PARTITION BY allow us to perform rankings within groups efficiently, making this the optimal solution for finding top items per category.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single pass to count orders plus sorting for window function ranking

⚡ Linearithmic

Space Complexity

O(n)

Storage for grouped counts and ranking results

⚡ Linearithmic Space

Constraints

1 ≤ customer_id ≤ 10⁴
1 ≤ product_id ≤ 10⁴
1 ≤ order_id ≤ 10⁵
No customer orders the same product more than once per day
All customer_id values in Orders exist in Customers table
All product_id values in Orders exist in Products table

Asked in

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

The optimal solution uses window functions with RANK() OVER (PARTITION BY customer_id ORDER BY COUNT(*) DESC) to efficiently rank products by order frequency within each customer group. This approach handles ties naturally and requires only a single pass through the data, making it the most performant solution.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Multiple Subqueries)	O(n² × m)	O(n × m)	Count orders for each customer-product pair, then find max count per customer
Window Functions (Optimal)	O(n log n)	O(n)	Use RANK() window function to rank products by order count within each customer group
Two-Pass CTE Approach	O(n × m)	O(n × m)	Use CTEs to count orders first, then find maximum counts per customer

Brute Force (Multiple Subqueries) — Algorithm Steps

Count orders for each customer-product combination
Find the maximum order count for each customer
Join these results to get products with maximum count
Include product names from Products table

Visualization

Tap to expand

Step-by-Step Walkthrough

Count Orders

First subquery counts orders per customer-product pair

Find Max Count

Second subquery finds maximum count per customer

Join Results

Join to get products with maximum count

Add Product Names

Final join with Products table

Code -

solution.c — C

// SQL solution using multiple subqueries
#include <stdio.h>

int main() {
    printf("%s\n", 
        "SELECT \n"
        "    c.customer_id,\n"
        "    c.product_id,\n"
        "    p.product_name\n"
        "FROM (\n"
        "    SELECT \n"
        "        customer_id,\n"
        "        product_id,\n"
        "        COUNT(*) as order_count\n"
        "    FROM Orders\n"
        "    GROUP BY customer_id, product_id\n"
        ") c\n"
        "JOIN (\n"
        "    SELECT \n"
        "        customer_id,\n"
        "        MAX(order_count) as max_count\n"
        "    FROM (\n"
        "        SELECT \n"
        "            customer_id,\n"
        "            product_id,\n"
        "            COUNT(*) as order_count\n"
        "        FROM Orders\n"
        "        GROUP BY customer_id, product_id\n"
        "    ) counts\n"
        "    GROUP BY customer_id\n"
        ") m ON c.customer_id = m.customer_id AND c.order_count = m.max_count\n"
        "JOIN Products p ON c.product_id = p.product_id\n"
        "ORDER BY c.customer_id, c.product_id;");
    
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n² × m)

Multiple passes through orders table (n) for each customer (m), with additional joins

⚠ Quadratic Growth

Space Complexity

O(n × m)

Temporary storage for all customer-product combinations and intermediate results

⚡ Linearithmic Space

Constraints

1 ≤ customer_id ≤ 10⁴
1 ≤ product_id ≤ 10⁴
1 ≤ order_id ≤ 10⁵
No customer orders the same product more than once per day
All customer_id values in Orders exist in Customers table
All product_id values in Orders exist in Products table

52.8K Views

High Frequency

~18 min Avg. Time

1.8K 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 Subqueries) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler