Attention Coders: Efficient SQL Query to Retrieve Products Ordered (using Join)
Please Upvote my solution, if you find it helpful ;)\n\n# Intuition\nWe want to find the products that were ordered during February 2020 and determine the total
## ***Please Upvote my solution, if you find it helpful ;)***\n\n# Intuition\nWe want to find the products that were ordered during **February 2020** and determine the total number of units ordered for each product. Additionally, we only want to include products that have a total unit count of at least **100**.\n\n# Approach\nTo solve this problem, we can follow these steps:\n\n1. Look at the Products table and select the product_name column, which contains the names of the products.\n1. Look at the Orders table and find the sum of units for each product using the product_id column that matches the products.\n1. Filter the orders to include only those made in the specified period. We check the year and month of the order_date column.\n1. Group the results by the product_id so that we can aggregate the units ordered for each product.\n1. Finally, apply a condition to include only those products that have a sum of units greater than or equal to 100.\n\n# Complexity\n- Time complexity:\nThe time complexity of your solution depends on the size of the Orders table and how efficiently the database can process the query. However, in general, your solution has a time complexity of $$O(n)$$, where n is the number of rows in the Orders table that satisfy the specified conditions.\n\n- Space complexity:\nThe space complexity of your solution is determined by the number of distinct product_ids in the specified period. This is because we need to store and process the aggregated results for each distinct product_id. So the space complexity is $$O(m)$$, where m is the number of distinct product_ids.\n\n# Code\n```\n# Write your MySQL query statement below\n\nSELECT p.product_name AS product_name, sum(o.unit) AS unit FROM Products p\nJOIN Orders o USING (product_id)\nWHERE YEAR(o.order_date)=\'2020\' AND MONTH(o.order_date)=\'02\'\nGROUP BY p.product_id\nHAVING SUM(o.unit)>=100\n```\n***Please Upvote my solution, if you find it helpful ;)***\n\n![6a87bc25-d70b-424f-9e60-7da6f345b82a_1673875931.8933976.jpeg](https://assets.leetcode.com/users/images/0ff6cb57-1f54-4baa-8225-480584e67777_1685558412.40014.jpeg)\n\n
['Database', 'MySQL']