## 1097. Game Play Analysis V

Question:

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:

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:

Solution:

``````select
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``````

## 1082. Sales Analysis I

Question:

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:

Solution:

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

## 1084. Sales Analysis III

Question:

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'
)``````

## 1075. Project Employees I

Question:

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:

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:

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``````

## 1068. Product Sales Analysis I

Question:

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:

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:

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``````

## 626. Exchange Seats

Question:

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:

Solution:

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

## 1045. Customers Who Bought All Products

Question:

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:

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``````