Advanced usage of map & filter in Python

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'))