SQL Interview Question: 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
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');
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-range | num_employee |
---|---|
21-30 | 7 |
31-40 | 6 |
41-50 | 5 |
51-60 | 2 |
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.
Hi, I am Vishal Jaiswal, I have about a decade of experience of working in MNCs like Genpact, Savista, Ingenious. Currently i am working in EXL as a senior quality analyst. Using my writing skills i want to share the experience i have gained and help as many as i can.
Welcome to Day 13 of Learning Python for Data Science! Today, we’re focusing on three…
Test your understanding of Python Data Structure, which we learned in our previous lesson of…
Welcome to Day 12 of Learning Python for Data Science. Today, we’ll dive into Pandas,…
NumPy Array in Python is a powerful library for numerical computing in Python. It provides…
Welcome to Day 9 of Learning Python for Data Science. Today we will explore comprehensions,…
Test your understanding of Python Data Structure, which we learned in our previous lesson of…