SQL ADVANCED Certification Skills test on Hackerrank - Second Problem - Weekend Hours

Watch also the first problem of SQL Advanced Certification test:
• SQL ADVANCED Certifica...
Solve this problem to better understand the Row Number function:
• Hackerrank SQL Solutio...
Database for this problem on GitHub:
github.com/CuriosityLeonardo/...
My code for this Solution in MSSQL (Corrected on 22nd of June, 2022):
github.com/CuriosityLeonardo/...
____________________________________________
SUBSCRIBE!
Do you want to understand how to solve SQL Problems in every detail? That's what my channel is about. When I was younger I thought I could never program because it looked way too difficult. The truth is that it takes time but with some patience anybody can do it! Follow me along and get better!
____________________________________________
SUPPORT MY CHANNEL
🙌The best way to support my channel right now is to give me a Super Thanks. You can do that by clicking thanks next to the title of the video. It is much appreciated!
____________________________________________
💾GitHub: github.com/CuriosityLeonardo?...
✋Add me on LinkedIn to get in contact: / markus-friemann-221b3814b

[@thanlong2009]

thank mentor, I learnt a lot from this video and just got the certification

[@thecodingmentor7701]

Thats great to hear Popcorn! Keep poppin'!

[@triceyy1238]

I really like all your videos!

[@thecodingmentor7701]

Thanks Tricey, thats so good to hear!

[@huybui3889]

Many thanks for sharing your code, i think we should add one case statement to work correctly, when minute (timestamp) >= minute(lag...) then hour (timestamp) - hour(lag...) and (timestamp) < minute(lag...) then hour (timestamp) - hour(lag...) - 1

[@thecodingmentor7701]

Hi Huy Bui, Yes you are absolutely Right! I didnt consider the minute granularity. Thanks a ton! Here is the corrected Code: github.com/CuriosityLeonardo/HackerrankCertifications/blob/main/Weekend%20Hours%20Solution.txt

[@RobertaFerreiraGomes]

Thanks for sharing the code. But could you next time show the entire query in the end.

[@zamihasan8253]

Hey! Thank you for sharing this. Can you please give me an idea how do I solve this in MYSQL?

[@thecodingmentor7701]

Hey Zami, sure! With statement and the lag function work the same as in MSSQL. The only difference is the datepart function. You can use the Extract function in MySQL instead

[@feridnecefzade5632]

Can you solve others questions in advance certification test?

[@thecodingmentor7701]

Hi Ferid, I solved the advanced certification test, so I cannot retake it and solve the alternatives

[@feridnecefzade5632]

@@thecodingmentor7701 how i can send you a question? about other questions in advance certificate

[@triceyy1238]

Why the code does not pass the test in Hackerrank?

[@thecodingmentor7701]

Hi Tricey, when we login for example at 15:10 and we logout at 16:05 we dont count the hour because not a full hour passed. I didnt consider this fact in the video, thats why the code does not pass. There is the correct solution in the Github link in the video description.

[@HienPham-mb2db]

