2308. Arrange Table by Gender

在这里插入图片描述

# Write your MySQL query statement below
select user_id,gender
from(
select* ,
#对相同性别按照id升序进行排名
row_number()over(partition by gender order by user_id) same_rk,
case
    when gender ='female' then 1
    when gender = 'male' then 3
    else  2
    end diff_rk
from Genders)new_table
order by same_rk,diff_rk

2314. The First Day of the Maximum Recorded Degree in Each City

在这里插入图片描述

with new_table as(
select city_id,day,degree,
    rank()over(partition by city_id order by degree desc, day asc) rk
from Weather)

select city_id,day,degree
from new_table
where rk = 1
order by city_id

2324. Product Sales Analysis IV

在这里插入图片描述

# Write your MySQL query statement below
with new_tale as(
    select user_id ,S.product_id ,
    RANK()OVER(PARTITION BY user_id order by  sum(quantity*price) desc)rk
    from Sales S left join Product P 
    on S.product_id = P.product_id
    group by S.user_id ,S.product_id
)
SELECT user_id ,product_id
from new_tale
where rk = 1

2329. 产品销售分析Ⅴ

在这里插入图片描述

# Write your MySQL query statement below
# Write your MySQL query statement below
with new_tale as(
    select user_id ,S.product_id ,sum(quantity*price) spending
    from Sales S left join Product P 
    on S.product_id = P.product_id
    group by user_id
)
SELECT user_id ,spending
from new_tale
order by spending desc ,1

2339. All the Matches of the League

在这里插入图片描述

# Write your MySQL query statement below
select t1.team_name home_team ,t2.team_name away_team
from Teams t1,Teams t2
where t1.team_name <>t2.team_name

2346. Compute the Rank as a Percentage

在这里插入图片描述

# Write your MySQL query statement below
select student_id,department_id,
       ifnull(round((((rank()
over(partition by department_id order by mark desc))-1)*100)
       /(count(*)over(partition by department_id)-1
       ),2),0) percentage
from students