Leetcode SQL (#1097, #1098, #1107)

1097. Game Play Analysis V

Question:

https://leetcode.com/problems/game-play-analysis-v/

Solution:

select 
event_date as install_dt, 
round(count(distinct b.player_id),0) as installs, 
round(count(distinct b.retained_player_id)/count(distinct b.player_id),2) as Day1_retention
from(
    select 
    a.player_id, 
    a.event_date, 
    case 
        when datediff(next_event_date, event_date) = 1 then a.player_id
        else null
    end as retained_player_id
    from(
        select 
        player_id,
        event_date, 
        lead(event_date,1) over(partition by player_id order by event_date) as next_event_date
        from Activity
        group by 1,2
    ) a
    join (
        select 
        player_id, 
        min(event_date) as min_event_date
        from Activity
        group by 1 
    ) m
        on (a.player_id = m.player_id and a.event_date = min_event_date)
)b
group by 1
order by 1

1098. Unpopular Books

Question:

https://leetcode.com/problems/unpopular-books/

Solution:

select 
a.book_id, 
a.name
from(
    select 
    b.book_id, 
    b.name, 
    b.available_from, 
    sum(
        case 
            when o.dispatch_date <= '2019-06-23' and o.dispatch_date >= '2018-06-23' then o.quantity 
        else 0 
        end) as total_quantity
    from (
        select * 
        from Books 
        /* exclude records available in the latest month from 2019-06-23 */
        where available_from < date_sub(date_format('2019-06-23', '%Y-%m-%d') , interval 1 month) 
    ) b
        left join Orders o 
            on b.book_id = o.book_id
    group by 1,2,3
    /* total books sold <10 copies in the last year */
    having sum(
        case 
            when o.dispatch_date <= '2019-06-23' and o.dispatch_date >= '2018-06-23' then o.quantity 
            else 0 
        end) < 10
) a
order by a.book_id

1107. New Users Daily Count

Question:

https://leetcode.com/problems/new-users-daily-count/

Solution:

select 
min_date as login_date, 
count(distinct user_id) as user_count
from(
    select 
    user_id, 
    min(activity_date) as min_date
    from traffic
    where activity = 'login'
    group by 1
) t
where datediff('2019-06-30', min_date) <= 90
group by 1
order by 1

Leetcode SQL (#1082 – #1084)

1082. Sales Analysis I

Question:

https://leetcode.com/problems/sales-analysis-i/

Solution:

select 
seller_id
from (
    select 
    seller_id, 
    rank() over (order by price desc) r
    from (
        select 
        s.seller_id, 
        sum(s.price) as price
        from Sales s
        group by 1
    ) a
) b
where r = 1
order by 1

1083. Sales Analysis II

Question:

https://leetcode.com/problems/sales-analysis-ii/

Solution:

select distinct 
buyer_id
from (
    select 
    buyer_id,
    case 
        when buyer_id in (
            select distinct 
            buyer_id
            from Sales s
                join Product p 
                    on s.product_id = p.product_id
            where product_name = 'iPhone'
        ) then 1 
        else 0 
    end as iphone_buyer,
    case 
        when buyer_id in (
            select distinct 
            buyer_id
            from Sales s
                join Product p 
                    on s.product_id = p.product_id
            where product_name = 'S8'
        ) then 1 
        else 0 
    end as s8_buyer
    from Sales
) a
where iphone_buyer = 0 
and s8_buyer = 1

1084. Sales Analysis III

Question:

https://leetcode.com/problems/sales-analysis-iii/

Solution:

select distinct 
a.product_id, 
a.product_name
from(
    select distinct 
    s.product_id, 
    p.product_name, 
    min(s.sale_date) as min_sale_date, 
    max(s.sale_date) as max_sale_date
    from Sales s
        left join Product p 
            on s.product_id = p.product_id
    where quantity >=1
    group by 1,2
) a
where (
    a.min_sale_date >= '2019-01-01' 
    and a.min_sale_date <= '2019-03-31'
)
and (
    a.max_sale_date >= '2019-01-01' 
    and a.max_sale_date <= '2019-03-31'
)

Leetcode SQL (#1075 – #1077)

1075. Project Employees I

Question:

https://leetcode.com/problems/project-employees-i/

Solution:

select 
p.project_id, 
round(avg(e.experience_years),2) as average_years
from Project p
    left join Employee e 
        on p.employee_id = e.employee_id
group by 1
order by 1

1076. Project Employees II

Question:

https://leetcode.com/problems/project-employees-ii/

Solution:

select distinct 
project_id
from(
    select 
    project_id, 
    rank() over(order by employee_count desc) as r
    from(
        select 
        project_id, 
        count(distinct employee_id) as employee_count
        from Project
        group by 1
    ) a
) b
where r = 1

1077. Project Employees III

Question:

https://leetcode.com/problems/project-employees-iii/

Solution:

select 
b.project_id, 
b.employee_id
from(
    select 
    a.project_id,
    a.employee_id, 
    a.experience_years, 
    rank() over(partition by project_id order by experience_years desc) as r
    from(
    select 
        e.employee_id, 
        p.project_id, 
        e.experience_years
    from Project p
        right join Employee e 
            on p.employee_id = e.employee_id
    ) a
) b
where b.r = 1 
and b.project_id is not null
order by 1,2

Leetcode SQL (#1068 – #1070)

1068. Product Sales Analysis I

Question:

https://leetcode.com/problems/product-sales-analysis-i/

Solution:

select 
p.product_name, 
s.year, 
s.price
from Product p
    join Sales s 
        on p.product_id = s.product_id

1069. Product Sales Analysis II

Question:

https://leetcode.com/problems/product-sales-analysis-ii/

Solution:

select 
p.product_id, 
ifnull(sum(quantity),0) as total_quantity
from product p
    join sales s 
        on p.product_id = s.product_id
group by 1
order by 1

1070. Product Sales Analysis III

Question:

https://leetcode.com/problems/product-sales-analysis-iii/

Solution:

select 
product_id, 
year as first_year, 
quantity, 
price
from(
    select 
    product_id, 
    quantity, 
    price, 
    year, 
    rank() over(partition by product_id order by year) as seq
    from sales 
) s
where s.seq = 1

Leetcode SQL (#626, #627, #1045, #1050)

626. Exchange Seats

Question:

https://leetcode.com/problems/exchange-seats/

Solution:

select 
s.id, 
case 
    when type = 'odd' and next_student is not null then next_student
    when type = 'odd' and next_student is null then student
    when type = 'even' then prev_student
    else null
end as student
from(
    select 
    id, 
    student,
    lead(student,1) over(order by id) as next_student,
    lag(student,1) over (order by id) as prev_student,
    case when id % 2 = 0 then 'even' else 'odd' end as type
    from seat
) s
order by id

627. Swap Salary

Question:

https://leetcode.com/problems/swap-salary/

Solution:

update salary
set sex = (
    case 
        when sex = 'm' then 'f' 
        when sex = 'f' then 'm' 
    end
)

1045. Customers Who Bought All Products

Question:

https://leetcode.com/problems/customers-who-bought-all-products/

Solution:

select 
c.customer_id
from(
    select 
    customer_id, 
    count(distinct product_key) as products
    from Customer 
    group by 1
) c
where c.products in (
    select 
    count(distinct product_key) as products 
    from Product
)

1050. Actors & Directors Who Cooperated At Least Three Times

Question:

https://leetcode.com/problems/actors-and-directors-who-cooperated-at-least-three-times/

Solution:

select distinct 
actor_id, 
director_id
from(
    select 
    actor_id, 
    director_id, 
    count(timestamp) as freq
    from ActorDirector
    group by 1,2
    having count(timestamp) >= 3
) a
order by 1,2