0% completed
Problem Statement
Table: TaskLog
Each row in this table logs a task completion by an assignee, indicating the task's ID, the employee who assigned the task, the assignee, and the completion date.
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| task_id | int |
| employee_id | int |
| assignee_id | int |
| completion_date| date |
+----------------+---------+
This table does not have a primary key and may contain duplicate rows.
task_id is the unique identifier for each task.
employee_id and assignee_id may have the same value, indicating the same person.
Develop a solution to find the IDs of employees who have completed at least one task assigned to themselves. The output should be sorted by employee_id
in ascending order.
Example
Input:
TaskLog table: +---------+-------------+------------+----------------+ | task_id | employee_id | assignee_id| completion_date| +---------+-------------+------------+----------------+ | 101 | 10 | 15 | 2022-01-01 | | 102 | 11 | 16 | 2022-01-02 | | 103 | 12 | 12 | 2022-01-02 | | 104 | 13 | 13 | 2022-01-03 | | 105 | 14 | 18 | 2022-01-04 | | 106 | 15 | 15 | 2022-01-05 | | 107 | 16 | 10 | 2022-01-05 | +---------+-------------+------------+----------------+
Output:
+-------------+ | employee_id | +-------------+ | 12 | | 13 | | 15 | +-------------+
Employees with IDs 12
, 13
, and 15
have completed tasks assigned by themselves.
Try It Yourself
Solution
To identify employees who have completed their own tasks, we need to examine records in the TaskLog
table where the employee_id
matches the assignee_id
.
- Select Employee IDs: Begin with a
SELECT
statement to fetch all employees from theTaskLog
table. - Apply Conditions: Use a
WHERE
clause to filter employees who have completed tasks assigned by themselves. - Sort the Results: Finally sort the results
SQL Query
The following SQL query accomplishes this:
SELECT DISTINCT employee_id FROM TaskLog WHERE employee_id = assignee_id ORDER BY employee_id ASC
Step by Step Approach
Step 1: Select Employee IDs
Begin by selecting distinct employee_id
from the TaskLog
table to ensure each employee is listed only once in the result.
SELECT DISTINCT employee_id FROM TaskLog
Output After Step 1:
+-------------+ | employee_id | +-------------+ | 10 | | 11 | | 12 | | 13 | | 14 | | 15 | | 16 | +-------------+
Step 2: Apply Conditions
Refine the selection to include only those records where the employee_id
matches the assignee_id
, indicating tasks completed by the assigners themselves.
SELECT DISTINCT employee_id FROM TaskLog WHERE employee_id = assignee_id
Output After Step 2:
+-------------+ | employee_id | +-------------+ | 12 | | 13 | | 15 | +-------------+
Step 3: Sort the Results
Finally, sort the resulting employee IDs in ascending order to comply with the problem statement's requirements.
SELECT DISTINCT employee_id FROM TaskLog WHERE employee_id = assignee_id ORDER BY employee_id ASC
Final Output:
+-------------+ | employee_id | +-------------+ | 12 | | 13 | | 15 | +-------------+
.....
.....
.....
Table of Contents
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible