1084 Sales Analysis III

Description

Table: Product

• product_id is the primary key (column with unique values) of this table.
• Each row of this table indicates the name and the price of each product.

Table: Sales

• This table can have duplicate rows.
• product_id is a foreign key (reference column) to the Product table.
• Each row of this table contains some information about one sale.

Problem Statement

Write a solution to report the products that were only sold in the first quarter of 2019. That is, between 2019-01-01 and 2019-03-31 inclusive.

Return the result table in any order. The result format is in the following example.

Example 1:

Input:

• Product table:

• Sales table:

Output:

Explanation:

• The product with id 1 was only sold in the spring of 2019.
• The product with id 2 was sold in the spring of 2019 but was also sold after the spring of 2019.
• The product with id 3 was sold after spring 2019.
• We return only product 1 as it is the product that was only sold in the spring of 2019.

Solution

There are couple of approaches you could take.

Approach 1: Using WHERE clause

First of all because you need information on product_id and product_name, you will have to join Product and Sales tables because these two set of information are in two different tables.

1WITH product_sales AS (
2    SELECT p.product_id, p.product_name, s.sale_date
3        FROM Product p
4        JOIN Sales s
5        ON p.product_id = s.product_id
6)
7SELECT product_id, product_name
8    FROM product_sales;

Next, you need to find the products that have been sold in the first quarter of 2019.

1SELECT DISTINCT product_id, product_name
2    FROM product_sales
3    WHERE sale_date BETWEEN '2019-01-01' AND '2019-03-31';

You also need to filter the products that have been sold in other quarters. This you can get using NOT IN clause.

1SELECT DISTINCT product_id, product_name
2    FROM product_sales
3    WHERE sale_date BETWEEN '2019-01-01' AND '2019-03-31'
4    AND product_id NOT IN (
5        SELECT product_id
6            FROM product_sales
7            WHERE sale_date NOT BETWEEN '2019-01-01' AND '2019-03-31'
8    );

The complete solution looks like this.

 1WITH product_sales AS (
 2    SELECT p.product_id, p.product_name, s.sale_date
 3        FROM Product p
 4        JOIN Sales s
 5        ON p.product_id = s.product_id
 6) SELECT DISTINCT product_id, product_name
 7    FROM product_sales
 8    WHERE sale_date BETWEEN '2019-01-01' AND '2019-03-31'
 9    AND product_id NOT IN (
10        SELECT product_id
11            FROM product_sales
12            WHERE sale_date NOT BETWEEN '2019-01-01' AND '2019-03-31'
13    );

Approach 2: Using GROUP BY clause

You could also find distinct products that have been sold in the first quarter of 2019 using aggregation and finding the minimum and maximum sale_date for each product_id.

1SELECT p.product_id, p.product_name
2    FROM Product p
3    JOIN Sales s
4    ON p.product_id = s.product_id
5    GROUP BY p.product_id
6    HAVING MIN(s.sale_date) >= '2019-01-01' AND MAX(s.sale_date) <= '2019-03-31';