SQL Data Analysis Interview Question #26/100 | SQL Challenge | SQL Tutorials | Business analyst

[@santhoshkumars3967]

Another solution with window function
with final as (select machine_id,
lag(activity_type) over (partition by machine_id) as previous_type,
lag(timestamp) over (partition by machine_id) as previous_timestamp,
activity_type,timestamp
from Activity),
conclusion as (select *,
(timestamp - previous_timestamp) as timings
from final
where
previous_type = 'start' and activity_type = 'end')
select machine_id, avg(timings)
from conclusion group by 1

[@DHANANJAYKUMAR-em1of]

Nicely explained👌👌👌

[@vinothkumars7421]

with cte as (SELECT machine_id,process_id, activity_type, timestamp as EndTimestamp, lag(timestamp) over (partition by machine_id, process_id) as Statrt_tiemstamp FROM q26_Activity)
, cte2 as
(select *,EndTimestamp-Statrt_tiemstamp as diff
from cte
where Statrt_tiemstamp is not null)
select machine_id,avg(diff) from cte2 group by machine_id

[@Savenature635]

I have done however; I don't know if this query is correct or not :
with cte as (select machine_id,process_id,max(case when activity_type like '%start%' then timestamp else null end) as start_time,
min(case when activity_type like '%end%' then timestamp else null end) as end_time,
count( process_id) as total_processes
from machines
group by 1,2
order by machine_id,process_id)
select distinct machine_id,(sum(end_time-start_time) over(partition by machine_id)) /
total_processes as avg_processing_time
from cte;

[@rishabhralli9151]

with cte as(
select * ,
row_number() over(partition by activity_type) as rt
from activity)
select c1.machine_id,
avg((c1.timestamp-c2.timestamp) )as total_time from cte c1
inner join cte c2
on c1.machine_id=c2.machine_id
where c1.activity_typec2.activity_type
and c1.rt=c2.rt
and c1.activity_type='end'
group by c1.machine_id
order by total_time desc;
anoher solution using join

[@AbinashPatra-n2i]

My Solution
With CTE1 as
(
select *,ROW_NUMBER() over (order by machine_id,process_id) as rn
from activity
where activity_type ='start'
),CTE2 AS
(
select *,ROW_NUMBER() over (order by machine_id,process_id) as rn
from activity
where activity_type ='end'
),CTE3 AS
(
select C1.machine_id,C1.process_id,AVG(C2.timestamp - C1.timestamp) as Avg_time
from CTE1 AS C1
inner join
CTE2 AS C2
ON C1.rn=C2.rn
Group by C1.machine_id,C1.process_id
)
Select machine_id,AVG(Avg_time) as M_avgtime
from CTE3
Group by machine_id