Day 10: Group By, CTEs, and CASE Statements Explained | LeetCode 1661 Solution in Hindi
In this article, we’ll solve the LeetCode problem “1661. Average Time of Process per Machine” using SQL. This problem is a great way to practice calculating averages, performing table joins, and working with time data in SQL.
Problem Description
You are provided with a table named Activity, which logs the start and end times of processes running on different machines in a factory. The table has the following columns:
machine_id (int): The ID of the machine.
process_id (int): The ID of the process running on the machine.
activity_type (enum): Either 'start' or 'end', indicating whether the process is starting or ending.
timestamp (float): The time in seconds when the activity occurred.
Your task is to calculate the average time each machine takes to complete a process. The time to complete a process is the difference between the 'end' timestamp and the 'start' timestamp. The average time should be calculated per machine and rounded to three decimal places.
Table: Activity
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| machine_id | int |
| process_id | int |
| activity_type | enum |
| timestamp | float |
+----------------+---------+
The table shows the user activities for a factory website.
(machine_id, process_id, activity_type) is the primary key (combination of columns with unique values) of this table.
machine_id is the ID of a machine.
process_id is the ID of a process running on the machine with ID machine_id.
activity_type is an ENUM (category) of type ('start', 'end').
timestamp is a float representing the current time in seconds.
'start' means the machine starts the process at the given timestamp and 'end' means the machine ends the process at the given timestamp.
The 'start' timestamp will always be before the 'end' timestamp for every (machine_id, process_id) pair.
There is a factory website that has several machines each running the same number of processes. Write a solution to find the average time each machine takes to complete a process.
The time to complete a process is the 'end' timestamp minus the 'start' timestamp. The average time is calculated by the total time to complete every process on the machine divided by the number of processes that were run.
The resulting table should have the machine_id along with the average time as processing_time, which should be rounded to 3 decimal places.
Return the result table in any order.
Step-by-Step Solution
Writing the SQL Query
Here’s the SQL query that solves the problem:
WITH ProcessTimes AS (
SELECT
machine_id,
process_id,
MAX(CASE WHEN activity_type = 'end' THEN timestamp END) -
MAX(CASE WHEN activity_type = 'start' THEN timestamp END) AS process_time
FROM Activity
GROUP BY machine_id, process_id
)
SELECT
machine_id,
ROUND(AVG(process_time), 3) AS processing_time
FROM ProcessTimes
GROUP BY machine_id;
What You Will Learn
Through this problem, you’ll gain experience with:
Using Common Table Expressions (CTEs): Simplifying complex queries by breaking them into smaller parts.
CASE Statements: Using conditional logic in SQL to select specific rows based on conditions.
Aggregate Functions: Using MAX, AVG, and ROUND to calculate time differences and averages.
Group By: Grouping data to perform aggregate calculations.
These concepts are crucial for working with SQL databases and are often tested in technical interviews.
Conclusion
The “Average Time of Process per Machine” SQL problem is a great exercise for practicing time-based calculations, grouping data, and performing aggregate functions. By mastering these concepts, you’ll be well-prepared to tackle more complex SQL queries in your work or interviews.
I hope this article helped you understand how to solve the problem step by step. If you’re preparing for interviews or just looking to improve your SQL skills, practicing problems like this is a great way to advance your knowledge.
Tags:
SQL, SQL for Beginners, SQL Tutorial, SQL Query, LeetCode, LeetCode SQL, PostgreSQL, SQL Interview Questions, Tech Interview, Database, Learn SQL, SQL Course, SQL Practice, SQL Queries, Coding Interview, SQL Problem Solving
Hashtags:
#SQL #SQLforBeginners #LeetCode #PostgreSQL #SQLTutorial #TechInterview #Database #LearnSQL #CodingInterview #SQLCourse #DataScience