LeetCode 1661: Average Time of Process per Machine [SQL]

[@ayeoh47]

this one felt more challenging than the other easy questions, i didnt think to create a self join, this made the problem much easier to solve thank you

[@frederikmuller]

glad it helped!

[@illiat3164]

I solved it by using 2 additional CTEs and I still think using CTEs here is more cleaner and production like code: with start_activity_type as ( select machine_id, process_id, timestamp as start_timestamp from Activity where activity_type = 'start' ), end_activity_type as ( select machine_id, process_id, timestamp as end_timestamp from Activity where activity_type = 'end' )

[@459B]

my solution without using join: select machine_id, avg(case when activity_type='end' then timestamp end)-avg(case when activity_type='start' then timestamp end) as processing_time from Activity group by machine_id

[@mickyman753]

that's intuitive , thanks for the solution

[@UmaAndLak]

This was what i typed in MySQL. Another solution. select machine_id, round(avg (end_timestamp - start_timestamp),3) as processing_time from ( select machine_id, process_id, SUM(if(activity_type ='start', timestamp,null)) AS start_timestamp, SUM(if(activity_type ='end', timestamp,null)) AS end_timestamp from Activity group by machine_id, process_id ) as t group by machine_id

[@ayeoh47]

this is what i sumbitted, this was my solution

[@ThinkBigToday]

Excellent explanation. You’re the 🐐

[@vijaysakthivel4966]

Thank you so much for this explanation. Really helpful.

[@SB-ix7db]

usage of subquery in this question, will increase the time complexity!

[@temenoscom3498]

great work, thanks.

[@lonakshibhagat3116]

Hi, can we use case when in this problem or not?

[@sanaayakurup5453]

My Solution-select machine_id,AVG(diff) from (select machine_id,end_time-start_time as diff from (select machine_id,process_id, sum(if(activity_type='start',time,0)) as start_time, sum(if(activity_type='end',time,0)) as end_time from Activity group by machine_id,process_id) t1 )t2 group by machine_id

[@partidaa98]

As a beginner (and this being an "easy" LC problem), I think it would be very helpful if you could explain the reasoning for some of your decision making/thought process. Sometimes it feels like you just say and type what you are doing, but it isn't always intuitive to follow your logic (at least to a beginner like me). Great video though, very clear and concise overall.

[@frederikmuller]

That’s good feedback, thank you!