Average Processing Time Calculation in SQL

Aug 13, 2024

SQL Interview Series - Question 10: Average Time of Process per Machine

Introduction

  • Lecture by Chirag on SQL Interview question.
  • Focus on calculating average processing time for machines in a factory.

Table Structure

  • Table Name: Activity
  • Columns:
    • Machine ID
    • Process ID
    • Activity Type (Start/End)
    • Time Stamp (float, representing current time in seconds)

Key Concepts

  • Primary Key: Combination of Machine ID, Process ID, and Activity Type.
  • Definitions:
    • Start: Indicates the machine starts a process at a given timestamp.
    • End: Indicates the machine ends a process at a given timestamp.
    • The timestamp for the start will always be before the end for each Machine ID and Process ID pair.

Task Objective

  • Calculate average time taken for each machine to complete a process:
    • Formula: Average Time = (End Time - Start Time) / Number of Processes
  • Return a table with:
    • Machine ID
    • Processing Time (rounded to three decimal places)

Example Explanation

  1. Given an Activity table with Machine IDs and their respective processes.
  2. Each process has a start and end time.
  3. Calculate completion time for each process:
    • Completion Time = End Time - Start Time
  4. Calculate average processing time for each machine:
    • Average Processing Time = (t1 + t2) / 2 for multiple processes.

SQL Query Construction

Steps to Write the Query:

  1. Inner Join the Activity Table:

    • Use INNER JOIN to connect the activity table to itself (alias as a1 and a2).
    • Joining condition:
      • a1.Process ID = a2.Process ID
      • a1.Machine ID = a2.Machine ID
      • a1.Time Stamp < a2.Time Stamp

  2. Select Required Columns:

    • SELECT a1.Machine ID, a2.Time Stamp - a1.Time Stamp AS Processing Time

  3. Calculate Average:

    • Use AVG() to calculate average processing time for each machine and round it off to three decimal places with ROUND().
    • Alias the result as Processing Time.

  4. Group By:

    • Use GROUP BY a1.Machine ID

Final Query Example

SELECT a1.Machine_ID, ROUND(AVG(a2.Time_Stamp - a1.Time_Stamp), 3) AS Processing_Time
FROM Activity a1
INNER JOIN Activity a2 ON a1.Process_ID = a2.Process_ID AND a1.Machine_ID = a2.Machine_ID AND a1.Time_Stamp < a2.Time_Stamp
GROUP BY a1.Machine_ID;

Conclusion

  • The final output will provide average processing times for each machine.
  • Encourage questions in the comment section for any doubts.
  • Reminder to like the video if it was helpful.