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

Solution and walkthrough of leetcode database problem 1661: Average Time of Process per Machine. I'm using MySQL but this solution should work in any SQL dialect such as PostgreSQL SQL Server, etc.

Playlists:

[ayeoh]

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

[B]

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

[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

[vijaysakthivel]

Thank you so much for this explanation. Really helpful.

[SB-ixdb]

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

[lonakshibhagat]

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

[sanaayakurup]

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

[partidaa]

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.