Using First_value( ), MAX( ) and CTE
IntuitionWhen the problem specified that the product_id should be sold only in the first quarter of 2019, my initial thought was to use the FIRST_VALUE() functi
# Intuition
<!-- Describe your first thoughts on how to solve this problem. -->
When the problem specified that the product_id should be sold only in the first quarter of `2019`, my initial thought was to use the `FIRST_VALUE()` function along with `MAX()`. Although you could use `MIN()` instead, I chose to utilize `FIRST_VALUE()`. This solution is not the most elegant, but it reflects my first approach to writing the query.
# Approach
<!-- Describe your approach to solving the problem. -->
After creating the CTE, all we had to do was specify our conditions in the `WHERE` clause using `BETWEEN` and `AND` for the first_sale_date and `<=` for the last_sale_date.


# Code
```mysql []
# Write your MySQL query statement below
WITH CTE AS (
    SELECT s.product_id, p.product_name,
        FIRST_VALUE(s.sale_date) OVER (PARTITION BY s.product_id ORDER BY s.sale_date
        ) AS first_sale_date,
        MAX(s.sale_date) OVER (PARTITION BY s.product_id) AS last_sale_date
    FROM Sales s
    LEFT JOIN Product p 
        ON s.product_id = p.product_id
)

SELECT DISTINCT product_id, product_name
FROM CTE
WHERE first_sale_date BETWEEN '2019-01-01' AND '2019-03-31'
AND last_sale_date <= '2019-03-31'
;
```
['MySQL']