Understanding the Problem and Requirements

You have two tables, Gate_Logs and Employee, with different structures. The goal is to find the employee who worked the highest weekly hours in a specific location over the past year.

Table Structures

Gate_Logs Table

Employee Table

Objective

Find the employee who worked the highest weekly hours in a specific location over the past year. To achieve this, you need to pair In/Out records, compute weekly work hours for each employee, rank employees per location per week, and finally select those with the highest rank.

Solution Overview

To solve this problem, we’ll use window functions to pair In/Out records, compute weekly work hours for each employee, and rank employees per location per week. We’ll then select those with the highest rank.

Step 1: Pairing In/Out Records

We can use the LAG window function to pair In/Out records. This will help us calculate the duration between these two records, which represents the work hours for a particular employee.

WITH periods AS (
  SELECT 
    `employee_id`, 
    `status` to_status,
    `timestamp` to_timestamp,
    LAG(`status`) OVER w AS fr_status,
    LAG(`timestamp`) OVER w AS fr_timestamp
  FROM gate_log
  WINDOW w AS (PARTITION BY `employee_id` ORDER BY `timestamp` ASC)
),

In the above code, we’re using a window named w that partitions the rows by employee_id and orders them by timestamp. The LAG function is used to get the previous row’s value for status and timestamp, which represents the In/Out record before the current one.

Step 2: Computing Weekly Work Hours

Next, we need to compute the weekly work hours for each employee. We can use the WEEKOFYEAR function to extract the week number from the timestamp and calculate the total seconds between the In and Out records.

sumup_weekly AS (
  SELECT 
    `employee_id`, 
    WEEKOFYEAR(fr_timestamp) week, 
    SUM(TIMESTAMPDIFF(SECOND, fr_timestamp, to_timestamp)) seconds
  FROM periods
  WHERE fr_status = 'In' AND to_status = 'Out'
  GROUP BY `employee_id`, `week`
),

In the above code, we’re grouping the rows by employee_id and week number. For each group, we calculate the total seconds between the In and Out records using the TIMESTAMPDIFF function.

Step 3: Ranking Employees per Location

After computing the weekly work hours for each employee, we need to rank employees per location per week. We can use the RANK window function to achieve this.

rank_weekly AS (
  SELECT
    e.`employee_id`,
    e.`location_id`,
    w.`week`,
    SEC_TO_TIME(w.`seconds`) work_hours,
    RANK() OVER(PARTITION BY e.`location_id`, w.`week` ORDER BY w.`seconds` DESC) rank_hours
  FROM sumup_weekly w
  JOIN employee e ON w.`employee_id` = e.`employee_id`
)

In the above code, we’re partitioning the rows by location_id and week number. For each partition, we calculate the rank of each employee based on their work hours in descending order.

Step 4: Selecting Employees with Highest Rank

Finally, we need to select the employees who have the highest rank. We can do this by filtering the rank_weekly table to include only rows where the rank_hours is equal to 1.

SELECT *
FROM rank_weekly
WHERE rank_hours = 1

This will give us the employee ID, location ID, week number, work hours, and rank for each employee who has worked the highest weekly hours in their respective location over the past year.

Example Use Case

Let’s say we have the following data in our Gate_Logs table:

And the following data in our Employee table:

Running the above query will give us the following result:

This means that employee 1 with location ID 1 has worked the highest weekly hours in their respective location over the past year.

Conclusion

In this solution, we’ve used window functions to pair In/Out records, compute weekly work hours for each employee, rank employees per location per week, and finally select those with the highest rank. This approach allows us to efficiently calculate the weekly work hours for each employee and identify the top performers in their respective locations.

Last modified on 2025-03-21