Top Product by Department | SQL Advanced

You have access to an Instacart-like database that contains information about users' orders, the products they ordered, and a list of products with their department information.

Write a query to list the top most ordered product (with highest number of orders) in each department. Display the product name along with its department name.

To answer this question, you will have access to the following tables:

orders table:

Column Name	Description
order_id	Unique identifier for each order.
user_id	Identifier of the user who placed the order.
product_id	Identifier of the product ordered.
order_date

You have access to an Instacart-like database that contains information about users' orders, the products they ordered, and a list of products with their department information.

Write a query to list the top most ordered product (with highest number of orders) in each department. Display the product name along with its department name.

To answer this question, you will have access to the following tables:

orders table:

Column Name	Description
order_id	Unique identifier for each order.
user_id	Identifier of the user who placed the order.
product_id	Identifier of the product ordered.
order_date	Date when the order was placed.

products table:

Column Name	Description
id	Unique identifier for each product.
product_name	Name of the product.
department_id	Identifier of the department to which the product belongs

departments table:

Column Name	Description
department_id	Unique identifier for each department.
department_name	Name of the department.

Example Output:

product_name	department_name
Juice	Beverages
Milk	Dairy and Eggs
Frozen Pizza	Frozen