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
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' )
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
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
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.
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
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
glad it helped!
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'
)
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
that's intuitive , thanks for the solution
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
this is what i sumbitted, this was my solution
Excellent explanation. You’re the 🐐
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.
That’s good feedback, thank you!
Thank you so much for this explanation. Really helpful.
usage of subquery in this question, will increase the time complexity!
great work, thanks.
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
Hi, can we use case when in this problem or not?