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.

• Mastering Pivot Table in Python: A Comprehensive Guide
• Data Science Interview Questions Section 3: SQL, Data Warehousing, and General Analytics Concepts
• Data Science Interview Questions Section 2: 25 Questions Designed To Deepen Your Understanding
• Data Science Questions Section 1: Data Visualization & BI Tools (Power BI, Tableau, etc.)
• Optum Interview Questions: 30 Multiple Choice Questions (MCQs) with Answers