Grokking SQL for Tech Interviews
Ask Author
Back to course home

0% completed

Vote For New Content
34. Employee Task Tracking (Easy)
On this page

Problem Statement

Example

Try It Yourself

Solution

SQL Query

Step by Step Approach

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

MYSQL
MYSQL
. . . .

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.

  1. Select Employee IDs: Begin with a SELECT statement to fetch all employees from the TaskLog table.
  2. Apply Conditions: Use a WHERE clause to filter employees who have completed tasks assigned by themselves.
  3. 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          |
+-------------+

.....

.....

.....

Like the course? Get enrolled and start learning!

On this page

Problem Statement

Example

Try It Yourself

Solution

SQL Query

Step by Step Approach