力扣50题后半部分

Majoryx

已于 2024-06-12 15:33:09 修改

阅读量1.3k

点赞数 47

文章标签： leetcode 算法职场和发展

于 2024-05-27 23:51:49 首次发布

本文链接：https://blog.csdn.net/Majoryx/article/details/139223224

版权

23.查询近30天活跃用户数

编写解决方案，统计截至 2019-07-27（包含2019-07-27），近 30 天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）。

以 任意顺序 返回结果表。

SELECT activity_date AS day , COUNT(DISTINCT user_id) AS active_users
FROM activity
WHERE activity_date BETWEEN '2019-06-28' AND '2019-07-27'
GROUP BY activity_date

24.销售分析III

编写解决方案，报告2019年春季才售出的产品。即仅在2019-01-01至2019-03-31（含）之间出售的商品。

以 任意顺序 返回结果表

SELECT p.product_id, p.product_name
FROM Product p
INNER JOIN (
    SELECT product_id, MIN(sale_date) AS min_sale_date, MAX(sale_date) AS max_sale_date
    FROM Sales
    GROUP BY product_id
) s ON p.product_id = s.product_id
WHERE s.min_sale_date >= '2019-01-01' AND s.max_sale_date <= '2019-03-31';

select p.product_id ,p.product_name

from Product p inner join Sales s

on p.product_id=s.product_id

where min(s.sale_date)>= '2019-01-01' and max(s.sale_date)<= '2019-03-31'

一开始这样写是不对的，因为min（）max（）是聚合函数不能直接用，需要有group by，那就可以引入。

25.超过5名学生的课

查询 至少有5个学生 的所有班级。

以 任意顺序 返回结果表。

select class from Courses
group by class  having  count(student) >= 5

26.求关注者的数量

编写解决方案，对于每一个用户，返回该用户的关注者数量。

按 user_id 的顺序返回结果表

select user_id ,count(follower_id) as followers_count

from Followers

group by user_id

order by user_id

27.只出现一次的最大数字

单一数字 是在 MyNumbers 表中只出现一次的数字。

找出最大的 单一数字 。如果不存在 单一数字 ，则返回 null 。

select  ifnull 
((select max(num) from MyNumbers group by num having count(*)=1  order by num desc limit 1),
null) as num

首先筛选出只出现一次的可以用 select num from 表 group by count（*）=1 order by num desc limit 1

然后借助 ifnull函数来操作

ifnull（（....如果不是null 则输出这里的数值），null）

select ifnull () as num

28.查询结果的质量和占比

将查询结果的质量 quality 定义为：

各查询结果的评分与其位置之间比率的平均值。

将劣质查询百分比 poor_query_percentage 为：

评分小于 3 的查询结果占全部查询结果的百分比。

编写解决方案，找出每次的 query_name 、 quality 和 poor_query_percentage。

quality 和 poor_query_percentage 都应 四舍五入到小数点后两位 。

以 任意顺序 返回结果表。

SELECT 
    query_name, 
    ROUND(AVG(rating/position), 2) quality,
    ROUND(SUM(IF(rating < 3, 1, 0)) * 100 / COUNT(*), 2) poor_query_percentage
FROM Queries
GROUP BY query_name

29.每月交易1

编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。

以 任意顺序 返回结果表。

select 
    date_format(trans_date,'%Y-%m') as month,  
    country, 
    count(*) as trans_count,
    sum(if(state='approved',1,0)) as approved_count,
    sum(amount) as trans_total_amount,
    sum(if(state='approved',amount,0))as approved_total_amount
from Transactions 
group by date_format(trans_date ,'%Y-%m'),country

30.即时食物配送 II

如果顾客期望的配送日期和下单日期相同，则该订单称为「即时订单」，否则称为「计划订单」。

「首次订单」是顾客最早创建的订单。我们保证一个顾客只会有一个「首次订单」。

编写解决方案以获取即时订单在所有用户的首次订单中的比例。保留两位小数。

select round (
    sum(order_date = customer_pref_delivery_date) * 100 /
    count(*),
    2
) as immediate_percentage
from Delivery
where (customer_id, order_date) in (
    select customer_id, min(order_date)
    from delivery
    group by customer_id
)

(order_date = customer_pref_delivery_date) 是一个条件表达式，它对于每一行会返回布尔值（TRUE 或 FALSE）。在 SQL 中，布尔值 TRUE 可以被视作 1，而 FALSE 可以被视作 0。
计算平均百分比： AVG(order_date = customer_pref_delivery_date) 计算的是这个条件表达式为 TRUE 的比例，即 order_date 和 customer_pref_delivery_date 相等的行占总行数的比例。因为布尔值被转换为 1 和 0，所以这个平均值实际上就是匹配的比例（以小数形式表示）

