Employee Attendance Record

Problem

Table: Employees

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| employee_id   | int     |
| employee_name | varchar |
+---------------+---------+
employee_id is the primary key for this table.
Each row of this table contains the ID and the name of one employee in the company.

Table: Attendance

+---------------+---------+------+
| Column Name   | Type    | Note |
+---------------+---------+------+
| employee_id   | int     |      |
| attendance_date | date  |      |
| status        | varchar |      |
+---------------+---------+------+
There is no primary key for this table, it may contain duplicates.
Each row of this table records the fact that the employee with ID employee_id had a status ('Present', 'Absent', 'Late') on a specific attendance_date.

Problem Definition

Write a solution to find the attendance record for each employee.

Return the result table ordered by employee_id.

Example

Input: 
Employees table:
+-------------+---------------+
| employee_id | employee_name |
+-------------+---------------+
| 101         | Alice         |
| 102         | Bob           |
| 103         | Charlie       |
+-------------+---------------+

Attendance table:
+-------------+----------------+--------+
| employee_id | attendance_date | status |
+-------------+----------------+--------+
| 101         | 2020-11-01      | Present|
| 101         | 2020-11-02      | Absent |
| 102         | 2020-11-01      | Late   |
| 102         | 2020-11-02      | Present|
| 103         | 2020-11-01      | Present|
| 103         | 2020-11-02      | Present|
+-------------+----------------+--------+

Output: 
+-------------+---------------+--------------+-------------+-----------+
| employee_id | employee_name | days_present | days_absent | days_late |
+-------------+---------------+--------------+-------------+-----------+
| 101         | Alice         | 1            | 1           | 0         |
| 102         | Bob           | 1            | 0           | 1         |
| 103         | Charlie       | 2            | 0           | 0         |
+-------------+---------------+--------------+-------------+-----------+

Try It Yourself

-- TODO: Write your user queries here

Solution

To address this problem, SQL queries are employed to extract and process information from the Employees and Attendance tables. The goal is to find the attendance record for each employee, including the number of days present, absent, and late.

The approach begins with a LEFT JOIN between the Employees and Attendance tables, using employee_id to link them. This join is essential for associating employee names with their attendance records.

Following the join, the SUM function, in combination with CASE statements, is used to calculate the number of days each employee was present, absent, and late. This is done by incrementing the count for each status accordingly.

The GROUP BY clause groups the results by employee_id and employee_name, which is necessary for aggregating the attendance records for each employee.

Finally, the result is ordered by employee_id using the ORDER BY clause, as specified in the problem statement.

SELECT e.employee_id,
       e.employee_name,
       SUM(CASE WHEN a.status = 'Present' THEN 1 ELSE 0 END) AS days_present,
       SUM(CASE WHEN a.status = 'Absent' THEN 1 ELSE 0 END) AS days_absent,
       SUM(CASE WHEN a.status = 'Late' THEN 1 ELSE 0 END) AS days_late
FROM Employees e
LEFT JOIN Attendance a ON e.employee_id = a.employee_id
GROUP BY e.employee_id, e.employee_name
ORDER BY e.employee_id;

Step-by-Step Approach

Step 1: Join Employees and Attendance Tables

Associate each employee with their corresponding attendance records.

SQL Query:

SELECT e.employee_id,
       e.employee_name,
       a.status
FROM Employees e
LEFT JOIN Attendance a ON e.employee_id = a.employee_id;

Output After Step 1:

+-------------+---------------+--------+
| employee_id | employee_name | status |
+-------------+---------------+--------+
| 101         | Alice         | Present|
| 101         | Alice         | Absent |
| 102         | Bob           | Late   |
| 102         | Bob           | Present|
| 103         | Charlie       | Present|
| 103         | Charlie       | Present|
+-------------+---------------+--------+

Step 2: Calculate Attendance Counts

Count the number of days each employee was Present, Absent, or Late.

SQL Query:

SELECT e.employee_id,
       e.employee_name,
       SUM(CASE WHEN a.status = 'Present' THEN 1 ELSE 0 END) AS days_present,
       SUM(CASE WHEN a.status = 'Absent' THEN 1 ELSE 0 END) AS days_absent,
       SUM(CASE WHEN a.status = 'Late' THEN 1 ELSE 0 END) AS days_late
FROM Employees e
LEFT JOIN Attendance a ON e.employee_id = a.employee_id
GROUP BY e.employee_id, e.employee_name
ORDER BY e.employee_id;

Explanation:

• SUM(CASE WHEN a.status = 'Present' THEN 1 ELSE 0 END) AS days_present:
  Counts the number of days the employee was present.

• SUM(CASE WHEN a.status = 'Absent' THEN 1 ELSE 0 END) AS days_absent:
  Counts the number of days the employee was absent.

• SUM(CASE WHEN a.status = 'Late' THEN 1 ELSE 0 END) AS days_late:
  Counts the number of days the employee was late.

Final Output:

+-------------+---------------+--------------+-------------+-----------+
| employee_id | employee_name | days_present | days_absent | days_late |
+-------------+---------------+--------------+-------------+-----------+
| 101         | Alice         | 1            | 1           | 0         |
| 102         | Bob           | 1            | 0           | 1         |
| 103         | Charlie       | 2            | 0           | 0         |
+-------------+---------------+--------------+-------------+-----------+

I just read a lesson on **Employee Attendance Record** (Databases) and want to truly understand it. Explain Employee Attendance Record from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **Employee Attendance Record** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **Employee Attendance Record** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **Employee Attendance Record** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.