Calling all beginners: simple SQL challenge

Hi!

Here's a little challenge for beginners.

We have a users table and we need to know the number of users in 3 age ranges.

• if age < 18 then age range is minor.
• if age < 65 then age range is adult.
• if age >= 65 then age range is senior.

Table structure

CREATE TABLE users (
  id int NOT NULL AUTO_INCREMENT,
  age SMALLINT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Sample data

INSERT INTO users(age)
VALUES
(2), (4), (6),
(12), (14), (16), (21), (25),
(27), (31), (33), (37), (39),
(57), (57), (59), (61), (63), (65)
;

Expected results

| ageRange | nb     |
| -------- | ------ |
| 6        | minor  |
| 12       | adult  |
| 1        | senior |

Go ahead and give it a try!

You might want to read about the following statements: IF, CASE/WHEN and GROUP BY.

A possible solution

Note that I'm using MySQL but since it is standard SQL you should be able to port it to another DB engine.

First we need to determine age ranges and for that we're going to add a CASE statement.

CASE
    WHEN age < 18 THEN 'minor'
    WHEN age < 65 THEN 'adult'
    ELSE 'senior'
END as ageRange

Pretty straightforward, right? We could have used two IF statements but I opted for CASE/WHEN as I find it elegant. :)

Add that to a SELECT statement and you get users' age along with their age group.

Next we need to count how many users belong to each group. It's as simple as adding a GROUP BY ageRange clause.

SELECT
    count(*),
    CASE
        WHEN age < 18 THEN 'minor'
        WHEN age < 65 THEN 'adult'
        ELSE 'senior'
    END as ageRange
FROM users
GROUP BY ageRange

And we're done!

Happy coding!