Grokking SQL for Tech Interviews
Ask Author
Back to course home

0% completed

Vote For New Content
34. Employee Task Tracking (Easy)
Table of Contents

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

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!

Table of Contents

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible