Employee seniority
mediumEmployee seniority
Google SQL Interview Question
Google's HR team is setting up a mentoring program and wants to label each employee by how long they have been with the company.
Show every employee's name, hire_date and a label as seniority. The label is "Senior" for anyone hired before 2020, "Mid-level" for anyone hired in 2020 or 2021, and "Junior" for anyone hired in 2022 or later.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Analytics Engineer
employeesTable16 rows
| Column Name | Type |
|---|---|
| employee_id | BIGINT |
| name | VARCHAR |
| department | VARCHAR |
| salary | BIGINT |
| manager_id | BIGINT |
| hire_date | DATE |
employeesExample Input
| employee_id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 2 | Mei Lin | Engineering | 185000 | 1 | 2018-06-04 |
| 4 | James Park | Engineering | 128000 | 2 | 2019-09-02 |
| 8 | Hannah Weber | Analytics | 134000 | 7 | 2020-01-13 |
| 10 | Kenji Sato | Analytics | 109000 | 8 | 2022-03-07 |
| 13 | Lucia Ferrari | Sales | 104000 | 11 | 2021-02-15 |
| 14 | Peter Novak | Sales | 96000 | 12 | 2022-09-12 |
Example Output
| name | hire_date | seniority |
|---|---|---|
| Hannah Weber | 2020-01-13 | Mid-level |
| James Park | 2019-09-02 | Senior |
| Kenji Sato | 2022-03-07 | Junior |
| Lucia Ferrari | 2021-02-15 | Mid-level |
| Mei Lin | 2018-06-04 | Senior |
| Peter Novak | 2022-09-12 | Junior |
Explanation
Mei Lin was hired in 2018 and James Park in 2019, so both are Senior. Hannah Weber and Lucia Ferrari were hired in 2020 and 2021, so they are Mid-level. Kenji Sato and Peter Novak joined in 2022, so they are Junior.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Google
Difficulty
medium
Topic
conditional logic
Your query
Starting the SQL engine...