Q1. Tenure analysis: Table: employee Column Name Data Type Description employee_id Integer Unique identifier for each employee department Varchar The department of the employee job_level Varchar The level of the job (e.g., Junior, Mid-level, Senior) employee_status Varchar The status of the employee (Active, Termed) hire_date Date The date the employee was hired term_date Date The date the employee left, NULL if currently employed

Task: 1. Write a SQL query to calculate the average tenure of active employees in each job level within each department. Assume today's date as the end date you need to calculate tenure for active employees.

2. Due to data error, there may be issues with term date and hire date. Write the issues that might occur in the date fields and how will you modify Task 1 to account for those conditions.

3. Write a SQL query to find the employee(s) in each department with the longest tenure, along with their tenure. Also, identify any employees whose tenure is more than two standard deviations above the average tenure for their department and job level.