Leetcode Hard Problem 3 | Market Analysis 2 | Complex SQL 9

In this video we will discuss one of the hard problem from leetcode called Market Analysis 2. here is the create and insert script for ready use :

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

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(50)
);

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

[akash]

Thank you ankit : My solution :
With finding_second_item as (
select seller_id, item_id from (
select seller_id, item_id, row_number() over(partition by seller_id order by order_date) as RN from orders
) where RN=2
)

select U.user_id as seller_id
, case when U.favorite_brand=I.item_brand then 'YES' ELSE 'NO' END as fav_or_not
from users U
left join finding_second_item FI on FI.seller_id=U.user_id
left join items I on FI.item_id=I.item_id

[arpiteshsrivastava]

Thanks ankit, My approach of course it's almost same.

with cte as
(select *,
row_number() over (partition by seller_id order by order_date) as rn
from orders)
select user_id as seller_id,
case when u.favorite_brand = i.item_brand then 'Yes' else 'No' end as item_fav_brand
from users u left join cte c on c.seller_id = u.user_id
left join items i on c.item_id = i.item_id
where rn = 2 or rn is null
order by seller_id;

[maxsteel]

Finally I understood the difference between using condition on where VS using condition on Joins -> rank =2 in present case. Thanks for simplifying this concept

[Demomail-mw]

Thank You Ankit.
My solution:
with cte as(
select * from (
select *,
row_number() over(partition by seller_id order by order_date) rn from orders)where rn=2)

select seller_id, (case when i.item_brand=u.favorite_brand then 'YES' else 'NO' end) scnd_item from cte c
join items i on c.item_id=i.item_id
join users u on c.seller_id=u.user_id
union
select distinct seller_id, 'NO' from orders where seller_id not in (select seller_id from cte)
order by 1;

[shylasree]

Hi Sir,

Shyla, here!
Your videos are amazing. Initially I was unable to write a query but now, I am able to solve complex queries as well.

Thank you! Keep posting!

[Sowmiyahere]

Thanks Ankit, for the explanation. The below query worked for me.

With sold as
(
select a.seller_id, a.item_id, i.item_brand from
(select seller_id, order_date, item_id, rank() over(partition by seller_id order by order_date) as item_rank
from orders) a, items i where a.item_rank=2 and i.item_id=a.item_id
)
select s.seller_id, case when s.item_brand=u.favorite_brand THEN 'YES' ELSE 'NO' end as if_favorite
from sold s, users1 u where s.seller_id=u.user_id

[Datapassenger_prashant]

happy teachers day sir.
After understanding the problem statement, I tried solving this problem and believe me, it just took 1 min to write the query.. I cannt believe that in just 7 SQL Problems I can be this much confident.

[sahasransupanda]

Thank you ankit, here is my solution:

with cte as(
select seller_id, i.item_id, favorite_brand, item_brand, row_number() over(partition by seller_id order by order_date) as rn,
count(*) over(partition by seller_id)as cnt from orders o
inner join users u on o.seller_id=u.user_id
inner join items i on i.item_id=o.item_id
order by order_date, seller_id
)
select seller_id, case when cnt>1 and (favorite_brand=item_brand) then "yes" else "no" end as flg from cte
where (rn=2 and cnt>1) or cnt=1

[shekharagarwal]

Thanks a lot Ankit .and second part of the question was catchy .
Please find my answer -
with o as ( select *, rank() over ( partition by seller_id order by order_date) as rk from orders )
select u.user_id, case when i.item_brand=u.favorite_brand then 'Yes' else 'No' End as fav_brand
from seller u
left outer join o on o.seller_id=u.user_id and o.rk=2
left join items i on o.item_id = i.item_id

[bidhanry]

the baby started crying when started using multiple joins 🤣

Thank you Ankit!
Here's my solution-

WITH a as (
select e.user_id,
item_brand,
CASE WHEN item_brand = favorite_brand THEN 'Yes'
WHEN item_brand is null or item_brand != favorite_brand THEN 'No' ENd as is_fav,
COUNT(1) over(partition by user_id) as cnt,
row_number() over(partition by seller_id order by order_date) as rn
from ecom_users e
LEFT JOIN orders o
ON o.seller_id = e.user_id
LEFT JOIN items i
on o.item_id=i.item_id
)
select a.user_id,
is_fav
from a
WHERE rn = 2 or cnt<2

[hairavyadav]

