QN=>
The times that employees log in and out are recorded over the course of a month.
For each employee, determine the number of hours worked during the weekends.
For simplicity, hours worked in a day, hours are truncated to their integer part.
For example, there are 10 hours between '2000:01:01 00:45:00' and '2000:01:01
10:45:00'. There are 9 hours between '2000:01:01 00:46:00' and '2000:01:01
10:45:00'
Return a list of employee IDs and weekend hours worked, descending by hours worked.
Solution.
WITH weekend_logs AS (
SELECT
emp_id,
STR_TO_DATE(timestamp, '%Y-%m-%d %H:%i:%s') AS log_time
FROM
attendance
WHERE
DAYOFWEEK(STR_TO_DATE(timestamp, '%Y-%m-%d %H:%i:%s')) IN (1, 7) -- 1 = Sunday, 7 = Saturday
),
paired_logs AS (
SELECT
emp_id,
log_time AS log_in_time,
LEAD(log_time) OVER (PARTITION BY emp_id ORDER BY log_time) AS log_out_time,
ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY log_time) AS rn
FROM
weekend_logs
),
filtered_paired_logs AS (
SELECT
emp_id,
log_in_time,
log_out_time
FROM
paired_logs
WHERE
rn % 2 = 1
)
SELECT
emp_id,
SUM(FLOOR(TIMESTAMPDIFF(MINUTE, log_in_time, log_out_time) / 60)) AS weekend_hours_worked
FROM
filtered_paired_logs
WHERE
log_out_time IS NOT NULL -- Ensure we only consider pairs with a log out time
GROUP BY
emp_id
ORDER BY
weekend_hours_worked DESC;
Explanation:
1.weekend_logs CTE:
Filters the logs to include only those that fall on weekends (Saturday and Sunday).
Converts the timestamp to a proper date format.
2.paired_logs CTE:
Uses the LEAD function to pair each log-in time with the subsequent log-out time for each employee, ordered by log_time.
Uses ROW_NUMBER to assign a row number to each log.
3.filtered_paired_logs CTE:
Filters to include only the log-in times by selecting rows with odd row numbers (rn % 2 = 1).
4. Final SELECT:
Calculates the number of hours worked by using the TIMESTAMPDIFF function to get the difference in minutes, then divides by 60 and uses FLOOR to truncate to the nearest integer.
Sums the hours worked on weekends for each employee.
Groups by emp_id and orders the results by weekend_hours_worked in descending order.
Hacker Rank Worker hour Calculation Problem
