MySQL Step-by-Step Solution to Find Products Ordered in a Period
Intuition\n Describe your first thoughts on how to solve this problem. \nWe need to find the name and the amount of products with at least 100 units ordered in 
# Intuition\n<!-- Describe your first thoughts on how to solve this problem. -->\nWe need to find the name and the amount of products with at least 100 units ordered in February 2020.\n\n# Approach\n<!-- Describe your approach to solving the problem. -->\n1. Select specific columns from the table.\n\n    > `SELECT` \u2192 This command retrieves data from a database\n\n2. Calculate the total units ordered.\n\n    > `SUM()` \u2192 This function calculates the sum of a set of values\n\n    > `AS` \u2192 This command renames a column with an alias (temporary name). In most database languages, we can skip the `AS` keyword and get the same result\n\n3. Combine the other table to link each product with its corresponding order data.\n\n    > `LEFT JOIN` \u2192 This command returns all rows from the left table (table 1), and the matching rows from the right table (table 2). The result is `NULL` from the right side, if there is no match.\n\n    #### Difference Between `USING` and `ON` in SQL Joins\n    1. The **USING** clause\n        > `USING` \u2192 This allows to specify the join key by name. It is used if several columns share the same name but you don\u2019t want to join using all of these common columns. The columns can\u2019t have any qualifiers in the statement, including the `WHERE` clause.\n\n    2. The **ON** clause\n        > `ON` \u2192 This allows to specify the column names for join keys in both tables. It is used to join tables where the column names don\u2019t match in both tables. The join conditions are removed from the filter conditions in the `WHERE` clause.\n\n4. Filter to include only orders from February 2020.\n\n    > `WHERE` \u2192 This command filters a result set to include only records that fulfill a specified condition\n\n    > `MONTH(date)` \u2192 This function returns the month part for a given `date` (a number from 1 to 12)\n\n    > `=` \u2192 This operator compares if one value is equal to another value\n\n    > `AND` \u2192 This operator filters records based on more than one condition\n\n    > `YEAR(date)` \u2192 This function returns the year part for a given `date` (a number from 1000 to 9999)\n\n5. Group the results by `product_name` to obtain the total order quantity for each product.\n\n    > `GROUP BY` \u2192 This command groups rows that have the same values into summary rows, typically to perform aggregate functions on them\n\n6. Choose products with a total order quantity of at least 100 units.\n\n    > `HAVING` \u2192 This clause allows filtering of aggregated results produced by `GROUP BY` clause (`WHERE` keyword cannot be used with aggregate functions)\n\n    > `>=` \u2192 This operator compares if one value is greater than or equal to another value\n\n# Complexity\n- Time complexity: $O(n)$\n<!-- Add your time complexity here, e.g. $$O(n)$$ -->\n`n` is the number of records in the table. This is because the query processes each row in the table to perform the join, filter and group operations.\n\n# Code\n```\nSELECT \n    p.product_name, \n    SUM(o.unit) AS unit \nFROM Products p\nLEFT JOIN Orders o USING(product_id)\nWHERE MONTH(o.order_date) = \'02\' AND YEAR(o.order_date) = \'2020\'\nGROUP BY p.product_name\nHAVING SUM(o.unit) >= 100\n```
['MySQL']