My approach sir:
with cte as( select item_id, seller_id from (select *, rank() over(partition by seller_id order by order_date) as rnk from orders) sal where rnk=2), cte2 as(
select u.user_id, u.favorite_brand, i.item_brand from users u left join cte as c on u.user_id=seller_id left join items as i on c.item_id=i.item_id)
select user_id as seller_id, case when favorite_brand=item_brand then "Yes" else "no" end as furv from cte2;

[LearnYouAndMe]

Thank you Ankit. Learned something new.
with sellerMinDt as
(
select t.seller_id, MIN(t.Order_date) as MinDt from orders_ma as t group by t.seller_id
),
set1 as
(
select t.seller_id, t.order_date,
iif(s.favorite_brand=i.Item_Brand, 'yes', 'no') as IsEqltoFavBrd,
ROW_NUMBER() over(partition by t.seller_id order by t.Order_date) as SubSr,
smdt.MinDt
from orders_ma as t
left join users_ma as s on s.user_id=t.seller_id
left join items_ma as i on i.item_id=t.item_id
left join sellerMinDt as smdt on smdt.seller_id=t.seller_id
)
select t.user_id, isnull(jt1.IsEqltoFavBrd,'no') as secondPurFavBrand
from users_ma as t
left join set1 as jt1 on jt1.seller_id=t.user_id and jt1.SubSr=2 and (jt1.Order_date>=jt1.MinDt)

[_Sujoy_Das]

Ankit Sir, In this question.
.
.
Shouldn't we use Dense Rank rather than Rank because it has asked for second order by date and in case our test case contains multiple orders on same day then it might lead it issues??? Please clarify!!

And I solved this question too... Nice Question sir!

[ashpoghosh]

My solution:

select user_id,
case when cnt>=2 and
then "Yes"
else "No" end as "2nd_item_fav_brand" from(
select u.user_id, u.favorite_brand, i.item_brand,
rank() over(partition by seller_id order by order_date) as rnk,
count(o.item_id) over(partition by seller_id ) as cnt
from users u left join orders o on o.seller_id = u.user_id left join items i on i.item_id=o.item_id
) temp where rnk=2 or cnt<2

[shashanksrivastava]

what about case when rank=1? it will not show up in final table.

[ashishroshan]

Hi Ankit,

Can you explain the part what happens when we write rn = 2 in the join condition

[pmohantymohanty]

ALternative Approach

Concepts Used:
1. Window functions : nth_rank
2. rows frame
3. Joins
4. aggregate function (count)
5. groupby
6. subquery


select sold_by as seller_id, favorite_brand,
case when total_orders_sold >=2 and favorite_brand = second_item_sold then 'yes' else 'no' end as condition_flag
from
(
select sold_by, count(order_date) as total_orders_sold, favorite_brand, second_item_sold from
(
select
order_date, brand_sold, sold_by, favorite_brand,
nth_value(brand_sold, 2) OVER (PARTITION BY sold_by ORDER BY order_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as second_item_sold
from
(
select o.order_id, o.order_date, o.item_id, i.item_brand as brand_sold, o.seller_id as sold_by, u.favorite_brand from orders o
join users u on o.seller_id = u.user_id
join items i on o.item_id = i.item_id
)as alias_table1
) as alias_table2
group by sold_by, favorite_brand, second_item_sold
) as alias_table3

[subhashreekarmakar]

with rank_item as
(select seller_id, order_date,
row_number () over (partition by seller_id order by order_date) as rnk
from orders ),
second_item as (
select a.*, c.item_brand from
orders a, rank_item b, items c
where a.order_date=b.order_date and b.rnk=2 and a.item_id=c.item_id
)

select distinct a.seller_id, b.favorite_brand,
case when item_brand=favorite_brand then 'yes' else 'No' end status
from second_item a, users b
where a.seller_id=b.user_id

[ravindrabisht]

Alternative

--Select *from users
--Select *from Orders
--select *from items

with rnk_orders AS
(
Select *,
rank() over(partition by seller_id order by order_date)rnk
from Orders
)
, favorite_check As
(
Select ro.*, i.item_brand, u.favorite_brand as item_fav_brand,
Case When i.item_brand=u.favorite_brand then 'Yes' else 'No' end as is_favorite
from rnk_orders ro
inner join items i on i.item_id=ro.item_id
inner join users u on ro.seller_id=u.user_id
where rnk=2
)
Select seller_id, item_fav_brand from favorite_check where is_favorite='Yes'