力扣 sql 中等&困难完成打卡 ~ 简单题还剩一些有缘再看叭😗

困难

1384.按年度列出销售总额

每个产品每年的总销售额，并包含 product_id, product_name 以及 report_year total_amount

销售年份的日期介于 2018 年到 2020 年之间。你返回的结果需要按 product_id 和 report_year 排序。

select tmp.product_id, product_name, report_year, average_daily_sales * days as total_amount
from(
    select product_id,
    "2018" as report_year,
    average_daily_sales,
    (case when period_end > "2019-01-01"  
    then datediff("2019-01-01", period_start) 
    else datediff(period_end, period_start)+1 end) as days
from sales
where period_start<="2018-12-31" 
union all
select  product_id,
     "2019" as report_year,
     average_daily_sales,
     (case when period_end>="2020-01-01" and period_start<"2019-01-01"
     then  datediff("2020-01-01", "2019-01-01") 
     when period_end>="2020-01-01" and period_start>"2019-01-01"
     then  datediff("2020-01-01", period_start) 
     when period_end<"2020-01-01" and period_start<"2019-01-01"
     then  datediff(period_end, "2018-12-31") 
     when period_end<"2020-01-01" and period_start>"2019-01-01"
     then datediff(period_end, period_start)+1
     else 0 end 
     ) as days
from sales
where period_start<="2019-12-31" and period_end>="2019-01-01"
union all
select  product_id,
     "2020" as report_year,
     average_daily_sales,
     (
         case when period_start<"2020-01-01" 
         then datediff(period_end, "2019-12-31")
         else datediff(period_end, period_start)+1 end
     )as days
from sales
where period_end>="2020-01-01" 
) tmp
left join Product
on tmp.product_id = Product.product_id
order by tmp.product_id asc, report_year asc;

1412. 查找成绩处于中游的学生

成绩处于中游的学生是指至少参加了一次测验, 且得分既不是最高分也不是最低分的学生。

写一个 SQL 语句，找出在 所有 测验中都处于中游的学生 (student_id, student_name)。

不要返回从来没有参加过测验的学生。返回结果表按照 student_id 排序。

with tmp as(
    select exam_id, student_id,
    rank() over(partition by exam_id order by score asc) rk1,
    rank() over(partition by exam_id order by score desc) rk2
    from Exam
)

select distinct tmp.student_id, student_name
from tmp
left join Student
on tmp.student_id = Student.student_id
where tmp.student_id not in (
    select distinct student_id
    from tmp
    where rk1=1 or rk2=1
)
order by tmp.student_id

1479.周内每天的销售情况

报告 周内每天 每个商品类别下订购了多少单位。返回结果表单 按商品类别排序

select item_category as Category,
    sum(case when date_format(order_date,"%W")="Monday"then quantity else 0 end) as Monday,
    sum(case when date_format(order_date,"%W")="Tuesday"then quantity else 0 end) as Tuesday,
    sum(case when date_format(order_date,"%W")="Wednesday"then quantity else 0 end) as Wednesday,
    sum(case when date_format(order_date,"%W")="Thursday"then quantity else 0 end) as Thursday,
    sum(case when date_format(order_date,"%W")="Friday"then quantity else 0 end) as Friday,
    sum(case when date_format(order_date,"%W")="Saturday"then quantity else 0 end) as Saturday,
    sum(case when date_format(order_date,"%W")="Sunday"then quantity else 0 end) as Sunday
from Orders
right join Items
on Orders.item_id = Items.item_id
group by Category
order by Category;

中等

1440. 计算布尔表达式的值

以计算表 Expressions 中的布尔表达式.

select t1.*, 
    if((case 
            when operator = ">" then t2.value>t3.value
            when operator = "<" then t2.value<t3.value
        else t2.value=t3.value end) = 1,"true", "false") as value
from Expressions t1
left join `Variables` t2 on t1.left_operand = t2.`name`
left join `Variables` t3 on t1.right_operand = t3.`name`

1454.至少连续 5 天登录的用户名

select  distinct t2.id, t3.name
from
(
    select id, login_date,
        date_sub(login_date, interval(row_number() over(partition by id order by login_date asc)) day) as date_rn
    from (
        select distinct id, login_date
        from Logins
    ) t1
) t2
left join Accounts t3 on t2.id = t3.id
group by t2.id, date_rn
having count(login_date)>=5
order by t2.id;

