Blog

Find the most common value (mode) in a specific column.

This question was asked in Interview at black rock. Read more

Company: BlackRock

CTC: 26LPA

SourceLinkedIn

SQL Interview Question

Q. Find the most common value (mode) in a specific column.

-- Create the Products table
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Category VARCHAR(50),
    Price DECIMAL(10, 2)
);

-- Insert sample data into Products table
INSERT INTO Products VALUES
(1, 'Laptop', 'Electronics', 800.00),
(2, 'Laptop', 'Electronics', 300.00),
(3, 'Headphones', 'Electronics', 50.00),
(4, 'Laptop', 'Electronics', 800.00),
(5, 'Tablet', 'Electronics', 300.00),
(6, 'Tablet', 'Electronics', 300.00),
(7, 'Chair', 'Furniture', 120.00),
(8, 'Table', 'Furniture', 250.00),
(9, 'Laptop', 'Electronics', 800.00),
(10, 'Desk', 'Furniture', 200.00);

See this code on db-fiddle

Solution

SELECT 
    ProductName,
    COUNT(*) AS Frequency
FROM 
    Products
GROUP BY 
    ProductName
ORDER BY 
    Frequency DESC
LIMIT 1;

Explanation

We have selected ProductName along with the count of rows as Frequency, grouping the data by ProductName. To display the product with the highest frequency at the top of the table, we used ORDER BY Frequency DESC. Finally, the LIMIT clause ensures that only the top record is selected.


I hope this would have been helpful for you, consider sharing it with your friends. thank you.

Spread the love

Recent Posts

Python Practice Questions & Solutions Day 5 of Learning Python for Data Science

Python Practice Questions & Solutions Day 5 of Learning Python for Data Science Welcome back…

3 days ago

Day 5 of Learning Python for Data Science: Data Types, Typecasting, Indexing, and Slicing

Day 5 of Learning Python for Data Science: Data Types, Typecasting, Indexing, and Slicing Understanding…

3 days ago

Python Practice Questions & Solutions Day 4 of Learning Python for Data Science

Python Practice Questions & Solutions Day 4 of Learning Python for Data Science Welcome back…

3 days ago

Day 4 of Learning Python for Data Science

Day 4 of Learning Python for Data Science Day 4 of Learning Python for Data…

3 days ago

Practice Questions and Answers for Day 3 of Learning Python for Data Science

Test your Python skills with these 20 practice questions and solutions from Day 3 of…

4 days ago

Day 3 of Learning Python for Data Science

Understanding Python’s conditional statements is essential for controlling the flow of a program. Today, we…

4 days ago