2066,2072,2082,2084,2112,2159,2175

---

2159,2175重点

---

2066. 账户余额

# Write your MySQL query statement below
select account_id,day,
sum(if(type='Deposit',amount,-amount))
over(partition by account_id order by day )balance
from Transactions
order by account_id ,day

2072. 赢得比赛的大学

# Write your MySQL query statement below
select 
case
    when (select sum(score>=90) from NewYork) > (select sum(score>=90) 
    from California) then 'New York University' 
    when (select sum(score>=90) from NewYork) < (select sum(score>=90) 
    from California) then 'California University' 
    else  'No Winner' end winner

---

2082. 富有客户的数量

# Write your MySQL query statement below
select count(distinct customer_id) rich_count
from Store
where amount>500

2084. 为订单类型为 0 的客户删除类型为 1 的订单

# Write your MySQL query statement below
with new_table as(
    select *,rank()over(partition by customer_id order by order_type) paiming
    from Orders   
)
select order_id , customer_id , order_type
from new_table
where paiming = 1

2112. The Airport With the Most Traffic

# Write your MySQL query statement below
with new_table as (
select departure_airport jichang , flights_count zongshu
from Flights 
union all
select arrival_airport jichang , flights_count zongshu
from Flights )

select jichang airport_id
from(
select jichang,
rank()over(order by sum(zongshu) desc) paiming
from new_table
group by jichang
)xin_table
where paiming = 1

2159. 分别排序两列（using和名次的使用）

# Write your MySQL query statement below
select table_1.first_col ,table_2.second_col
from
(select first_col ,
row_number()over(order by first_col asc) paiming
from Data) table_1 
left join
(select second_col ,
row_number()over(order by second_col desc) paiming
from Data)table_2
using(paiming)

2175. 世界排名的变化（两名次相减）

# Write your MySQL query statement below
with new_table as(
    select T.*,points+points_change new_points
    from TeamPoints T left join PointsChange P 
    on T.team_id = P.team_id
)
select team_id , name , 
cast(rank()over(order by points desc ,name) as signed)  -
cast(rank()over(order by new_points desc ,name) as signed) rank_diff
#BIGINT UNSIGNED value is out of range in 
from new_table

总结

using() 函数：是针对同名字段进行连接

using(id) 等于 on A.id=B.id

using(id)===on A.id=B.id

BIGINT UNSIGNED value is out of range in…的解决方法（两名词相减）

两个名次相减方法：cast(mingci as signed)-cast(mingci2 as signed)

sql cast(lastactivity as signed)-cast(lastvisit as signed)