1459. 矩形面积

由表中任意两点可以形成的所有可能的矩形（仅考虑边与 x 轴和 y 轴平行的情况）。

结果表中的每一行包含三列 (p1, p2, area) 如下:

p1 和 p2 是矩形两个对角的点的 id 且 p1 < p2
矩形的面积由列 area 表示
请按照面积大小降序排列，如果面积相同的话, 则按照 p1 和 p2 升序对结果表排序

select t1.id as p1, t2.id as p2, abs(t1.x_value-t2.x_value) * abs(t1.y_value-t2.y_value)  as area
from points t1, points t2
where t1.id<t2.id
and  t1.x_value-t2.x_value<>0 
and  t1.y_value-t2.y_value<>0
order by area desc, p1 asc, p2 asc;

1501.可以放心投资的国家

想要投资的国家是: 该国的平均通话时长要严格地大于全球平均通话时长.

回忆: 字符串处理部分函数

select length('ysy'),
		upper('ysy'),lower('YSy'),

    # 截取 切片(py) / substr(r)
    left('Kindergarten', 4),   # 前4个
    right('Kindergarten', 6),  # 后6个
    substring('Kindergarten', 3, 2),   # 从第3个（起始位置）往后的2个字母
    
    # 替换 replace(py) / gsub(r)
    replace('Kindergarten', 'garten','garden'),
    
    # 连接 +(py) / paste (r)
    concat('aa','+','bb')   # 输出aa+bb
;

select distinct Country.`name` as country
from Calls
left join person
on (Calls.caller_id = Person.id or Calls.callee_id = Person.id)
left join Country on Country.country_code = left(person.phone_number,3)
group by left(phone_number,3)
having avg(duration)>(
    select avg(duration)
    from Calls
)

1555.银行账户概要

查询每个用户的当前余额，并检查他们是否已透支（当前额度小于 0）

select 
    t3.user_id, 
    user_name, credit1 + sum(ifnull(t4.amount,0)) as credit,
    if(credit1 + sum(ifnull(t4.amount,0))<0,"Yes","No") as credit_limit_breached
from
(
    select t1.user_id, t1.user_name, 
        t1.credit - sum(ifnull(t2.amount,0)) as credit1
    from users t1
    left join transactions t2 on t1.user_id = t2.paid_by
    group by t1.user_id
) t3
left join transactions t4 
on t3.user_id = t4.paid_to
group by t3.user_id

1613. 找到遗失的ID——递归（需复习）

遗失的顾客id是指那些不在 Customers 表中, 值却处于 1 和表中最大 customer_id 之间的id.

-- 递归生成1到100
with recursive t1 as 
(
    select 1 as n
    union all 
    select n+1 from t1 where n < 100)
-- 按照条件筛选
select n as ids
from t1
where n >=1
and n <= (
    select max(customer_id)
    from Customers
)
and n not in (
    select distinct customer_id
    from Customers
)
order by ids

1709. 访问日期之间的最大空档期

假设今天的日期是 '2021-1-1' 。编写 SQL 语句，对于每个 user_id ，求出相邻访问（若该次访问是最后一次，则为今天【这里暗示要 left join 】）之间最大的空档期天数 window 。

with tmp as(
    select user_id, visit_date, 
        row_number() over(partition by user_id order by visit_date asc) as rn
    from UserVisits
)

select t1.user_id as user_id,
    max(datediff(ifnull(t2.visit_date,"2021-01-01"), t1.visit_date)) as biggest_window
from tmp t1
left join tmp t2
on t1.user_id = t2.user_id
and t1.visit_date<t2.visit_date
and t1.rn+1 = t2.rn
group by t1.user_id
order by user_id;

中等且无聊

前面的感觉还不错，后面的是无聊的中等题：

1468. 税后工资

如果这个公司所有员工最高工资不到 1000 ，税率为 0%
如果这个公司所有员工最高工资在 1000 到 10000 之间，税率为 24%
如果这个公司所有员工最高工资大于 10000 ，税率为 49%
按任意顺序返回结果，税后工资结果取整

