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`;

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.

• SQL Interview Question at Zomato for a Data analyst Position (0-3 Years) – | Shared By An Experienced Data Analyst
• The Ultimate Guide to SQL Indexing and Query Optimization
• SQL Interview Questions Asked In Walmart For Data Analyst Post | CTC – 18 LPA | Learn With Curious Club!!
• SQL Interview Questions for Deloitte Data Engineer Roles: Your Ultimate Prep Guide
• Data Analyst SQL Interview Questions | EY (Ernst & Young) | Shared By An Experienced Data Analyst