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