1596-TheMostFrequentlyOrderedProductsForEachCustomer.sql

-- 1596. The Most Frequently Ordered Products for Each Customer
-- Table: Customers
-- +---------------+---------+
-- | Column Name   | Type    |
-- +---------------+---------+
-- | customer_id   | int     |
-- | name          | varchar |
-- +---------------+---------+
-- customer_id is the column with unique values for this table.
-- This table contains information about the customers.
 
-- Table: Orders
-- +---------------+---------+
-- | Column Name   | Type    |
-- +---------------+---------+
-- | order_id      | int     |
-- | order_date    | date    |
-- | customer_id   | int     |
-- | product_id    | int     |
-- +---------------+---------+
-- order_id is the column with unique values for this table.
-- This table contains information about the orders made by customer_id.
-- No customer will order the same product more than once in a single day.
 
-- Table: Products
-- +---------------+---------+
-- | Column Name   | Type    |
-- +---------------+---------+
-- | product_id    | int     |
-- | product_name  | varchar |
-- | price         | int     |
-- +---------------+---------+
-- product_id is the column with unique values for this table.
-- This table contains information about the products.
 
-- Write a solution to find the most frequently ordered product(s) for each customer.
-- The result table should have the product_id and product_name for each customer_id who ordered at least one order.
-- Return the result table in any order.
-- The result format is in the following example.

-- Example 1:
-- Input: 
-- Customers table:
-- +-------------+-------+
-- | customer_id | name  |
-- +-------------+-------+
-- | 1           | Alice |
-- | 2           | Bob   |
-- | 3           | Tom   |
-- | 4           | Jerry |
-- | 5           | John  |
-- +-------------+-------+
-- Orders table:
-- +----------+------------+-------------+------------+
-- | order_id | order_date | customer_id | product_id |
-- +----------+------------+-------------+------------+
-- | 1        | 2020-07-31 | 1           | 1          |
-- | 2        | 2020-07-30 | 2           | 2          |
-- | 3        | 2020-08-29 | 3           | 3          |
-- | 4        | 2020-07-29 | 4           | 1          |
-- | 5        | 2020-06-10 | 1           | 2          |
-- | 6        | 2020-08-01 | 2           | 1          |
-- | 7        | 2020-08-01 | 3           | 3          |
-- | 8        | 2020-08-03 | 1           | 2          |
-- | 9        | 2020-08-07 | 2           | 3          |
-- | 10       | 2020-07-15 | 1           | 2          |
-- +----------+------------+-------------+------------+
-- Products table:
-- +------------+--------------+-------+
-- | product_id | product_name | price |
-- +------------+--------------+-------+
-- | 1          | keyboard     | 120   |
-- | 2          | mouse        | 80    |
-- | 3          | screen       | 600   |
-- | 4          | hard disk    | 450   |
-- +------------+--------------+-------+
-- Output: 
-- +-------------+------------+--------------+
-- | customer_id | product_id | product_name |
-- +-------------+------------+--------------+
-- | 1           | 2          | mouse        |
-- | 2           | 1          | keyboard     |
-- | 2           | 2          | mouse        |
-- | 2           | 3          | screen       |
-- | 3           | 3          | screen       |
-- | 4           | 1          | keyboard     |
-- +-------------+------------+--------------+
-- Explanation: 
-- Alice (customer 1) ordered the mouse three times and the keyboard one time, so the mouse is the most frequently ordered product for them.
-- Bob (customer 2) ordered the keyboard, the mouse, and the screen one time, so those are the most frequently ordered products for them.
-- Tom (customer 3) only ordered the screen (two times), so that is the most frequently ordered product for them.
-- Jerry (customer 4) only ordered the keyboard (one time), so that is the most frequently ordered product for them.
-- John (customer 5) did not order anything, so we do not include them in the result table.

-- Create table If Not Exists Customers (customer_id int, name varchar(10))
-- Create table If Not Exists Orders (order_id int, order_date date, customer_id int, product_id int)
-- Create table If Not Exists Products (product_id int, product_name varchar(20), price int)
-- Truncate table Customers
-- insert into Customers (customer_id, name) values ('1', 'Alice')
-- insert into Customers (customer_id, name) values ('2', 'Bob')
-- insert into Customers (customer_id, name) values ('3', 'Tom')
-- insert into Customers (customer_id, name) values ('4', 'Jerry')
-- insert into Customers (customer_id, name) values ('5', 'John')
-- Truncate table Orders
-- insert into Orders (order_id, order_date, customer_id, product_id) values ('1', '2020-07-31', '1', '1')
-- insert into Orders (order_id, order_date, customer_id, product_id) values ('2', '2020-7-30', '2', '2')
-- insert into Orders (order_id, order_date, customer_id, product_id) values ('3', '2020-08-29', '3', '3')
-- insert into Orders (order_id, order_date, customer_id, product_id) values ('4', '2020-07-29', '4', '1')
-- insert into Orders (order_id, order_date, customer_id, product_id) values ('5', '2020-06-10', '1', '2')
-- insert into Orders (order_id, order_date, customer_id, product_id) values ('6', '2020-08-01', '2', '1')
-- insert into Orders (order_id, order_date, customer_id, product_id) values ('7', '2020-08-01', '3', '3')
-- insert into Orders (order_id, order_date, customer_id, product_id) values ('8', '2020-08-03', '1', '2')
-- insert into Orders (order_id, order_date, customer_id, product_id) values ('9', '2020-08-07', '2', '3')
-- insert into Orders (order_id, order_date, customer_id, product_id) values ('10', '2020-07-15', '1', '2')
-- Truncate table Products
-- insert into Products (product_id, product_name, price) values ('1', 'keyboard', '120')
-- insert into Products (product_id, product_name, price) values ('2', 'mouse', '80')
-- insert into Products (product_id, product_name, price) values ('3', 'screen', '600')
-- insert into Products (product_id, product_name, price) values ('4', 'hard disk', '450')


SELECT 
    r.customer_id AS customer_id,
    p.product_id AS product_id,
    p.product_name  AS product_name 
FROM
    Products AS p,
    (
        SELECT
            customer_id,
            product_id,
            RANK() OVER(PARTITION BY customer_id ORDER BY COUNT(product_id) DESC) AS rk
        FROM
            Orders
        GROUP BY
            customer_id,product_id
    ) AS r
WHERE
    p.product_id = r.product_id AND
    r.rk = 1 -- 最经常订购的商品