SQL || INNER JOIN || DATE || EASY Detailed Explaination & Solution || Leetcode -1327 By Vedansh Jain
Intuition / Understanding the ProblemWe are tasked with finding products that have at least 100 units ordered in February 2020 and displaying their names along
# Intuition / Understanding the Problem
<!-- Describe your first thoughts on how to solve this problem. -->
We are tasked with finding products that have at least 100 units ordered in February 2020 and displaying their names along with the total units ordered.

**1. What’s the Goal?**
We need to combine information from two tables:

- Products Table: Contains information about products ***(product_id, product_name, product_category).***
- Orders Table: Contains data about orders ***(product_id, order_date, unit).***

**Our goal is to:**
Identify products ordered in February 2020.
Calculate the total units ordered for each product during that month.
Return the product names where the total units ordered are 100 or more.

**2. How Do We Connect These Tables?**
To achieve this, we need to link the product_id from the Products table with the product_id from the Orders table.

- **`INNER JOIN`:** We’ll use an INNER JOIN because we are only interested in products that have orders.
Filter by Date: We will filter orders to include only those made in February 2020.

# Approach / Step-by-Step Solution
<!-- Describe your approach to solving the problem. -->
Let’s break down how to solve this problem step-by-step.

- **Step 1: Identify the Common Column (product_id)**
Both Products and Orders tables have product_id, which will help us link products with their respective orders.

- **Step 2: Use INNER JOIN to Combine Data**
We will INNER JOIN the Products table with the Orders table on product_id. This ensures we include only products that have at least one order.

- **Step 3: Filter Orders in February 2020**
We’ll filter orders to include only those where order_date falls within February 2020.
To do this, we’ll use DATE_FORMAT(order_date, '%Y-%m') = '2020-02'.

- **Step 4: Group by Product and Sum Units**
We’ll GROUP BY product_id and use SUM(Orders.unit) to calculate the total units ordered for each product.

- **Step 5: Filter Products with at Least 100 Units Ordered**
We’ll use the HAVING clause to include only products with a total of 100 or more units ordered.

# Code
```mysql []
# Write your MySQL query statement below
SELECT
  Products.product_name,
  SUM(Orders.unit) AS unit
FROM Products
INNER JOIN Orders
  USING (product_id)
WHERE DATE_FORMAT(Orders.order_date, '%Y-%m') = '2020-02'
GROUP BY product_id
  HAVING SUM(Orders.unit) >= 100;
```

# Explanation of the Query
**1. SELECT Products.product_name, SUM(Orders.unit) AS unit:**
We select product_name from the Products table to display the product names.
We use SUM(Orders.unit) to calculate the total units ordered in February 2020, and we alias it as unit.
**2. FROM Products INNER JOIN Orders USING (product_id):**
We perform an INNER JOIN to combine the Products and Orders tables using the common product_id column.
This ensures only products with orders are included.
**3. WHERE DATE_FORMAT(Orders.order_date, '%Y-%m') = '2020-02':**
We filter orders to include only those made in February 2020.
DATE_FORMAT(order_date, '%Y-%m') converts the date to 'YYYY-MM' format, and we compare it to '2020-02'.
**4. GROUP BY product_id:**
We group the results by product_id so we can sum the units for each product.
**5. HAVING SUM(Orders.unit) >= 100:**
After grouping, we use the HAVING clause to filter products with at least 100 units ordered.


![Pleaseupvotevedanshjain.png](https://assets.leetcode.com/users/images/0c7afdbb-fab9-46b5-bfe3-b9f72d2be86e_1739128107.950346.png)


# Complexity
- **Time complexity:**
The time complexity is O(n log n), where:
n is the number of rows in the Orders table.
The INNER JOIN and GROUP BY operations are the most time-consuming, especially if product_id is not indexed.
- **Space complexity:**
The space complexity is O(k), where k is the number of products that meet the condition (products with at least 100 units ordered). The space is used to store the result set.

# Additional Insights / Key Points
**1. Why Use INNER JOIN Instead of LEFT JOIN?**
We use INNER JOIN because we only want products that have at least one order. A LEFT JOIN would include products with no orders, which isn’t necessary here.

**2. Why Use DATE_FORMAT Instead of BETWEEN?**
DATE_FORMAT(order_date, '%Y-%m') = '2020-02' makes it easy to filter by a specific month.
Alternatively, you could use WHERE order_date BETWEEN '2020-02-01' AND '2020-02-29' for more precision.

**3. Why Use HAVING Instead of WHERE for Filtering Total Units?**
WHERE filters rows before grouping, while HAVING filters after the grouping and aggregation.
Since we’re filtering based on the result of SUM(Orders.unit), we must use HAVING.

# Conclusion ( Upvote - If Helps Even 1% 💎 )
This solution uses INNER JOIN to combine product and order data, filters for February 2020 orders, and uses GROUP BY with HAVING to find products with at least 100 units ordered. The final output is clean and displays the product names along with their total units ordered.

*If you found this explanation helpful, don’t forget to upvote! 💎*
['MySQL']