This Leetcode problem Sales Analysis II LeetCode Solution is done in SQL.
List of all LeetCode Solution
![Sales Analysis II LeetCode Solution](https://i0.wp.com/totheinnovation.com/wp-content/uploads/2024/02/LeetCode-Problem-Solution.png?resize=200%2C200&ssl=1)
Sales Analysis II LeetCode Solution
Table of Contents
Problem Statement
Column Name | Type |
product_id | int |
product_name | varchar |
unit_price | int |
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.
Column Name | Type |
seller_id | int |
product_id | int |
buyer_id | int |
sale_date | date |
quantity | int |
price | int |
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.
Write an SQL query that reports the buyers who have bought S8 but not iPhone. Note that S8 and iPhone are products present in the Product table.
The result format is in the following example.
product_id | product_name | unit_price |
1 | S8 | 1000 |
2 | G4 | 800 |
3 | iPhone | 1400 |
Product table:
seller_id | product_id | buyer_id | sale_date | quantity | price |
1 | 1 | 1 | 2019-01-21 | 2 | 2000 |
1 | 2 | 2 | 2019-02-17 | 1 | 800 |
2 | 2 | 3 | 2019-06-02 | 1 | 800 |
3 | 3 | 4 | 2019-05-13 | 2 | 2800 |
<strong>Output:</strong>
Code language: HTML, XML (xml)
buyer_id |
1 |
Explanation:
The buyer with id 1 bought an S8 but didn’t buy an iPhone. The buyer with id 3 bought both.
Sales Analysis II LeetCode Solution MySQL
select
distinct s.buyer_id
from
Sales as s
join Product as p on s.product_id = p.product_id
where
product_name = 'S8'
and s.buyer_id not in (
select
buyer_id
from
Sales as s
join Product as p on s.product_id = p.product_id
where
product_name = 'iPhone'
);
Code language: SQL (Structured Query Language) (sql)
Sales Analysis II LeetCode Solution MySQL (Another approach)
select
buyer_id
from
Sales
join Product using(product_id)
group by
buyer_id
having
sum(product_name = 'S8') > 0
and sum(product_name = 'iPhone') = 0;
Code language: SQL (Structured Query Language) (sql)