UptoSkills Logo
Login
Top 3 Most Frequent Items per CustomerMedium

Top 3 Most Frequent Items per Customer

MediumSQLPractice library · +20 XPtop n per groupsqlwindow functions

A popular e-commerce platform wants to identify the top 3 most frequently purchased items by each customer to personalize recommendations.

Tables:

  • customers: customer_id (integer, PK), name (varchar(50))
  • orders: order_id (integer, PK), customer_id (integer, FK), order_date (date)
  • order_items: order_item_id (integer, PK), order_id (integer, FK), item_name (varchar(100)), quantity (integer)

Return the customer_id, item_name, and the count of how many times the customer ordered that item, for the top 3 most frequent items per customer. If a customer has fewer than 3 distinct items, return all distinct items they ordered. Order the results by customer_id and then by the item's frequency in descending order. Handle ties in frequency by listing them alphabetically by item_name in ascending order. NULLs are not expected in the core data columns used for ranking. Empty results for a customer are possible if they have no orders.

Result columns: customer_id, item_name, order_count.

Tables

CREATE TABLE customers (
  customer_id integer PRIMARY KEY,
  name varchar(50) NOT NULL
);

CREATE TABLE orders (
  order_id integer PRIMARY KEY,
  customer_id integer NOT NULL,
  order_date date NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
  order_item_id integer PRIMARY KEY,
  order_id integer NOT NULL,
  item_name varchar(100) NOT NULL,
  quantity integer NOT NULL CHECK (quantity > 0),
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

Example data

INSERT INTO customers (customer_id, name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'David');

INSERT INTO orders (order_id, customer_id, order_date) VALUES
(101, 1, '2023-01-15'),
(102, 1, '2023-01-20'),
(103, 2, '2023-02-10'),
(104, 2, '2023-02-15'),
(105, 2, '2023-03-01'),
(106, 3, '2023-04-05'),
(107, 3, '2023-04-10'),
(108, 3, '2023-04-10'),
(109, 3, '2023-05-01'),
(110, 4, '2023-06-01');

INSERT INTO order_items (order_item_id, order_id, item_name, quantity) VALUES
(1, 101, 'Laptop', 1),
(2, 102, 'Mouse', 2),
(3, 101, 'Keyboard', 1),
(4, 102, 'Keyboard', 1),
(5, 103, 'Monitor', 1),
(6, 104, 'Webcam', 1),
(7, 104, 'Mouse', 1),
(8, 105, 'Mouse', 2),
(9, 106, 'Charger', 1),
(10, 107, 'Cable', 1),
(11, 108, 'Charger', 1),
(12, 109, 'Charger', 1),
(13, 110, 'Desk Lamp', 1);

Your query is also checked on hidden datasets with different data.

Hints

Similar problems

More in: SQL and Databases problems