leetcode数据
文章平均质量分 66
leetcode数据
小应的每天进步史
任何的被逼无奈,都只不过是不够强大
展开
-
1303. 求团队人数
select employee_id , count(employee_id) over (partition by team_id) as team_size from employee员工表:Employee+---------------+---------+| Column Name | Type |+---------------+---------+| employee_id | int || team_id | in...原创 2021-12-02 01:20:17 · 139 阅读 · 0 评论 -
1294. 不同国家的天气类型
select A.country_name,(case when avg(b.weather_state)<=15 then"Cold"when avg(b.weather_state)>=25 then"Hot"else "Warm" end) as weather_typefrom countries as A , weather as B where a.country_id = b.country_id and b.day between '2019-11-01' and.原创 2021-12-02 01:05:46 · 117 阅读 · 0 评论 -
1280. 学生们参加各科测试的次数
# Write your MySQL query statement belowselect A.student_id AS 'student_id' ,A.student_name AS 'student_name',B.subject_name AS 'subject_name', count(C.subject_name) as 'attended_exams' from Students as A join Subjects as B left outer join Examinati.原创 2021-12-02 00:42:23 · 100 阅读 · 0 评论 -
1251. 平均售价
# Write your MySQL query statement belowselect product_id, round(sum(sales)/sum(units),2) as average_pricefrom(select A.product_id , A.price * B.units as sales ,B.unitsfrom Prices as A join UnitsSold as B on A.product_id =B.product_id where B.purc.原创 2021-12-02 00:01:59 · 130 阅读 · 0 评论 -
1211. 查询结果的质量和占比
# Write your MySQL query statement belowselect query_name, round(AVG (rating/position), 2) as quality, round(sum(if (rating< 3,1 , 0))*100/ count(*), 2) as poor_query_percentagefrom Queriesgroup by query_name查询表 Queries:+------...原创 2021-12-02 00:00:44 · 131 阅读 · 0 评论 -
1179. 重新格式化部门表
select id, sum(case month when 'Jan' then revenue end) as Jan_Revenue, sum(case month when 'Feb' then revenue end) as Feb_Revenue, sum(case month when 'Mar' then revenue end) as Mar_Revenue, sum(case month when 'Apr' then revenue end) as A.原创 2021-12-01 15:39:38 · 85 阅读 · 0 评论 -
1173. 即时食物配送 I
# Write your MySQL query statement belowselect round((select count(*) from Delivery where order_date=customer_pref_delivery_date) / (select count(*) from Delivery )*100,2)as immediate_percentage配送表: Delivery+-----------------------------+---.原创 2021-12-01 15:32:49 · 101 阅读 · 0 评论 -
1142. 过去30天的用户活动 II
select ifnull(round(count(distinct session_id)/count(distinct user_id),2),0) as average_sessions_per_userfrom Activitywhere datediff("2019-07-27",activity_date) <30Table: Activity+---------------+---------+| Column Name | Type |+----------...原创 2021-12-01 15:31:57 · 108 阅读 · 0 评论 -
1141. 查询近30天活跃用户数
select activity_date as day, count(distinct(user_id)) as active_usersfrom Activitywhere datediff('2019-07-27',activity_date)<30group by activity_date活动记录表:Activity+---------------+---------+| Column Name | Type |+---------------+---------...原创 2021-12-01 15:31:08 · 178 阅读 · 0 评论 -
1113. 报告的记录
# Write your MySQL query statement belowselect B.buyer_idfrom Product as Ajoin Sales as B on A.product_id = B.product_idwhere A.product_name ="S8"and B.buyer_id not in( select B.buyer_id from Product as Ajoin Sales as B on A.product.原创 2021-12-01 15:28:14 · 125 阅读 · 0 评论 -
1084. 销售分析III
# Write your MySQL query statement belowselect A.product_id, A.product_namefrom Product as A join Sales as B on A.product_id =B.product_id group by product_idhaving min(sale_date) >="2019-01-01"and max(sale_date) <="2019-03-31"Table:Pr...原创 2021-12-01 02:15:23 · 60 阅读 · 0 评论 -
1083. 销售分析 II
# Write your MySQL query statement belowselect B.buyer_idfrom Product as Ajoin Sales as B on A.product_id = B.product_idwhere A.product_name ="S8"and B.buyer_id not in( select B.buyer_id from Product as Ajoin Sales as B on A.product.原创 2021-12-01 02:13:08 · 84 阅读 · 0 评论 -
1082. 销售分析 I
# Write your MySQL query statement belowselect seller_idfrom salesgroup by seller_idhaving sum(price)=(select sum(price)from Salesgroup by seller_id order by sum(price) desclimit 0,1)产品表:Product+--------------+---------+| Column Name | T..原创 2021-11-30 18:01:08 · 115 阅读 · 0 评论 -
1076. 项目员工II
# Write your MySQL query statement belowselect project_idfrom(select *, rank() over ( order by count(employee_id) desc) as rankingfrom projectgroup by project_id) as Awhere A.ranking=1Table:Project+-------------+-------..原创 2021-11-30 17:59:59 · 93 阅读 · 0 评论 -
1075. 项目员工 I
# Write your MySQL query statement belowselect A.project_id, round(avg(B.experience_years),2) as average_yearsfrom Project as A join Employee as B on A.employee_id =B.employee_id group by project_id项目表Project:+-------------+---------+| Column Na...原创 2021-11-30 17:56:24 · 84 阅读 · 0 评论 -
1069. 产品销售分析 II
# Write your MySQL query statement belowselect product_id , sum(quantity) as total_quantityfrom Salesgroup by product_id销售表:Sales+-------------+-------+| Column Name | Type |+-------------+-------+| sale_id | int || product_id | int |...原创 2021-11-30 17:54:38 · 95 阅读 · 0 评论 -
1068. 产品销售分析 I
# Write your MySQL query statement belowselect B.product_name, A.year, A.pricefrom Sales as A join Product as B on A.product_id =B.product_id销售表Sales:+-------------+-------+| Column Name | Type |+-------------+-------+| sale_id | int |...原创 2021-11-30 17:50:58 · 107 阅读 · 0 评论 -
1050. 合作过至少三次的演员和导演
# Write your MySQL query statement belowselect actor_id,director_idfrom ActorDirectorgroup by actor_id, director_idhaving count(*) >=3ActorDirector表:+-------------+---------+| Column Name | Type |+-------------+---------+| actor_id ...原创 2021-11-30 17:47:25 · 156 阅读 · 0 评论 -
619. 只出现一次的最大数字
# Write your MySQL query statement below# 正确SQL为select ifnull((select num from MyNumbersgroup by numhaving count(*) = 1order by num desclimit 0,1), null) numMyNumbers 表:+-------------+------+| Column Name | Type |+-------------+------+| nu.原创 2021-11-30 17:46:31 · 68 阅读 · 0 评论 -
613. 直线上的最近距离
# Write your MySQL query statement belowselect min(abs(A.x-B.x)) as shortestfrom point as AJOINpoint as bon A.x <> B.x表point保存了一些点在 x 轴上的坐标,这些坐标都是整数。写一个查询语句,找到这些点中最近两个点之间的距离。| x ||-----|| -1 || 0 || 2 |最近距离显然是 '1'...原创 2021-11-30 17:44:41 · 93 阅读 · 0 评论 -
610. 判断三角形
selectx,y,z,case when x+y>z and x+z>y and y+z>x then'Yes'else 'No'end as trianglefrom triangle一个小学生 Tim 的作业是判断三条线段是否能形成一个三角形。然而,这个作业非常繁重,因为有几百组线段需要判断。假设表 triangle保存了所有三条线段的长度 x、y、z ,请你帮 Tim 写一个查询语句,来判断每组 x、y、z 是否可以组成一个三角形?| x ...原创 2021-11-30 17:41:11 · 104 阅读 · 0 评论 -
607. 销售员
# Write your MySQL query statement belowSELECT s.nameFROM salesperson sWHERE s.sales_id NOT IN (SELECT o.sales_id FROM orders o LEFT JOIN company c ON o.com_id = c.com_id W.原创 2021-11-30 17:39:20 · 75 阅读 · 0 评论 -
603. 连续空余座位
# Write your MySQL query statement belowSelecselect distinct a.seat_idfrom cinema as a, cinema as bwhere abs(a.seat_id-b.seat_id)=1and a.free=1and b.free=1order by seat_id几个朋友来到电影院的售票处,准备预约连续空余座位。你能利用表cinema,帮他们写一个查询语句,获取所有空余座位,并将它们按照 seat_id...原创 2021-11-29 20:30:19 · 184 阅读 · 0 评论 -
597. 好友申请 I:总体通过率
selectround( ifnull( (select count(*) from (select distinct requester_id, accepter_id from RequestAccepted) as A) / (select count(*) from (select distinct sender_id, send_to_id from FriendRequest) as B), 0), 2) as accept_rate;在 Fac.原创 2021-11-29 20:29:27 · 86 阅读 · 0 评论 -
586. 订单最多的客户
SELECT customer_numberFROM ordersGROUP BY customer_numberORDER BY COUNT(*) DESCLIMIT 1在表orders中找到订单数最多客户对应的customer_number。数据保证订单数最多的顾客恰好只有一位。表orders 定义如下:| Column | Type ||-------------------|-----------|| orde...原创 2021-11-29 20:28:31 · 90 阅读 · 0 评论 -
584. 寻找用户推荐人
# Write your MySQL query statement belowselect namefrom customerwhere referee_id <> 2 or referee_id is null给定表customer,里面保存了所有客户信息和他们的推荐人。+------+------+-----------+| id | name | referee_id|+------+------+-----------+| 1 | Will | ...原创 2021-11-29 20:27:30 · 91 阅读 · 0 评论 -
577. 员工奖金
# Write your MySQL query statement belowselect A.name, B.bonusfrom Employee as A left joinBonus as Bon A.empId=B.empIdwhere bonus <1000 or bonus is null选出所有 bonus < 1000 的员工的 name 及其 bonus。Employee 表单+-------+--------+-----------+-------.原创 2021-11-29 20:26:34 · 93 阅读 · 0 评论 -
512. 游戏玩法分析 II
# Write your MySQL query statement belowselect player_id, device_idfrom Activitywhere (player_id, event_date)in (select player_id, min(event_date)from Activitygroup by player_id)Table:Activity+--------------+---------+| Column Name | Type ...原创 2021-11-29 20:25:21 · 113 阅读 · 0 评论 -
511. 游戏玩法分析 I
# Write your MySQL query statement belowselect player_id,min(event_date) as first_loginfrom Activitygroup by player_id活动表Activity:+--------------+---------+| Column Name | Type |+--------------+---------+| player_id | int || devi...原创 2021-11-29 20:21:23 · 83 阅读 · 0 评论