Blog

Write a query to group employees by age ranges (e.g., 20–30, 31–40) and count the number of employees in each group.

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

Company: BlackRock

CTC: 26LPA

SQL Interview Question

Question

Q. Write a query to group employees by age ranges (e.g., 20–30, 31–40) and count the number of employees in each group.

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT CHECK (age > 0 AND age < 100),
    department VARCHAR(50),
    date_hired DATE
);

-- Insert data into the employees table
INSERT INTO employees (first_name, last_name, age, department, date_hired) VALUES
('Alice', 'Smith', 25, 'HR', '2019-03-15'),
('Bob', 'Johnson', 34, 'Finance', '2017-08-22'),
('Charlie', 'Brown', 28, 'IT', '2020-11-05'),
('Diana', 'Wilson', 42, 'Marketing', '2015-06-01'),
('Ethan', 'Taylor', 50, 'IT', '2010-09-12'),
('Fiona', 'Anderson', 39, 'HR', '2018-04-30'),
('George', 'Thomas', 46, 'Finance', '2008-01-25'),
('Hannah', 'White', 31, 'Marketing', '2021-05-17'),
('Isaac', 'Martin', 55, 'Finance', '2005-10-10'),
('Julia', 'Garcia', 29, 'IT', '2022-02-14'),
('Kevin', 'Martinez', 33, 'HR', '2016-12-09'),
('Laura', 'Robinson', 48, 'Marketing', '2013-07-19'),
('Michael', 'Clark', 26, 'Finance', '2020-03-21'),
('Natalie', 'Rodriguez', 41, 'IT', '2012-08-11'),
('Oliver', 'Lewis', 38, 'HR', '2019-01-05'),
('Patricia', 'Walker', 22, 'Marketing', '2023-06-24'),
('Quinn', 'Hall', 53, 'Finance', '2000-11-30'),
('Rachel', 'Allen', 30, 'IT', '2021-09-18'),
('Samuel', 'Young', 32, 'Marketing', '2018-03-07'),
('Tina', 'King', 27, 'HR', '2020-05-12');

Solution

SELECT 
    CASE
        WHEN age BETWEEN 21 AND 30 THEN '21-30'
        WHEN age BETWEEN 31 AND 40 THEN '31-40'
        WHEN age BETWEEN 41 AND 50 THEN '41-50'
        WHEN age BETWEEN 51 AND 60 THEN '51-60'
    END AS `age-range`, -- Use backticks for column alias
    COUNT(*) AS num_employee
FROM employees
GROUP BY `age-range`;
age-rangenum_employee
21-307
31-406
41-505
51-602

View on DB Fiddle

Explanation

Here, in this scenario, we use the CASE statement to define the age ranges and assign a corresponding group name in the age_range column. Then, we count all the rows to determine the total number of employees within each age group. Finally, we select the age_range and the count, using GROUP BY to calculate the count for each specific age range.


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…

4 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…

4 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…

4 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…

4 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…

5 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…

5 days ago