DEV Community

Khaled Said
Khaled Said

Posted on

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
    }
Enter fullscreen mode Exit fullscreen mode

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')
Enter fullscreen mode Exit fullscreen mode

on the sample data should return the following dictionary:

{
    "attended": True,
    "duration": "12:00"
}
Enter fullscreen mode Exit fullscreen mode

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"
}
Enter fullscreen mode Exit fullscreen mode

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'))
Enter fullscreen mode Exit fullscreen mode

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more

Top comments (0)

Billboard image

Create up to 10 Postgres Databases on Neon's free plan.

If you're starting a new project, Neon has got your databases covered. No credit cards. No trials. No getting in your way.

Try Neon for Free →

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay