SQL Interview Questions Series#10

Scenario: An online store wants to identify the top 3 products that were sold the most in June 2023. The sales table contains information about each sale.

Question: Write a query to find the IDs of the top 3 products along with the total quantity sold in June 2023. Display the results in descending order based on the quantity sold.

Schema

Let's start by defining the schema for the sales table. The table has the following structure:

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    quantity INT,
    sale_date DATE
);

Dataset

Next, we'll create some sample data for the sales table:

INSERT INTO sales (sale_id, product_id, quantity, sale_date) VALUES
(1, 1, 10, '2023-06-01'),
(2, 2, 15, '2023-06-05'),
(3, 3, 5, '2023-06-07'),
(4, 1, 7, '2023-06-10'),
(5, 2, 10, '2023-06-15'),
(6, 3, 20, '2023-06-20'),
(7, 1, 15, '2023-06-25'),
(8, 2, 5, '2023-06-28'),
(9, 3, 10, '2023-06-30');

Solution

To find the top 3 products that were sold the most in June 2023, we need to aggregate the quantity sold for each product_id within the specified date range and then sort the results in descending order based on the total quantity sold. Here is the SQL query to achieve this:

SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
WHERE sale_date BETWEEN '2023-06-01' AND '2023-06-30'
GROUP BY product_id
ORDER BY total_quantity DESC
LIMIT 3;

Explanation

• The SELECT statement retrieves the product_id and the total quantity sold (SUM(quantity)) from the sales table.

• The WHERE clause filters the rows to include only those sales that occurred between June 1, 2023, and June 30, 2023.

• The GROUP BY clause groups the sales data by product_id.

• The ORDER BY clause sorts the results in descending order based on the total_quantity sold.

• The LIMIT 3 clause ensures that only the top 3 products are returned.

Github:

https://github.com/vipinputhanveetil/sql-interview-questions-series/blob/main/sql_interview_question_10.sql