1083 Sales Analysis II

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 might have repeated rows.
• product_id is a foreign key (reference column) to the Product table.
• buyer_id is never NULL.
• sale_date is never NULL.
• Each row of this table contains some information about one sale.

Problem Statement

Write a solution to report the buyers who have bought S8 but not iPhone. Note that S8 and iPhone are products presented in the Product table.

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 buyer with id 1 bought an ‘S8’ but did not buy an ‘iPhone’. The buyer with id 3 bought both.

Solution

Here, you want to find buyers who have not bought an iPhone. In order to find information on the product, you first need to join Product and Sales table. You can assign this to a temporary view.

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

Next, you need to filter the buyers who have bought an iPhone. How do you find the buyers who have ever bought iPhone?

1SELECT buyer_id 
2    FROM product_sales
3    WHERE product_name = 'iPhone';

Next, you need to find the buyers who have bought S8, but not iPhone. Notice that this is exclusive-OR relationship. If the user has bought both models, they should not be included in the output. You can achieve this using WHERE clause.

1SELECT DISTINCt buyer_id
2    FROM product_sales
3    WHERE product_name = 'S8'
4    AND buyer_id NOT IN (
5        SELECT buyer_id 
6            FROM product_sales
7            WHERE product_name = 'iPhone'
8    );

The complete solution looks like this.

 1WITH product_sales AS (
 2    SELECT p.product_id, p.product_name, s.buyer_id 
 3        FROM Product p
 4        JOIN Sales s ON p.product_id = s.product_id
 5) SELECT DISTINCT buyer_id 
 6    FROM product_sales
 7    WHERE product_name = 'S8' AND buyer_id NOT IN (
 8        SELECT buyer_id 
 9            FROM product_sales 
10            WHERE product_name = 'iPhone'
11    );