Leetcode SQL (#1148 – #1149, #1158 – #1159)

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

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

select 
u.user_id as buyer_id, 
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 
        on u.user_id = o.buyer_id
group by 1,2
order by 1

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