The Most Frequently Ordered Products For Each Customer - Leetcode Solution

Problem Statement:

You are given two tables: orders and products. The orders table contains information about each order made by customers while the products table contains information about the products sold by the company.

The orders table has the following columns:

• order_id: unique id of the order
• customer_id: id of the customer who placed the order
• product_id: id of the product that was ordered
• order_date: date on which the order was placed

The products table has the following columns:

• product_id: unique id of the product
• product_name: name of the product
• price: price of the product

Write a SQL query to find out the most frequently ordered product(s) for each customer. If there are ties, return all the products with the highest frequency.

Solution:

To find the most frequently ordered product(s) for each customer, we need to join the orders and products tables on the product_id column. We'll then group the resulting table by customer_id and product_name to get the frequency of each product ordered by each customer.

We can use the RANK() function to rank the products for each customer based on their frequency. We'll only include the products with a rank of 1, which are the most frequently ordered products. If there are ties, we'll include all the products with the highest frequency.

Here's the SQL query to solve the problem:

SELECT 
    customer_id, 
    product_name 
FROM 
    (
        SELECT 
            o.customer_id, 
            p.product_name, 
            COUNT(*) AS frequency,
            RANK() OVER (PARTITION BY o.customer_id ORDER BY COUNT(*) DESC) AS rnk
        FROM 
            orders o 
            JOIN products p ON o.product_id = p.product_id
        GROUP BY 
            o.customer_id, 
            p.product_name
    ) tmp
WHERE 
    rnk = 1
ORDER BY 
    customer_id

Explanation:

We start by joining the orders and products tables on the product_id column:

SELECT 
    o.customer_id, 
    p.product_name, 
    COUNT(*) AS frequency
FROM 
    orders o 
    JOIN products p ON o.product_id = p.product_id
GROUP BY 
    o.customer_id, 
    p.product_name

This query groups the resulting table by customer_id and product_name to get the frequency of each product ordered by each customer.

Next, we use the RANK() function to rank the products for each customer based on their frequency:

RANK() OVER (PARTITION BY o.customer_id ORDER BY COUNT(*) DESC) AS rnk

This query ranks the products for each customer based on their frequency in descending order. The PARTITION BY clause partitions the ranking by customer_id.

Finally, we select only the products with a rank of 1, which are the most frequently ordered products:

WHERE 
    rnk = 1

We order the resulting table by customer_id to get the most frequently ordered product(s) for each customer.

1