Given an employee with ID 1 who was present on January 1st, absent on January 2nd, and present on January 3rd, how would you calculate his attendance percentage over this period?

Attendance percentage is calculated as the ratio of days the employee was present to the total number of days observed, multiplied by 100.

For the example:

• Present days = 2 (Jan 1 and Jan 3)
• Total days = 3
• Percentage = (2 / 3) × 100 ≈ 66.7%.

In SQL you can compute this with a conditional aggregation:

SELECT employee_id,
       COUNT(*) FILTER (WHERE status = 'present') AS present_days,
       COUNT(*) AS total_days,
       ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'present') / COUNT(*), 2) AS attendance_pct
FROM   Attendance
WHERE  employee_id = 1
  AND  date BETWEEN '2023-01-01' AND '2023-01-03'
GROUP BY employee_id;

The query counts present days, total days, and then calculates the percentage. The ROUND function keeps the result to two decimal places. This approach works for any date range and any employee.