行转列（根据枚举）

1777. 每家商店的产品价格

select
    product_id,
    sum(if(store='store1',price,null)) store1,
    sum(if(store='store2',price,null)) store2,
    sum(if(store='store3',price,null)) store3
from
    Products
group by
    1

1811. 寻找面试候选人

# Write your MySQL query statement below

#  所有面试候选人 的姓名 name 和邮件 mail
# 1. 该用户在 连续三场及更多 比赛中赢得 任意 奖牌
# 2. 该用户在 三场及更多不同的 比赛中赢得 金牌（这些比赛可以不是连续的）
# 3. 所有连续的比赛都有连续的ID，没有ID被跳过

with a as(
select
    * from
(select contest_id, gold_medal model from Contests
    union 
select contest_id, silver_medal model from Contests
    union
select contest_id, bronze_medal model from Contests) temp
    order by contest_id)

select name, mail from
(select distinct a1.model id from a a1 left join a a2 on a1.contest_id = a2.contest_id - 1 left join a a3 on
    a2.contest_id = a3.contest_id - 1
    where a1.model = a2.model and a2.model = a3.model
union
select gold_medal from
(select gold_medal, count(gold_medal) ct from Contests
    group by gold_medal 
    having ct >= 3) temp1) temp2 left join Users on
    temp2.id = user_id 

LintCode 每个时间段的订单量

select 
    customer_id,
    sum(if(datediff("2023-04-03",order_date)<=30,1,0)) as orders_30d,
    sum(if(datediff("2023-04-03",order_date)<=30,amount,0)) as amount_30,
    sum(if(datediff("2023-04-03",order_date)<=60,1,0)) as orders_60d,
    sum(if(datediff("2023-04-03",order_date)<=60,amount,0)) as amount_60,
    sum(if(datediff("2023-04-03",order_date)<=90,1,0)) as orders_90d,
    sum(if(datediff("2023-04-03",order_date)<=90,amount,0)) as amount_90
from orders
group by customer_id