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 🐐

[@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!

[@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.

[@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

[@lonakshibhagat3116]

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