4.18
1) Last person to fit in the bus
select person_name
from (
select *
, sum(weight) over (order by turn) as total_weight
from queue
) as q
where total_weight <= 1000
order by turn desc
limit 1
4.19
1) Restaurant Growth
4.20
1) Movie rating
(select name as results
from movierating
join users using (user_id)
group by name
order by count(*) desc, name
limit 1)
union all
(select title as results
from movierating
join movies using (movie_id)
where created_at between '2020-02-01' and '2020-02-29'
group by title
order by avg(rating) desc, title
limit 1)
4.21
1) Capital Gain/Loss
select stock_name,
sum(
case when operation = 'Sell' then price
else -1 * price
end) as capital_gain_loss
from stocks
group by stock_name
4.22
1) Count Salary Categories
(select "High Salary" as category,
(select count(*)
from accounts
where income > 50000) as accounts_count)
union
(select "Average Salary" as category,
(select count(*)
from accounts
where income >= 20000 and income <= 50000) as accounts_count)
union
(select "Low Salary" as category,
(select count(*)
from accounts
where income < 20000) as accounts_count)
4.23
1) Confirmation Rate
select s.user_id,
case when c.action is null then 0
else round(sum(c.action = 'confirmed') / count(*),2)
end as confirmation_rate
from signups as s
left join confirmations as c on s.user_id = c.user_id
group by s.user_id