select t1.company_id,employee_id, employee_name, round(salary*ratio,0) as salary
from Salaries t1
left join 
(
    select company_id,
        (case 
        when max(salary)<1000 then 1
        when max(salary) between 1000 and 10000 then 1-0.24
        when max(salary) > 10000 then 1-0.49
    else null end) as ratio
    from salaries
    group by company_id
) t2
on t1.company_id  = t2.company_id;

1532.最近的三笔订单

找到每个用户的最近三笔订单。如果用户的订单少于 3 笔，则返回他的全部订单。

select Customers.`name` as customer_name, t1.customer_id as customer_id, order_id, order_date
from
(
    select customer_id,  order_id, order_date, 
        row_number()over(partition by customer_id order by order_date desc) as rk
    from orders
) t1
left join Customers
on t1.customer_id = Customers.customer_id
where rk<=3
order by customer_name asc, customer_id asc, order_date desc;

1596. 顾客最经常订购的商品

找到每一个顾客最经常订购的商品（可能不止一种）。表单应该有每一位至少下过一次单的顾客 customer_id , 他最经常订购的商品的 product_id 和 product_name

select t1.customer_id, t1.product_id, t2.product_name
from
(
    select customer_id, product_id, count(order_id), 
        rank() over (partition by customer_id order by count(order_id) desc) as rk
    from orders
    group by customer_id, product_id
) t1
left join Products t2 on t1.product_id = t2.product_id
where rk = 1;

1715. 苹果和橘子的个数

查询每个大箱子中苹果和橘子的个数。如果大箱子中包含小盒子，还应当包含小盒子中苹果和橘子的个数。

select sum(ifnull(boxes.apple_count,0))+sum(ifnull(chests.apple_count,0)) as apple_count,
    sum(ifnull(boxes.orange_count,0))+sum(ifnull(chests.orange_count,0)) as orange_count
from boxes 
left join chests
on boxes.chest_id = chests.chest_id

1699.两人的通话次数

查询每一对用户 (person1, person2) 之间的通话次数和通话总时长，其中 person1 < person2 。

select person1, person2, count(1) as call_count, sum(duration) as total_duration
from (
    select 
        if(from_id<to_id, from_id, to_id) as person1,
        if(from_id<to_id, to_id, from_id) as person2,
        duration
    from Calls
) tmp 
group by person1, person2

1549. 每件商品的最新订单

SELECT product_name, t.product_id, order_id, order_date
FROM(
    SELECT *, rank() OVER(PARTITION BY product_id ORDER BY order_date DESC) AS rk 
    FROM Orders
) t LEFT JOIN Products p USING(product_id)
WHERE rk = 1
ORDER BY product_name, product_id, order_id

1445. 苹果 和 桔子

报告每一天 苹果 和 桔子 销售的数目的差异.

select sale_date, 
    sum(case when fruit="apples" then sold_num else 0 end) - sum(case when fruit="oranges" then sold_num else 0 end) as diff
from Sales
group by sale_date
order by sale_date

1398.购买了 A B 却没有买 C

SELECT customer_id,customer_name 
FROM Customers 
WHERE 
customer_id IN(SELECT customer_id FROM Orders WHERE product_name='A') 
AND 
customer_id IN(SELECT customer_id FROM Orders WHERE product_name='B') 
AND 
customer_id NOT IN(SELECT customer_id FROM Orders WHERE product_name='C')

1393. 股票损益

编写一个SQL查询来报告每支股票的资本损益。

股票的资本损益是一次或多次买卖股票后的全部收益或损失。

以任意顺序返回结果即可。

with tmp as(
  select stock_name, operation, price,
		row_number()over(partition by stock_name order by operation_day) as rn
	from stocks
)
select t1.stock_name, sum(t2.price - t1.price) as capital_gain_loss
from tmp t1, tmp t2
where t1.stock_name = t2.stock_name
and t1.operation = "buy" 
and t2.operation = "sell"
and t1.rn+1 = t2.rn
group by t1.stock_name

1421.净现值查询

找到 Queries 表中每一次查询的净现值.

select q.id,q.year,ifnull(n.npv,0) npv 
from queries q left join npv n
on q.id=n.id and q.year=n.year