Sales Analysis II Problem


Description

LeetCode Problem 1083.

Table: Product

1
2
3
4
5
6
7
8
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
| unit_price   | int     |
+--------------+---------+
product_id is the primary key of this table.

Table: Sales

1
2
3
4
5
6
7
8
9
10
11
12
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| seller_id   | int     |
| product_id  | int     |
| buyer_id    | int     |
| sale_date   | date    |
| quantity    | int     |
| price       | int     |
+------ ------+---------+
This table has no primary key, it can have repeated rows.
product_id is a foreign key to Product table.

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 query result format is in the following example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Product table:
+------------+--------------+------------+
| product_id | product_name | unit_price |
+------------+--------------+------------+
| 1          | S8           | 1000       |
| 2          | G4           | 800        |
| 3          | iPhone       | 1400       |
+------------+--------------+------------+

Sales 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         | 1          | 3        | 2019-06-02 | 1        | 800   |
| 3         | 3          | 3        | 2019-05-13 | 2        | 2800  |
+-----------+------------+----------+------------+----------+-------+

Result table:
+-------------+
| buyer_id    |
+-------------+
| 1           |
+-------------+
The buyer with id 1 bought an S8 but didn't buy an iPhone. The buyer with id 3 bought both.


MySQL Solution

1
2
3
4
5
6
select distinct s.buyer_id
from Product p
join Sales s
on p.product_id=s.product_id
group by buyer_id
having sum(p.product_name='S8') > 0 and sum(p.product_name = 'iPhone') = 0




Related Posts

Game Play Analysis V Problem

LeetCode 1097. Write an SQL query that reports for each...

Unpopular Books Problem

LeetCode 1098. Write an SQL query that reports the books...

New Users Daily Count Problem

LeetCode 1107. Write an SQL query that reports for every...

Sales Analysis III Problem

LeetCode 1084. Write an SQL query that reports the products...

Sales Analysis II Problem

LeetCode 1083. Write an SQL query that reports the buyers...

Sales Analysis I Problem

LeetCode 1082. Write an SQL query that reports the best...