create table users (
user_id         int     ,
 join_date       date    ,
 favorite_brand  varchar(15));

 create table orders (
 order_id       int     ,
 order_date     date    ,
 item_id        int     ,
 buyer_id       int     ,
 seller_id      int 
 );

 create table items
 (
 item_id        int     ,
 item_brand     varchar(15)
 );


 insert into users values (1,'2019-01-01','Lenovo'),(2,'2019-02-09','Samsung'),(3,'2019-01-19','LG'),(4,'2019-05-21','HP');

 insert into items values (1,'Samsung'),(2,'Lenovo'),(3,'LG'),(4,'HP');

 insert into 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);
 

 Select * from orders;
 Select * from items;
Select * from users;
 
 
/*MARKET ANALYSIS: Write an SQL query to find for each seller,
whether the brand of the second item (by date) they sold is their favorite brand
If a seller sold less than two items, report the answer for that seller as no.
output eg : 
seller id   2nd_item_fav_brand
1           yes/no
2           yes/no
*/

--Combine order table and items table to get the item brand

WITH order_tbl as (
Select O.* ,I.item_brand
FROM orders O
JOIN items I ON  O.item_id = I.item_id )
--Combine it with User table to get everything in single table
,comb_tbl as (
SELECT U.*,O.*
FROM users U
LEFT JOIN order_tbl O  ON U.user_id = O.seller_id )

--partition the table by User_id
,comb_tbl2 as (
SELECT 
ROW_NUMBER() OVER(PARTITION BY user_id
ORDER BY user_id,order_date ) as ROW_NUM,
user_id, order_date ,item_id,item_brand,favorite_brand
FROM comb_tbl
)

,final_tbl as (
SELECT * from comb_tbl2
where ROW_NUM IN(
case when item_id IS NULL then 1
else 2 end)
)

SELECT user_id as Seller,
CASE WHEN item_brand = favorite_brand THEN 'YES'
else 'NO' END as item_fav_brand
FROM final_tbl
by