31.游戏玩法分析IV

编写解决方案，报告在首次登录的第二天再次登录的玩家的比率，四舍五入到小数点后两位。换句话说，你需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。

 SELECT ROUND(COUNT(DISTINCT player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction
FROM Activity
WHERE (player_id, event_date) IN (
  SELECT player_id, MIN(event_date) + INTERVAL 1 DAY
  FROM Activity
  GROUP BY player_id

也可以用两个表连接，主要是 min（）+ interval 1 day 这个有点牛逼

32.买下所有产品的客户

编写解决方案，报告 Customer 表中购买了 Product 表中所有产品的客户的 id。

SELECT 
    customer_id
FROM 
    Customer
GROUP BY 
    customer_id
HAVING 
    COUNT(DISTINCT product_key) = (SELECT COUNT(*) FROM Product);

第二个表产品表中，计算行可以知道有多少种产品。

然后按照每个id分组，计算每个组里面的（去重产品）行数与产品的数量相等那就OK了。

33.每位经理的下属员工数量

对于此问题，我们将至少有一个其他员工需要向他汇报的员工，视为一个经理。

编写一个解决方案来返回需要听取汇报的所有经理的 ID、名称、直接向该经理汇报的员工人数，以及这些员工的平均年龄，其中该平均年龄需要四舍五入到最接近的整数。

返回的结果集需要按照 employee_id 进行排序。

select a.employee_id , a.name , count(b.employee_id) as reports_count, round(avg(b.age), 0) as average_age
from Employees a inner join Employees b
on a.employee_id = b.reports_to
group by a.employee_id 
order by a.employee_id

34.员工的直属部门

一个员工可以属于多个部门。当一个员工加入超过一个部门的时候，他需要决定哪个部门是他的直属部门。请注意，当员工只加入一个部门的时候，那这个部门将默认为他的直属部门，虽然表记录的值为'N'.

请编写解决方案，查出员工所属的直属部门。

返回结果 没有顺序要求 。

select employee_id, department_id
from Employee
where primary_flag = 'Y' or employee_id in (
    select employee_id
    from Employee
    group by employee_id
    having count(employee_id) = 1
)

35.判断三角形

对每三个线段报告它们是否可以形成一个三角形。

以 任意顺序 返回结果表。

查询结果格式如下所示。

select x,y,z, if(x+y>z and x+z>y and y+z>x and x>0 and y>0 and z>0,'Yes','No')as triangle 
from Triangle

36.连续出现的数字

找出所有至少连续出现三次的数字。

返回的结果表中的数据可以按 任意顺序 排列。

结果格式如下面的例子所示：

select distinct l1.num as ConsecutiveNums
from Logs l1,Logs l2,Logs l3
where 
l1.id =l2.id -1
and
l2.id=l3.id-1
and
l1.num=l2.num
and
l3.num=l2.num

37.指定日期的产品价格

编写一个解决方案，找出在 2019-08-16 时全部产品的价格，假设所有产品在修改前的价格都是 10 。

以 任意顺序 返回结果表。

select product_id,new_price as price from Products
where (product_id,change_date) in
(select product_id,max(change_date ) 
from Products 
where change_date <='2019-08-16' 
group by product_id)

union all

select product_id,10 from Products
group by product_id
having min(change_date)>'2019-08-16'

38.最后一个能进入巴士的人

有一队乘客在等着上巴士。然而，巴士有1000 千克的重量限制，所以其中一部分乘客可能无法上巴士。

编写解决方案找出 最后一个 上巴士且不超过重量限制的乘客，并报告 person_name 。题目测试用例确保顺位第一的人可以上巴士且不会超重。

select 
    person_name 
from 
    (select person_name, turn, sum(weight) over(order by turn) as total 
    from Queue) t 
where total <= 1000 
order by turn desc 
limit 1
#sum(weight) over(order by turn) 桉turn的顺序求和相加，
#先做一个包含sum求和的表，然后再做一次筛选。

39.按分类统计薪水

查询每个工资类别的银行账户数量。工资类别如下：

"Low Salary"：所有工资 严格低于 20000 美元。
"Average Salary"：包含范围内的所有工资 [$20000, $50000] 。
"High Salary"：所有工资 严格大于 50000 美元。

结果表必须包含所有三个类别。如果某个类别中没有帐户，则报告 0 。

按 任意顺序 返回结果表。

SELECT 
    CASE 
        WHEN income < 20000 THEN 'Low Salary'
        WHEN income >= 20000 AND income <= 50000 THEN 'Average Salary'
        WHEN income > 50000 THEN 'High Salary'
    END AS category,
    COUNT(*) AS accounts_count
FROM 
    Accounts
GROUP BY 
    CASE 
        WHEN income < 20000 THEN 'Low Salary'
        WHEN income >= 20000 AND income <= 50000 THEN 'Average Salary'
        WHEN income > 50000 THEN 'High Salary'
    END
UNION
SELECT 
    'Low Salary' AS category,
    0 AS accounts_count
WHERE NOT EXISTS (SELECT 1 FROM Accounts WHERE income < 20000)
UNION
SELECT 
    'Average Salary' AS category,
    0 AS accounts_count
WHERE NOT EXISTS (SELECT 1 FROM Accounts WHERE income >= 20000 AND income <= 50000)
UNION
SELECT 
    'High Salary' AS category,
    0 AS accounts_count
WHERE NOT EXISTS (SELECT 1 FROM Accounts WHERE income > 50000)

40.修复表中的名字

编写解决方案，修复名字，使得只有第一个字符是大写的，其余都是小写的。

返回按 user_id 排序的结果表。

SELECT user_id, concat(upper(SUBSTRING(name,1,1)),LOWER(SUBSTRING(name,2))) as name
from Users
order by user_id

SUBSTRING(column_name, start, length)：这将从列的值中提取一个子字符串，从指定的起始位置开始，直到指定的长度。

UPPER(expression)：这会将字符串表达式转换为大写。

LOWER(expression)：这会将字符串表达式转换为小写。

CONCAT(string1, string2, ...)：这会将两个或多个字符串连接成一个字符串。

41.患某种疾病的患者

查询患有 I 类糖尿病的患者 ID （patient_id）、患者姓名（patient_name）以及其患有的所有疾病代码（conditions）。I 类糖尿病的代码总是包含前缀 DIAB1 。

select * from
Patients
where conditions like 'DIAB1%' or conditions like '% DIAB1%'

42.删除重复的电子邮箱

编写解决方案删除所有重复的电子邮件，只保留一个具有最小 id 的唯一电子邮件。

（对于 SQL 用户，请注意你应该编写一个 DELETE 语句而不是 SELECT 语句。）

运行脚本后，显示的答案是 Person 表。驱动程序将首先编译并运行您的代码片段，然后再显示 Person 表。Person 表的最终顺序 无关紧要 。

delete p1 from
Person p1,Person p2
where 
p1.email=p2.email and p1.id>p2.id

43.第二高的薪水

查询并返回 Employee 表中第二高的薪水。如果不存在第二高的薪水，查询应该返回 null(Pandas 则返回 None) 。

查询结果如下例所示。

SELECT
(SELECT DISTINCT salary from Employee
order by salary DESC
limit 1 offset 1)
AS SecondHighestSalary

offset 1 跳过第一个

44.按日期分组销售产品

编写解决方案找出每个日期、销售的不同产品的数量及其名称。
每个日期的销售产品名称应按词典序排列。
返回按 sell_date 排序的结果表。

SELECT 
    sell_date,
    COUNT(DISTINCT(product)) AS num_sold, 
    GROUP_CONCAT(DISTINCT product ORDER BY product SEPARATOR ',') AS products
FROM 
    Activities
GROUP BY 
    sell_date
ORDER BY 
    sell_date ASC

GROUP_CONCAT(DISTINCT product ORDER BY product SEPARATOR ',') AS products

SEPARATOR 分隔符 ‘ 逗号 ’ ，GROUP_CONCAT( ）用于将多个行中的值合并成一个字符串。

45.列出指定时间段内所有的下单产品

写一个解决方案，要求获取在 2020 年 2 月份下单的数量不少于 100 的产品的名字和数目。

返回结果表单的 顺序无要求 。

select p.product_name,sum(o.unit) unit from
products p left join orders o on p.product_id = o.product_id
where o.order_date like'2020-02-%'
group by product_name having unit >=100

46.查找拥有有效邮箱的用户

编写一个解决方案，以查找具有有效电子邮件的用户。

一个有效的电子邮件具有前缀名称和域，其中：

前缀名称是一个字符串，可以包含字母（大写或小写），数字，下划线 '_' ，点 '.' 和/或破折号 '-' 。前缀名称必须以字母开头。
域为 '@leetcode.com' 。

以任何顺序返回结果表。

^：这个符号表示字符串的开始。
[a-zA-Z]：这个部分匹配任何单个字母，无论大小写。
[a-zA-Z0-9_.-]：这个部分匹配任何数量（包括零个）的字母，数字，下划线，点或破折号。星号表示前面的字符可以出现任意次数。
\@leetcode\.com：这个部分匹配"@leetcode.com"字符串。在这里，\用于转义特殊字符@和.，使它们被视为普通字符而不是正则表达式的特殊符号。
$：这个符号表示字符串的结束。

SELECT
    *
FROM
    Users
WHERE
    mail
REGEXP
    '^[A-Za-z][A-Za-z0-9_.-]*@leetcode[.]com$';

#@leetcode[.]com:匹配@leetcode.com

47.餐馆营业额变化增长

你是餐馆的老板，现在你想分析一下可能的营业额变化增长（每天至少有一位顾客）。

计算以 7 天（某日期 + 该日期前的 6 天）为一个时间段的顾客消费平均值。average_amount 要 保留两位小数。

结果按 visited_on 升序排序。

SELECT
	a.visited_on,sum(b.amount) as amount,round(sum(b.amount)/7,2) as average_amount
FROM
	( SELECT DISTINCT visited_on FROM customer ) a JOIN customer b 
 	ON datediff( a.visited_on, b.visited_on ) BETWEEN 0 AND 6 
WHERE
	a.visited_on >= (SELECT min(visited_on) FROM customer) + 6 
    group by a.visited_on
order by a.visited_on

首先确定输出的时间用a表框定范围，然后再连接b表，连接的条件是时间在7天之内（使用datediff函数），然后按照a表的时间进行分类使用聚合函数就可以了

48.好友申请 II ：谁有最多的好友

编写解决方案，找出拥有最多的好友的人和他拥有的好友数目。

生成的测试用例保证拥有最多好友数目的只有 1 个人。

   
   select c.id,sum(c.num) as num from
    (select id,num
    from
        (select requester_id as id,count(requester_id) num
        from RequestAccepted
        group by requester_id)a

    UNION all

    select id,num 
    from
        (select accepter_id as id,count(accepter_id) num
        from RequestAccepted
        group by accepter_id)b
    )c
group by c.id
order by num desc limit 1

49.2016年的投资

编写解决方案报告 2016 年 (tiv_2016) 所有满足下述条件的投保人的投保金额之和：

他在 2015 年的投保额 (tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
他所在的城市必须与其他投保人都不同（也就是说 (lat, lon) 不能跟其他任何一个投保人完全相同）。

tiv_2016 四舍五入的 两位小数 。

SELECT round(sum(TIV_2016), 2) 'tiv_2016'
FROM insurance i1
WHERE TIV_2015 in (
    SELECT TIV_2015 FROM insurance i2
    WHERE i1.PID != i2.PID
)
AND (LAT, LON) NOT IN (
    SELECT LAT, LON FROM insurance i2
    WHERE i1.PID != i2.PID
);

从insurance表中找出所有符合条件的tiv_2016对其求和保留两位，

第一个条件 tiv_2015需要重复出现

第二个条件坐标不能完全相同

50.部门工资前三高的所有员工

公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的不同工资中 排名前三 。

编写解决方案，找出每个部门中 收入高的员工 。

以 任意顺序 返回结果表。

返回结果格式如下所示。

在IT部门:
- Max的工资最高
- 兰迪和乔都赚取第二高的独特的薪水
- 威尔的薪水是第三高的

在销售部:
- 亨利的工资最高
- 山姆的薪水第二高
- 没有第三高的工资，因为只有两名员工

SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
    Employee e1
        JOIN
    Department d ON e1.DepartmentId = d.Id
WHERE
    3 > (SELECT
            COUNT(DISTINCT e2.Salary)
        FROM
            Employee e2
        WHERE
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        )

Majoryx

关注

47
点赞
踩
31

收藏

觉得还不错? 一键收藏
0
评论
力扣50题后半部分

一开始这样写是不对的，因为min（）max（）是聚合函数不能直接用，需要有group by，那就可以引入。编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。ifnull（（....如果不是null 则输出这里的数值），null）天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）。编写解决方案以获取即时订单在所有用户的首次订单中的比例。编写解决方案，对于每一个用户，返回该用户的关注者数量。评分小于 3 的查询结果占全部查询结果的百分比。
复制链接

扫一扫