The solution to Oracle "Average Time of Process per Machine" Problem. Beats 50.72 in my case %
Intuition\n Describe your first thoughts on how to solve this problem. \nI decided to divide problem into subqueries the following manner. Becaue this way we ca
# Intuition\n<!-- Describe your first thoughts on how to solve this problem. -->\nI decided to divide problem into subqueries the following manner. Becaue this way we can solve our issue more effectively and intuitively.\n\n# Approach\n<!-- Describe your approach to solving the problem. -->\nFirstly we should find out the start and end timestamps where each process ends and starts accordingly. We could do this by the following subquery with grouping by end_time, start_time. Where MAX and MIN functions will let us find the appropriate timestamps.\n\n# Code \n\n```\nSELECT machine_id, process_id,\n    MIN(timestamp) AS start_time,\n    MAX(timestamp) AS end_time\nFROM Activity\nGROUP BY machine_id, process_id\nORDER BY machine_id, process_id;\n\n```\nKeep it mind this is not strictly necessary to use ORDER BY here. But it gives you more concise and straight ordered result.\n\nAfter this you could easily wrap this structure inside the main query in order for you to find the essential result.\n\n# Code\n```\n/* Write your PL/SQL query statement below */\n\nSELECT machine_id, ROUND(AVG(end_time - start_time), 3) AS processing_time\nFROM \n  (\n    SELECT machine_id, process_id,\n      MIN(timestamp) AS start_time,\n      MAX(timestamp) AS end_time\n    FROM Activity\n    GROUP BY machine_id, process_id\n    ORDER BY machine_id, process_id\n  ) \nGROUP BY machine_id\nORDER BY machine_id;\n\n```\n\nAs you can see, we round our AVG value so as to get the appropriate outcome. And actually we also should GROUP our result using GROUP BY and ORDER BY.\n\nThank you. Feel free to leave your comments.
['Oracle']