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

Day 13 of Learning Python for Data Science: Mastering Pivot, Apply and RegEx

Welcome to Day 13 of Learning Python for Data Science! Today, we’re focusing on three…

6 days ago

Practice day 12 of Learning Python for Data Science

Test your understanding of Python Data Structure, which we learned in our previous lesson of…

2 weeks ago

Day 12 of Learning Python for Data Science – Pandas

Welcome to Day 12 of Learning Python for Data Science. Today, we’ll dive into Pandas,…

2 weeks ago

Day 10 Of Learning Python for Data Science – NumPy Array In Python

NumPy Array in Python is a powerful library for numerical computing in Python. It provides…

2 weeks ago

Day 9 of Learning Python for Data Science – Queries Related To Functions In Python

Welcome to Day 9 of Learning Python for Data Science. Today we will explore comprehensions,…

2 weeks ago

Practice day 8 of Learning Python for Data Science

Test your understanding of Python Data Structure, which we learned in our previous lesson of…

2 weeks ago