In 2 different job interviews I got asked to solve the following question using python. so I thought it would be good to publish the solution for the question.
You have the following relation:
erDiagram
AttendanceActions ||--o{ Attendance : AttendanceId
AttendanceActions {
int id
int AttendanceId
datetime ActionTime
text Action
}
Attendance {
int id
date day
text employee
}
and the following tables:
Attendance
| id | day | employee |
|---|---|---|
| 1 | 2020-04-01 | EMP01 |
| 2 | 2020-04-01 | EMP02 |
| 3 | 2020-04-02 | EMP01 |
| 4 | 2020-04-03 | EMP01 |
| 5 | 2020-04-02 | EMP02 |
| 6 | 2020-04-03 | EMP02 |
AttendanceActions
| id | AttendanceId | ActionTime | Action |
|---|---|---|---|
| 1 | 1 | 2020-04-01 12:00 AM | CheckIn |
| 2 | 1 | 2020-04-01 12:00 PM | CheckOut |
| 3 | 2 | 2020-04-01 12:05 PM | CheckIn |
| 4 | 2 | 2020-04-01 11:50 PM | CheckOut |
| 5 | 3 | 2020-04-02 12:05 AM | CheckIn |
| 6 | 3 | 2020-04-02 11:50 AM | CheckOut |
| 7 | 3 | 2020-04-02 11:50 PM | CheckIn |
| 8 | 4 | 2020-04-03 12:05 PM | CheckOut |
Overview
You are given a simple SQLITE3 database for an attendance system with a small set of data.
An employee can perform one of two actions: CheckIn or CheckOut. When an action is performed, it is stored at 2 levels:
- An attendance entry is created for the day if no actions were previously performed on that day
- An attendance action entry is created recording the time for this specific action and linked to the day’s attendance record
For example, if EMP01 checks in at midnight of 2020-04-01, two records are created:
- A record in the Attendance table with date 2020-04-01 and employee EMP01
- A record in the AttendanceActions table referencing the previous attendance record with time 2020-04-01 12:00 AM
The time stamps are stored in local server time, which is in the Cairo timezone (UTC + 2). This is not a good practice, but it’s based on a real-world case.
Requirements
01 Check day attendance for employee
Write a function that accepts an employee code (e.g. EMP01) and a date (e.g. 2020-04-01) and reports whether the employee has attended that day and how long.
Examples
get_attendance('emp01', '2020-04-01')
on the sample data should return the following dictionary:
{
"attended": True,
"duration": "12:00"
}
The employee attended from 12:00 AM to 12:00 PM, which is exactly 12 hours.
For 2020-04-02, the employee attended from 12:05 AM to 11:50 AM (11:45 night shift), then checked in again for his next shift at 11:50 PM (so 10 minutes before midnight the next day) for a total of 11:55:
{
"attended": True,
"duration": "11:55"
}
For 2020-04-03, the employee checked out at 12:05 PM for a duration of 12:05 (12 hours 5 minutes) calculated from midnight since his last check-in was the previous day.
Solution
from datetime import datetime, timedelta
import sqlite3
def _convert_string_datetime(time):
return datetime.strptime(time, '%Y-%m-%d %I:%M %p')
def get_attendance(employee: str, tdate: str):
emp = employee
start_time = datetime.strptime(tdate, '%Y-%m-%d') - timedelta(minutes=15)
end_time = (start_time + timedelta(days=1, minutes=15))
query = '''
SELECT *, et.ActionTime forwardTime FROM
(SELECT
ac.id,
ac.ActionTime,
ac.Action,
ac.Attendanceid,
a.employee,
(LEAD (ac.id, 1,0) OVER (ORDER BY ac.id)) forwardID
FROM AttendanceActions ac
INNER JOIN Attendance a ON ac.Attendanceid = a.id
WHERE a.employee = '{}' ORDER BY ac.id) ee
LEFT JOIN AttendanceActions et ON ee.forwardID = et.id
'''.format(emp)
with sqlite3.connect('attendance.db') as con:
cur = con.cursor()
db_res = cur.execute(query)
p_res = db_res.fetchall()
pt_res = list(map(lambda x: x[1] - x[0], filter(
lambda x: start_time < x[0] < end_time and x[2] == 'CheckIn', map(
lambda x: ((_convert_string_datetime(x[1]) if x[1] else 0),
(_convert_string_datetime(x[8]) if x[8] else 0), x[2]), p_res))))
if not pt_res:
return {
"attended": False,
"duration": "00:00"
}
else:
return {
"attended": True,
"duration": str(pt_res[0])
}
print(get_attendance('EMP01', '2020-04-01'))
print(get_attendance('EMP01', '2020-04-02'))
print(get_attendance('EMP01', '2020-04-03'))
print(get_attendance('EMP02', '2020-04-01'))
print(get_attendance('EMP02', '2020-04-02'))
print(get_attendance('EMP02', '2020-04-03'))
Top comments (0)