# SQL Interview Questions Series#5

**Interview Question:** Write a SQL query to categorize employees based on their years of service as follows:

* **Veteran**: Employees with 20 or more years of service.
    
* **Experienced**: Employees with 10 to 19 years of service (inclusive).
    
* **Intermediate**: Employees with 5 to 9 years of service (inclusive).
    
* **Newbie**: Employees with less than 5 years of service.
    

Display the employee's name, years of service, and the corresponding service category. Sort the results by years of service.

### **Schema**

Assume we have the following table `employees` that contains employee information:

```sql
CREATE OR REPLACE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    hire_date DATE
);
```

### **Dataset**

Let's insert some sample data into the `Employees` table to illustrate the query:

```sql
INSERT INTO employees (employee_id, first_name, last_name, hire_date) VALUES
(1, 'John', 'Doe', '2000-01-15'),
(2, 'Jane', 'Smith', '2010-04-22'),
(3, 'Emily', 'Johnson', '2015-08-30'),
(4, 'Michael', 'Williams', '2020-11-10'),
(5, 'Sarah', 'Brown', '2012-05-18');
```

### **Solution**

To categorize employees based on their years of service, we will use the `CASE` statement along with the `DATEDIFF` function to calculate the years of service. The following SQL query accomplishes this:

```sql
SELECT 
    first_name, 
    last_name, 
    DATEDIFF(YEAR, hire_date, GETDATE()) AS years_of_service,
    CASE
        WHEN DATEDIFF(YEAR, hire_date, GETDATE()) >= 20 THEN 'Veteran'
        WHEN DATEDIFF(YEAR, hire_date, GETDATE()) BETWEEN 10 AND 19 THEN 'Experienced'
        WHEN DATEDIFF(YEAR, hire_date, GETDATE()) BETWEEN 5 AND 9 THEN 'Intermediate'
        ELSE 'Newbie'
    END AS service_category
FROM 
    employees
ORDER BY 
    years_of_service;
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1735805956519/358eee19-7e26-434e-97eb-2e28abf19f2a.png align="center")

### **Explanation**

1. **Calculate Years of Service:** The `DATEDIFF(YEAR, hire_date, GETDATE())` function calculates the number of years between the hire date and the current date, representing the years of service.
    
2. **Categorize Employees Using CASE Statement:** The `CASE` statement is used to categorize employees based on their years of service:
    
    * `Veteran`: Employees with 20 or more years of service.
        
    * `Experienced`: Employees with 10 to 19 years of service.
        
    * `Intermediate`: Employees with 5 to 9 years of service.
        
    * `Newbie`: Employees with less than 5 years of service.
        
3. **Sort Results by Years of Service:** The `ORDER BY years_of_service` clause sorts the results by the calculated years of service.
    

### **Github:**

[https://github.com/vipinputhanveetil/sql-interview-questions-series/blob/main/sql\_interview\_question\_5.sql](https://github.com/vipinputhanveetil/sql-interview-questions-series/blob/main/sql_interview_question_5.sql)

### **Conclusion**

By using the `CASE` statement along with the `DATEDIFF` function, you can efficiently categorize employees based on their years of service. This approach is useful for various HR analytics and reporting needs.