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-range	num_employee
21-30	7
31-40	6
41-50	5
51-60	2

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.

• Write a query to identify the employee(s) whose salary is closest to the average salary of the company.
• Display all months where sales exceeded the average monthly sales.
• Find the most common value (mode) in a specific column.
• Identify employees who have never received a performance review.
• Write a query to retrieve the first order placed by each customer.