8.17
1) Exchange Seats
with base as (
select *
, max(id) over () as last_id
from seat
)
select case
when mod(id, 2) = 1 and last_id != id then id+1
when mod(id, 2) = 1 and last_id = id then id
else id-1
end as id
, student
from base
order by id
8.18
1) Customers Who Bought All Products
select customer_id
from Customer
group by 1
having count(distinct product_key) = (select count(*) from Product)
2) Product Sales Analysis III
select product_id,
year as first_year,
quantity,
price
from (
select *,
dense_rank () over(partition by product_id order by year) as rk
from Sales
) as t
where rk = 1
8.19
1) Market Analysis I
select u.user_id as buyer_id,
u.join_date,
sum(case when year(o.order_date) = 2019 then 1 else 0 end) as orders_in_2019
from Users u
left join orders o on u.user_id = o.buyer_id
group by 1
select round(avg(order_date = customer_pref_delivery_date)*100,2) as immediate_percentage
from Delivery
where (customer_id, order_date) in (select customer_id, min(order_date) from Delivery group by 1)
8.20
1) Monthly Transactions I
select date_format(trans_date, '%Y-%m') as month,
country,
count(*) as trans_count,
sum(case when state = 'approved' then 1 else 0 end) as approved_count,
sum(amount) as trans_total_amount,
sum(case when state = 'approved' then amount else 0 end) as approved_total_amount
from Transactions
group by 1,2
2) Last Person to Fit in the Bus
select person_name
from (
select *,
sum(weight) over(order by turn) as total_weight
from Queue
) as t
where total_weight <= 1000
order by turn desc
limit 1
8.21
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)
2) 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 1
