Last updated on January 21st, 2025 at 09:56 pm

Here, we see the Sales Analysis II LeetCode Solution. This Leetcode problem is solved using MySQL and Pandas.

List of all LeetCode Solution

Level of Question

Easy

Sales Analysis II LeetCode Solution

1. 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

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

Output:

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.

2. Code Implementation in Different Languages

2.1 Sales Analysis II 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'
  );

2.2 Sales Analysis II 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;