## 1148. Article Views I

Question:

Solution:

``````select
viewer_id as id
from(
select
viewer_id
from Views
group by 1
/* count unique articles that the authors are also the viewers */
having count(distinct case when author_id = viewer_id then article_id else null end) > 0
) a
order by 1``````

## 1149. Article Views II

Question:

Solution:

``````select distinct
viewer_id as id
from(
select
viewer_id,
view_date
from Views
group by 1,2
having count(distinct article_id) > 1
) a``````

## 1158. Market Analysis I

Question:

Solution:

``````select
u.join_date,
count(distinct case when year(o.order_date) = 2019 then o.order_id else null end) as orders_in_2019
from users u
left join orders o
group by 1,2
order by 1``````

## 1159. Market Analysis II

Question:

Solution:

``````select
u.user_id as seller_id,
case
when u.favorite_brand = i.item_brand then 'yes'
else 'no'
end as 2nd_item_fav_brand
from Users u
left join (
select
a.seller_id,
a.item_brand
from (
select
seller_id,
item_brand,
row_number() over (partition by seller_id order by order_date) as r
from Orders o
left join Items i
on o.item_id = i.item_id
) a
where r = 2 /* 2nd item sold */
) i
on u.user_id = i.seller_id
order by 1``````