@@thecodingmentor7701 The code doesnt work anymore, when using MS SQL, result exactly the same with the expected output but still fail. :(

[@aniketkosalge367]

Winners Chart
There were a number of contests where participants each made multiple attempts. The attempt with the highest score is the only one considered. Write a query to list the contestants ranked in the top 3 for each contest. If multiple contestants have the same score in a contest, they are at the same rank.
Report event_id, rank 1 name(s), rank 2 name(s), rank 3 name(s). Order the contests by event_id. Names that share a rank should be ordered alphabetically and separated by a comma.
Order the report by event_id.
This is the sql advance hackerrank question please create a video for this also

[@arjunsingh4514]

solution mila?

[@tamerlanmamedoff3303]

it does not work correctly xiyar

[@thecodingmentor7701]

Hi Tamerian, you are right, thank you! Thanks to you and the other comment from Huy Bui I could correct the code. Here is the right version: github.com/CuriosityLeonardo/HackerrankCertifications/blob/main/Weekend%20Hours%20Solution.txt

[@joshdee7639]

You shouldnt be copying his code and using it as yours anyways

[@exploringbharat4625]

Correct answer
WITH hours_worked AS (
SELECT
emp_id,
CASE
WHEN MINUTE(TIMESTAMP) >= MINUTE(LAG(TIMESTAMP) OVER(PARTITION BY DATE(TIMESTAMP), emp_id ORDER BY TIMESTAMP)) THEN HOUR(TIMESTAMP) - HOUR(LAG(TIMESTAMP) OVER(PARTITION BY DATE(TIMESTAMP), emp_id ORDER BY TIMESTAMP))
ELSE HOUR(TIMESTAMP) - HOUR(LAG(TIMESTAMP) OVER(PARTITION BY DATE(TIMESTAMP), emp_id ORDER BY TIMESTAMP)) - 1
END AS hours_worked
FROM attendance
-- only weekends
WHERE DAYOFWEEK(TIMESTAMP) IN (1, 7)
)
SELECT
emp_id,
SUM(hours_worked) AS hours_worked
FROM hours_worked
GROUP BY emp_id
ORDER BY hours_worked DESC;

[@zerchu]

Thx!

[@realniqqa69fast]

YO BRUH THANKS I COMPLETED SQL ADVANCED WITH YOUR HELP

[@truthseeker1951]

thanks

[@Snehakrn_12]

finally after trying every possible code , this one fitted .. thank you so much

[@chinmaydhobale5662]

your are a Legend bruh thank for sharing this code

[@lucnguyencaominh3820]

here is my simpler answer:
with w as(
select emp_id,timestamp,datediff(MINUTE,lag(timestamp) over(partition by emp_id,day(timestamp) order by emp_id,timestamp),timestamp)/60 as weekendhours
from weekend
where DATENAME(WEEKDAY, timestamp) in ('saturday','sunday'))
select emp_id, sum(weekendhours) weekendhours from w
group by emp_id
order by weekendhours desc

[@djrednitro4116]

damn I wish I saw this earlier I took the test and got stuck on it so I failed.

[@siddharthapurwar]

This will only work when there is only one login and one logout in a day...if there are multiple logins and logout in a single day by a employee...then it wont work,

[@thecodingmentor7701]

You are right, thats a good observation! In the test, there is only one login and logoff per day so it works in the test. Do you have a more flexible solution which also works for multiple timestamps per employee per day? Please share with us!

[@sanjanaabrid7396]

this is my solution :
with cte as
(select emp_id,timestamp, lag(timestamp,1) over(partition by emp_id, cast(timestamp as date) order by timestamp) as endtime
from attendance
where datepart(weekday,timestamp) in (1,7)
)
select emp_id, sum(DATEDIFF(hour, endtime, timestamp)) as hrdiff
from cte
where endtime is not null
group by emp_id
order by hrdiff desc

[@thecodingmentor7701]

Hi Sanjanaa, I cannot test it but it looks very elegant, thanks for sharing!

[@nebiyoutefera952]

select emp_id, sum(WorkedTime) as TotalWorkTime from
(select emp_id, (datediff(MINUTE, min(timestamp),max(timestamp))/60) as workedTime from [Attendance]
where datepart(DW, timestamp) in(1,7)
group by emp_id, datepart(DW, timestamp)
) as temp
group by emp_id
order by TotalWorkTime desc

[@thecodingmentor7701]

Hi nebiyou, thanks for sharing your code! The datediff function in your code is making the difference between the first day of work from an employee and the last day of work without considering log off and login times of employees. This code is a good start but still needs some refinement, keep going!

[@nebiyoutefera952]

@@thecodingmentor7701 can you share me the database script and let me test with the data.

[@thecodingmentor7701]

@@nebiyoutefera952 Sure! Here is the database: github.com/CuriosityLeonardo/HackerrankCertifications/blob/main/Weekend%20Hours%20Database.txt

[@nhutrinh4380]

why not datediff(hour, ) to avoid / 60

[@PrateekRohila29]

The Correct Answer for this Problem is
WITH hours_worked AS (
SELECT
emp_id,
CASE
WHEN MINUTE(TIMESTAMP) >= MINUTE(LAG(TIMESTAMP) OVER(PARTITION BY DATE(TIMESTAMP), emp_id ORDER BY TIMESTAMP)) THEN
HOUR(TIMESTAMP) - HOUR(LAG(TIMESTAMP) OVER(PARTITION BY DATE(TIMESTAMP), emp_id ORDER BY TIMESTAMP))
ELSE
HOUR(TIMESTAMP) - HOUR(LAG(TIMESTAMP) OVER(PARTITION BY DATE(TIMESTAMP), emp_id ORDER BY TIMESTAMP)) - 1
END AS hours_worked
FROM attendance
WHERE DAYOFWEEK(TIMESTAMP) IN (1, 7) -- 1 for Sunday, 7 for Saturday
)
SELECT
emp_id,
SUM(hours_worked) AS hours_worked
FROM hours_worked
GROUP BY emp_id
ORDER BY hours_worked DESC;