leetcode database 刷题记录 easy篇

题号标黄：当时不会写的

10.13

题号	记录	备注
511. Game Play Analysis I		select player_id,min(event_date) as 'first_login' from Activity group by player_id
512. Game Play Analysis II
577. Employee Bonus		where ifnull(bonus,0)<1000
584. Find Customer Referee
586. Customer Placing the Largest Number of Orders		# 子查询匹配 select customer_number  from Orders group by customer_number having count(*) >=  all(select count(order_number)  # 这里主要如果orders是一个复杂的table，建议用cte封装一下     from orders     group by customer_number) # order by count select customer_number from Orders group by customer_number  order by count(order_number) desc  # 这里只适用于本题，不能出来多个第一 limit 1 # rnk select customer_number from (select      customer_number,      dense_rank() over(order by count(order_number) desc) as ranking      # 窗口函数适用范围广，在数据层面先构造再筛选 from orders group by customer_number) t where ranking = 1

10.15

597. Friend Requests I: Overall Acceptance Rate		ifnull,case when
603. 连续空余座位		不会
607. Sales Person
610. Triangle Judgement		case when
613. Shortest Distance in a Line
619. Biggest Single Number
1050. Actors and Directors Who Cooperated At Least Three Times		晕乎乎
1068. Product Sales Analysis I
1069. Product Sales Analysis II		select      distinct product_id,     sum(quantity) over(partition by product_id) total_product from sales;

1107

1075. Project Employees I

1.27 共5题

题号	记录	备注
1142 User Activity for the Past 30 Days II		1. ifnull(expression1,e2,e3....)  即   若e1 null, 返回e2         若e2 null, 返回e3 coalesce 和此用法一致 2. datediff('日期','日期')<30 3. 日期 between__ and__ 4. dateadd(xxx,数字，日期) xxx为‘day’/‘week’/‘year’ 等等
1148 Article Views I
1173 Immediate Food Delivery I		round(xxx,2) xxx保留2位小数
1179 Reformat Department Table		max，sum都可，目的是为了aggregation
1211 Queries Quality and Percentage

 1.29 共7题

1241 Number of Comments per Post		学会用join！！
1251 Average Selling Price		日期between and 也是左闭右闭
1280 Students and Examinations		cross join 笛卡尔积
1294 Weather Type in Each Country		case when xx then xx else xxx end as xxx  记住else后面不接表达式 一定包含end
1303 Find the Team Size		当时有思路是要用self join，但是还是不会写 法1:对于每一个employee，用另一个表数有多少和他相同的team 法2：搞清楚left self join结果是什么，是在原有基础上，只on team，得出所有匹配的结果，这样对于每一个原表的employee，就有每一种匹配情况，多少行就是team多少人。 所以group by就可以解决
1322 Ads Performance		直接用sum(xx='xxxx')就可以充当count的作用
1327 List the Products Ordered in a Period		1. ！！！aggregation 不能用where，应该用having，且顺序在group by后，order by 前 2. 筛选2月日期可以用between and 也可以用where order_date like '2020-02%'

 1.30 共5 题

1350 Students With Invalid Departments
1378 Replace Employee ID With The Unique Identifier
1407 Top Travellers
1421 NPV Queries
1435 Create a Session Bar Chart		开始思路没有join，发现不会生成[10-15>这一行，因为count数量为0. 因此为了创建完整表格，需要先建立frame，即 用union创建行名称，left join具体的count数

 1.31 共6题

1484 Group Sold Products By The Date		1. 记得count不同的要加distinct 2. 新语法 group_concat(distinct xxx order by xxx) 用来连接分别每个组的内容
1495 Friendly Movies Streamed Last Month		1. between xxx and xxx 日期要记得加‘  ’ 2. select 要distinct
1511 Customer Order Frequency		1. 多个表join可以直接 from xx join xx on xx join xx on xx 或者！ from xx,xx,xx on xxxx and xxxx 2. aggregation的group by是having！！！ 3. 要记得where groupby只之类的顺序 4. 6，7月都>100可以分别算6月和7月，用and🔗
1517 Find Users With Valid E-Mails		学习新知识点 regexp为正则表达 ^为开头 $为结尾 \\转译，即后面符号本身 {1}出现1次, 控制第一个字符为字母 *出现无数次 [a-z] 就是abcdefg....都可以出现
1527 Patients With a Condition		一定是每个单词的开头，所以 空格diab1或者直接biab1
1543 Fix Product Name Format		不知道为什么出来结果会有空格导致不成功 所以用function  trim(both/leading/trailling 'xxx' from xxx)

 2.1.明天刷几道medium的题目试试看

1565 Unique Orders and Customers Per Month
1571 Warehouse Manager
1581 Customer Who Visited but Did Not Make Any Transactions		where group by having order by

2/8/2022 共8题

1587 Bank Account Summary II
1607 Sellers With No Sales
1623 All Valid Triplets That Can Represent a Country
1633 Percentage of Users Attended a Contest
1661Average Time of Process per Machine
1667 Fix Names in a Table
1677 Product's Worth Over Invoices		要用ifnull
1683 Invalid Tweets

2/9/2022 现在做 觉得太简单了 共9题

1693 Daily Leads and Partners
1729 Find Followers Count
1890 The Latest Login in 2020
1939 Users That Actively Request Confirmation Messages
1965. Employees With Missing Information
1978 Employees Whose Manager Left the Company
2026 Low-Quality Problems
2072 The Winner University
2082 The Number of Rich Customers