# Exercise 11：1159.Market Analysis II

## 1.Description

Table: `Users`

```
+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| user_id        | int     |
| join_date      | date    |
| favorite_brand | varchar |
+----------------+---------+user_id is the primary key of this table.
This table has the info of the users of an online shopping website where users can sell and buy items.
```

Table: `Orders`

```
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| order_date    | date    |
| item_id       | int     |
| buyer_id      | int     |
| seller_id     | int     |
+---------------+---------+order_id is the primary key of this table.
item_id is a foreign key to the Items table.
buyer_id and seller_id are foreign keys to the Users table.
```

Table: `Items`

```
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| item_id       | int     |
| item_brand    | varchar |
+---------------+---------+item_id is the primary key of this table.
```

Write an SQL query to find for each user, whether the brand of the second item (by date) they sold is their favorite brand. If a user sold less than two items, report the answer for that user as no.

It is guaranteed that no seller sold more than one item on a day.

The query result format is in the following example:

```
Users table:
+---------+------------+----------------+
| user_id | join_date  | favorite_brand |
+---------+------------+----------------+
| 1       | 2019-01-01 | Lenovo         |
| 2       | 2019-02-09 | Samsung        |
| 3       | 2019-01-19 | LG             |
| 4       | 2019-05-21 | HP             |
+---------+------------+----------------+

Orders table:
+----------+------------+---------+----------+-----------+
| order_id | order_date | item_id | buyer_id | seller_id |
+----------+------------+---------+----------+-----------+
| 1        | 2019-08-01 | 4       | 1        | 2         |
| 2        | 2019-08-02 | 2       | 1        | 3         |
| 3        | 2019-08-03 | 3       | 2        | 3         |
| 4        | 2019-08-04 | 1       | 4        | 2         |
| 5        | 2019-08-04 | 1       | 3        | 4         |
| 6        | 2019-08-05 | 2       | 2        | 4         |
+----------+------------+---------+----------+-----------+

Items table:
+---------+------------+
| item_id | item_brand |
+---------+------------+
| 1       | Samsung    |
| 2       | Lenovo     |
| 3       | LG         |
| 4       | HP         |
+---------+------------+

Result table:
+-----------+--------------------+
| seller_id | 2nd_item_fav_brand |
+-----------+--------------------+
| 1         | no                 |
| 2         | yes                |
| 3         | yes                |
| 4         | no                 |
+-----------+--------------------+

The answer for the user with id 1 is no because they sold nothing.
The answer for the users with id 2 and 3 is yes because the brands of their second sold items are their favorite brands.
The answer for the user with id 4 is no because the brand of their second sold item is not their favorite brand.
```

## 2.Create Table and insert into values

```sql
create table if not exists leetcode.ex_1159_user
(user_id	string, join_date date,favorite_brand	string) stored as orc ;

INSERT INTO table leetcode.ex_1159_user VALUES
('1'  ,'2019-01-01','Lenovo'), 
('2'  ,'2019-02-09','Samsung'), 
('3'  ,'2019-01-19','LG'), 
('4'  ,'2019-05-21','HP') 
;

create table if not exists leetcode.ex_1159_orders
(order_id			string, 
 order_date 	date,
 item_id 			string,
 buyer_id			string,
 seller_id		string
) stored as orc ;

INSERT INTO table leetcode.ex_1159_orders VALUES
('1'  ,'2019-08-01' ,'4'  ,'1'  ,'2'),
('2 ' ,'2019-08-02' ,'2'  ,'1'  ,'3'),
('3 ' ,'2019-08-03' ,'3'  ,'2'  ,'3'), 
('4'  ,'2019-08-04' ,'1'  ,'4'  ,'2'),
('5 ' ,'2019-08-04' ,'1'  ,'3'  ,'4'),
('6 ' ,'2019-08-05' ,'2'  ,'2'  ,'4')
;

create table if not exists leetcode.ex_1159_items
(item_id	string, item_brand	string) stored as orc ;

INSERT INTO table leetcode.ex_1159_items VALUES
('1','Samsung'),('2','Lenovo'),('3','LG'),('4','HP') ; 
```

{% hint style="info" %}
Result table要返回所有user\_id，不管是否有order紀錄，注意JOIN的主表

The result table should return all user\_id, regardless of whether the user has an order record, pay attention to the type of JOIN
{% endhint %}