✅100% BEATS 🔥 CTE and LAG 🤠
Proof \uD83D\uDC68\u200D\uD83C\uDFEB\n\n\n\n\n\n\n# \uD83D\uDD0D Problem\nWhile attempting to solve this problem, we encounter a significant issue. Namely, we f
# Proof \uD83D\uDC68\u200D\uD83C\uDFEB\n![stats.png](https://assets.leetcode.com/users/images/7853752e-7054-45f4-a61a-610a3e6cca8c_1717539641.0532646.png)\n\n\n\n<hr>\n\n# \uD83D\uDD0D Problem\nWhile attempting to solve this problem, we encounter a significant issue. Namely, we face a problem that is not immediately noticeable. To put it plainly, the issue arises when dealing with a large amount of data. Consider a scenario where a CROSS JOIN is applied to a large dataset. This results in a Cartesian product with n * n complexity (for example, if n = 1,000,000), meaning each row corresponds to one million others. \uD83D\uDE35\u200D\uD83D\uDCAB\n\n<hr>\n\n# \u27A1\uFE0F Alternative Approach\nAs a solution, we may opt to create two separate tables\u2014one for the start and one for the end. However, this is merely a temporary workaround that doesn\'t truly address the problem. It merely mitigates it, as the resulting complexity becomes (n / 2) * (n / 2) (for example, if n = 1,000,000). Consequently, for one row, half a million others are matched. \uD83D\uDE13\n\n<hr>\n\n# \uD83D\uDDFF The Unsung Hero: LAG func\nIt may seem like there\'s nothing more we can do (Napoleon reference), but the LAG function comes to our aid. This function retrieves the value from the row that is currently being processed, resulting in a much more efficient solution. For example, if we have n = 1,000,000 rows, with the LAG function, only one row is matched for each row processed \uD83D\uDE80\n\n<hr>\n\n# Code \u2699\uFE0F \uD83D\uDD27\n```\n-- The best approach, although not the fastest in this case.\nWITH ProcessTimes AS (\n    SELECT \n        machine_id,\n        process_id,\n        activity_type,\n        `timestamp` - LAG(`timestamp`) OVER(PARTITION BY machine_id, process_id ORDER BY `timestamp`) AS process_time\n    FROM \n        Activity\n)\nSELECT \n    machine_id,\n    ROUND(AVG(process_time), 3) AS processing_time\nFROM \n    ProcessTimes\nWHERE \n    activity_type = \'end\'\nGROUP BY \n    machine_id;\n\n-- (n/2) * (n/2) The fastest solution.\nWITH act_start AS ( SELECT machine_id, process_id, `timestamp` FROM Activity WHERE activity_type = \'start\'), \n     act_end AS (SELECT machine_id, process_id, `timestamp` FROM Activity WHERE activity_type = \'end\')\n\nSELECT \n    acts.machine_id,\n    ROUND(AVG(acte.`timestamp` - acts.`timestamp`), 3) AS \'processing_time\'\nFROM \n    act_start acts\n    INNER JOIN act_end acte ON acts.machine_id = acte.machine_id AND acts.process_id = acte.process_id\nGROUP BY \n    acts.machine_id;\n\n-- n * n The Slowest solution.\nSELECT \n    a1.`machine_id`, \n    ROUND( ( AVG(a2.`timestamp`, a1.`timestamp`), 3) AS \'processing_time\'\nFROM \n    Activity a1, Activity a2\nWHERE \n    a1.machine_id = a2.machine_id \n    AND a1.process_id = a2.process_id\n    AND a1.activity_type = \'start\' \n    AND a2.activity_type = \'end\'\nGROUP BY \n    a1.machine_id, a1.activity_type\n```\n>If u need any help, just write in the comment section.\n<hr>\n\n# Please consider giving an Upvote \uD83D\uDE4F\n![Baldwin IV](https://assets.leetcode.com/users/images/1a8725c2-71f4-4f02-b913-a43648ab0490_1715072776.8033872.jpeg)\n\n---\n\n# MY OTHER SQL 100% BEATS \uD83C\uDFAF\n*38.* <a href="https://leetcode.com/problems/exchange-seats/solutions/5260448/100-beats-cte-and-union">Exchange Seats</a>\n*39.* <a href="https://leetcode.com/problems/movie-rating/solutions/5264216/100-beats-cte-and-union"> Movie Rating</a>\n*40.* <a href="https://leetcode.com/problems/restaurant-growth/solutions/5264608/100-beats-cte-and-offset"> Restaurant Growth</a>\n*41.* <a href="https://leetcode.com/problems/friend-requests-ii-who-has-the-most-friends/solutions/5270991/100-beats-cte-and-offset">Friend Requests II: Who Has the Most Friends</a>
['MySQL']