# 力扣MySQL题解

# 目录

目录
力扣-SQL入门
力扣-SQL 基础plus会员-11天

[TOC]

# 力扣-SQL入门

# 1.选择

# 595. 大的国家 (opens new window)

# Write your MySQL query statement below
select name , population, area
from World
where area>=3000000 or population>=25000000

1
2
3
4

# 1757. 可回收且低脂的产品 (opens new window)

# Write your MySQL query statement below
select product_id 
from Products
where 'Y'=recyclable and 'Y'=low_fats

1
2
3
4

# 584. 寻找用户推荐人 (opens new window)

易错，本题，必须判断referee_id is null

# Write your MySQL query statement below
select name 
from customer
where ( not referee_id=2 ) or  referee_id is null

1
2
3
4

# 183. 从不订购的客户 (opens new window)⭐️

not in的使用

# Write your MySQL query statement below
select name as Customers
from Customers
where Id not in
( 
    select CustomerId
    from Orders
)

1
2
3
4
5
6
7
8

更加明确的写法如下⭐️⭐️

select Customers.name as Customers
from Customers
where Customers.id not in
(
    select Customerid from Orders
)

1
2
3
4
5
6
7

# 2.排序 & 修改

# 1873. 计算特殊奖金 (opens new window)⭐️

学习case end的使用

# Write your MySQL query statement below
select employee_id , 
case
  when ( left(name,1)!='M' and employee_id % 2=1 ) then salary
  else 0
end 
as bonus
from Employees
order by employee_id

1
2
3
4
5
6
7
8
9

# 627. 变更性别 (opens new window)⭐️

if语句

# Write your MySQL query statement below
update Salary
set sex=
    if( 'f'=sex ,
    'm','f')

1
2
3
4
5

case解法

# Write your MySQL query statement below
update Salary
set sex=
    case sex
        when 'f' then 'm'
        when 'm' then 'f'
    end

1
2
3
4
5
6
7

# 196. 删除重复的电子邮箱 (opens new window)⭐️

自连接！学习⭐️⭐️

# Please write a DELETE statement and DO NOT write a SELECT statement.
# Write your MySQL query statement below
delete p1 
	from Person p1 ,Person p2
where p1.Email =p2.Email and p1.Id > p2.Id

1
2
3
4
5

# 3.字符串处理函数/正则

# 1667. 修复表中的名字 (opens new window)

# Write your MySQL query statement below
SELECT user_id, 
    CONCAT(UPPER(SUBSTRING(name, 1, 1)),
         LOWER(SUBSTRING(name, 2, (SELECT LENGTH(name))))) name
FROM Users
ORDER BY user_id

1
2
3
4
5
6
7

# 1484. 按日期分组销售产品 (opens new window)

记忆，不会的GROUP_CONCAT（）函数

select 
    sell_date, 
    # 获取“不同的”产品数【count(distinct product)】
    count(distinct product) as num_sold, 
    # “不同的”【distinct product】产品按照字典排序【order by product】  & “,”分割【separator ','】
    group_concat(distinct product order by product separator ',') as products
from Activities
group by sell_date
order by sell_date;

1
2
3
4
5
6
7
8
9

# 1527. 患某种疾病的患者 (opens new window)

# Write your MySQL query statement below
select patient_id,patient_name, conditions
from Patients
where conditions like '% DIAB1%'
    or conditions like'DIAB1%'

1
2
3
4
5

# 4.组合查询&指定选取

# 1965. 丢失信息的雇员 (opens new window)

# Write your MySQL query statement below
select employee_id from (
    select e.employee_id employee_id from Employees e left join Salaries s on e.employee_id=s.employee_id where salary is null
    union all
    select s.employee_id employee_id from Employees e right join Salaries s on e.employee_id=s.employee_id where name is null
) x order by employee_id;

1
2
3
4
5
6
7

# 1795. 每个产品在不同商店的价格 (opens new window)

# Write your MySQL query statement below
select product_id, 'store1' store, store1 price from products where store1 is not NULL
UNION
select product_id, 'store2' store, store2 price from products where store2 is not NULL
UNION
select product_id, 'store3' store, store3 price from products where store3 is not NULL

1
2
3
4
5
6
7
8

# 608. 树节点 (opens new window)

# Write your MySQL query statement below
# Write your MySQL query statement below
select id,(
    case WHEN p_id is null THEN 'Root'
        WHEN id in(select p_id from tree) THEN 'Inner'
        else 'Leaf' END)
    as type from tree 
order by id

1
2
3
4
5
6
7
8

# 176. 第二高的薪水 (opens new window)

# Write your MySQL query statement below
SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary
;

1
2
3
4
5
6
7
8
9
10

# 5.合并

多表的联结又分为以下几种类型：

1）左联结（left join），联结结果保留左表的全部数据

2）右联结（right join），联结结果保留右表的全部数据

3）内联结（inner join），取两表的公共数据

# 175. 组合两个表 (opens new window)

# Write your MySQL query statement below
select FirstName, LastName, City, State
from Person left join Address
on Person.PersonId = Address.PersonId

1
2
3
4

# 1581. 进店却未进行过交易的顾客 (opens new window)

# Write your MySQL query statement below
select a.customer_id, count(1) count_no_trans 
    from Visits a
    where a.visit_id not in (select b.visit_id from Transactions b)
    group by a.customer_id

1
2
3
4
5
6

# 1148. 文章浏览 I (opens new window)

# Write your MySQL query statement below
select distinct author_id as id
from  Views
where author_id = viewer_id
order by author_id

1
2
3
4
5
6

# 6.合并

# 197. 上升的温度 (opens new window)

# Write your MySQL query statement below
SELECT b.Id
FROM Weather as a,Weather as b
WHERE a.Temperature < b.Temperature and DATEDIFF(a.RecordDate,b.RecordDate) = -1;

1
2
3
4

# 607. 销售员 (opens new window)

select distinct name from SalesPerson where sales_id not in (
    select distinct sales_id from Orders where com_id=(
        select com_id from Company where name="RED"
    )
);

1
2
3
4
5

# 7.计算函数

# 1141. 查询近30天活跃用户数 (opens new window)

# Write your MySQL query statement below
# Write your MySQL query statement below
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;

1
2
3
4
5
6
7
8
9

# 1693. 每天的领导和合伙人 (opens new window)

# Write your MySQL query statement below
select date_id , make_name ,count(distinct lead_id) unique_leads , count(distinct partner_id) unique_partners
from dailysales
group by date_id,make_name;

1
2
3
4
5

# 1729. 求关注者的数量 (opens new window)

# Write your MySQL query statement below
select user_id, count(1) followers_count 
from Followers 
group by user_id 
order by user_id;

1
2
3
4
5

# 8.计算函数

# 511. 游戏玩法分析 I (opens new window)

# Write your MySQL query statement below
select player_id,min(event_date) as 'first_login'
from Activity
group by player_id

1
2
3
4

# 586. 订单最多的客户 (opens new window)

SELECT
    customer_number
FROM
    orders
GROUP BY customer_number
ORDER BY COUNT(*) DESC
LIMIT 1

1
2
3
4
5
6
7

# 1890. 2020年最后一次登录 (opens new window)

# Write your MySQL query statement below
# Write your MySQL query statement below
select
    user_id,max(time_stamp) as last_stamp 
from
    Logins
where
    time_stamp between '2020-01-01' and '2021-01-01'
group by 
    user_id

1
2
3
4
5
6
7
8
9
10
11

# 1741. 查找每个员工花费的总时间 (opens new window)

SELECT
    event_day day, emp_id, SUM(out_time - in_time) total_time
FROM
    Employees
GROUP BY
    event_day, emp_id;

1
2
3
4
5
6
7

# 9.控制流

# 1393. 股票的资本损益 (opens new window)

# Write your MySQL query statement below
# Write your MySQL query statement below
# 方法一 case when
# select stock_name, sum(
#     case when operation = 'buy' then -price
#     else price end
# ) as capital_gain_loss 
# from stocks 
# group by stock_name;

# 方法二 if语句
select stock_name, sum(
    if(operation = 'buy', -price, price)
) as capital_gain_loss 
from stocks 
group by stock_name;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 1407. 排名靠前的旅行者 (opens new window)

# Write your MySQL query statement below
# Write your MySQL query statement below
# 1.先查出每个有行程的用户的距离 用到group by 和sum
# 2.由于没有行程的用户也需要查找所以需用到left join(主表数据不受影响),
#   再通过IFNULL()函数对行程为null的用户赋值为0,最后对其order by排序就行了
select name, ifnull (travelled_distance,0) as travelled_distance
from Users

left join
(
    select
    user_id,sum(distance) as travelled_distance 
    from Rides 
        group by user_id
)t1
on t1.user_id= Users.id
order by travelled_distance desc, name asc

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 1158. 市场分析 I (opens new window)

# Write your MySQL query statement below
select Users.user_id as buyer_id, 
    join_date, 
    ifnull(UserBuy.cnt, 0) as orders_in_2019
from Users
left join (
    select buyer_id, count(order_id) cnt 
    from Orders
    where order_date between '2019-01-01' and '2019-12-31'
    group by buyer_id
) UserBuy
on Users.user_id = UserBuy.buyer_id

1
2
3
4
5
6
7
8
9
10
11
12
13

# 10.过滤

# 182. 查找重复的电子邮箱 (opens new window)

# Write your MySQL query statement below
select Email
from Person
group by Email
having count(Email) > 1

1
2
3
4
5

# 1050. 合作过至少三次的演员和导演 (opens new window)

# Write your MySQL query statement below
select actor_id , director_id
from ActorDirector
group by actor_id , director_id
having count(timestamp) >= 3

1
2
3
4
5

# 1587. 银行账户概要 II (opens new window)

# Write your MySQL query statement below
select    name, SUM(amount) balance
from
    Users u 
join
    Transactions t 
on
    u.account = T.account
group by
    u.account
having
    sum(amount) > 10000;

1
2
3
4
5
6
7
8
9
10
11
12

# 1084. 销售分析III (opens new window)

select product_id, product_name
from product
where product_id in (
    select
        product_id
    from sales
    group by product_id
    having max(sale_date) <= '2019-03-31' and min(sale_date) >= '2019-01-01'
)

1
2
3
4
5
6
7
8
9

# 力扣-SQL 基础plus会员-11天

# 1.数值处理函数

# 1699. 两人之间的通话次数 (opens new window)

# Write your MySQL query statement below
# Write your MySQL query statement below
SELECT 
    person1,person2, 
    count(*) call_count, 
    sum(duration) total_duration 
FROM (
SELECT 
    IF(from_id>to_id, to_id, from_id) person1, 
    IF(from_id>to_id,from_id,to_id) person2, 
    duration  
FROM calls 
) c 
GROUP BY 
    person1, person2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 1251. 平均售价 (opens new window)

SELECT u.product_id product_id,
        ROUND(SUM(u.units*p.price)/SUM(u.units),2) average_price        
FROM UnitsSold u ,Prices p
WHERE u.product_id=p.product_id 
        AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY u.product_id

1
2
3
4
5
6
7

# 1571. 仓库经理 (opens new window)

# Write your MySQL query statement below
SELECT 
    w.name WAREHOUSE_NAME,
    SUM(p.Width * p.Length * p.Height * w.units) VOLUME
FROM 
    warehouse w
LEFT JOIN 
    products p
ON 
    w.product_id = p.product_id
GROUP BY w.name;

1
2
3
4
5
6
7
8
9
10
11
12

# 1445. 苹果和桔子 (opens new window)

select    sale_date,
    sum(
        if(fruit = 'apples', sold_num, -sold_num)
    ) as diff
    from sales
group by sale_date

1
2
3
4
5
6

# 2.数值处理函数

# 1193. 每月交易 I (opens new window)

select date_format(trans_date,'%Y-%m') as month,
        country, count(*) as trans_count, 
        sum(case state when 'approved' then 1 else 0 end) as approved_count,
        sum(amount) as trans_total_amount,
        sum(case state when 'approved' then amount else 0 end) as approved_total_amount
from Transactions
group by month,country;

1
2
3
4
5
6
7

# 1633. 各赛事的用户注册率 (opens new window)

# Write your MySQL query statement below

select contest_id,
       round(count(user_id) / (select count(1) from Users) * 100, 2) as percentage
       from Register
       group by contest_id
       order by percentage desc , contest_id

1
2
3
4
5
6
7
8

# 1173. 即时食物配送 I (opens new window)

# Write your MySQL query statement below
select round (
    sum(order_date = customer_pref_delivery_date) /
    count(*) * 100,
    2
) as immediate_percentage
from Delivery

1
2
3
4
5
6
7
8

# 1211. 查询结果的质量和占比 (opens new window)

# Write your MySQL query statement below
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

1
2
3
4
5
6
7
8

# 3.连接

# 1607. 没有卖出的卖家 (opens new window)

/* write your pl/sql query statement below */
select s.seller_name from seller s where s.seller_id not in (
    select o.seller_id from orders o where o.sale_date like '2020%'
) order by seller_name

1
2
3
4
5

# 619. 只出现一次的最大数字 (opens new window)

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

1
2
3
4
5
6
7
8
9

# 1112. 每位学生的最高成绩 (opens new window)

SELECT student_id, min(course_id) AS course_id, grade
FROM enrollments
WHERE (student_id, grade) IN
(
    SELECT student_id, MAX(grade) AS grade
    FROM Enrollments
    GROUP BY student_id
)
GROUP BY student_id, grade
ORDER BY student_id

1
2
3
4
5
6
7
8
9
10
11

# 1398. 购买了产品 A 和产品 B 却没有购买产品 C 的顾客 (opens new window)

select o.customer_id, c.customer_name from orders o
left join customers c
on o.customer_id = c.customer_id
group by customer_id
having 
    SUM(if(product_name = 'A', 1, 0)) > 0 and
    SUM(if(product_name = 'B', 1, 0)) > 0 and
    SUM(if(product_name = 'C', 1, 0)) = 0

1
2
3
4
5
6
7
8

# 4.连接

# 1440. 计算布尔表达式的值 (opens new window)

select e.left_operand,e.operator,e.right_operand,
case e.operator
    when '>' then if(v1.value>v2.value,'true','false')
    when '<' then if(v1.value<v2.value,'true','false')
    else  if(v1.value=v2.value,'true','false')
end value
    
from Expressions e
left join Variables v1 on v1.name = e.left_operand 
left join Variables v2 on v2.name = e.right_operand

1
2
3
4
5
6
7
8
9
10
11

# 1264. 页面推荐 (opens new window)

select distinct page_id as recommended_page
from likes
where user_id in (
                select user1_id as user_id from friendship where user2_id = 1
                union all
                select user2_id as user_id from friendship where user1_id = 1
                ) and page_id not in (
                        select page_id from likes where user_id = 1
                        )

1
2
3
4
5
6
7
8
9

# 570. 至少有5名直接下属的经理 (opens new window)

select
name
from
employee as t1 join
(select
 managerid
 from
 employee
 group by managerid
 having count(managerid) >= 5) as t2
on t1.id = t2.managerid

1
2
3
4
5
6
7
8
9
10
11
12

# 1303. 求团队人数 (opens new window)

# write your mysql query statement below
select e.employee_id,t1.team_size from employee e inner join 
(
    select team_id, 
    count(team_id) as team_size from employee group by team_id
) t1
on t1.team_id = e.team_id

1
2
3
4
5
6
7
8

# 5.连接

# 1280. 学生们参加各科测试的次数 (opens new window)

# 自连接
with cte1 as
(
    select *
    from students,subjects
)


select 
    a.student_id,
    a.student_name,
    a.subject_name,
    ifnull(count(b.subject_name),0) as attended_exams 
from cte1 a
left join Examinations b
using(student_id,subject_name)
group by 1,2,3
order by 1,2,3

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

# 1501. 可以放心投资的国家 (opens new window)

# write your mysql query statement below
select c.name as country
	from calls, person, country c
where (caller_id = id or callee_id = id) and country_code = left(phone_number, 3)
	group by country_code
	having avg(duration) > (select avg(duration) from calls);

1
2
3
4
5
6

# 184. 部门工资最高的员工 (opens new window)

select
    department.name as 'department',
    employee.name as 'employee',
    salary
from
    employee
        join
    department on employee.departmentid = department.id
where
    (employee.departmentid , salary) in
    (   select
            departmentid, max(salary)
        from
            employee
        group by departmentid
	)
;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# 580. 统计各专业学生人数 (opens new window)

select
    dept_name, count(student_id) as student_number
from
    department
        left outer join
    student on department.dept_id = student.dept_id
group by department.dept_name
order by student_number desc , department.dept_name
;

1
2
3
4
5
6
7
8
9
10

# 6.连接

# 1294. 不同国家的天气类型 (opens new window)

# Write your MySQL query statement below
SELECT country_name AS 'country_name',
    CASE 
        WHEN AVG(w1.weather_state) <= 15 THEN 'Cold' 
        WHEN AVG(w1.weather_state) >= 25 THEN 'Hot'
        ELSE 'Warm'
    END
        AS 'weather_type'
FROM 
    Countries AS c1
    INNER JOIN Weather AS w1
    ON c1.country_id = w1.country_id
WHERE w1.day BETWEEN '2019-11-01' AND '2019-11-30'
GROUP BY c1.country_id
;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 626. 换座位 (opens new window)

select
    (case
        when mod(id, 2) != 0 and counts != id then id + 1
        when mod(id, 2) != 0 and counts = id then id
        else id - 1
    end) as id,
    student
from
    seat,
    (select
        count(*) as counts
    from
        seat) as seat_counts
order by id asc;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 1783. 大满贯数量 (opens new window)

# Write your MySQL query statement below
#计算Championships表中每个运动员id出现的次数

SELECT player_id,player_name,
      SUM(IF(player_id=Wimbledon,1,0)+IF(player_id=Fr_open,1,0)+IF(player_id=US_open,1,0)+IF(player_id=Au_open,1,0)) AS grand_slams_count
FROM Championships 
JOIN Players 
GROUP BY player_id
HAVING grand_slams_count>0

1
2
3
4
5
6
7
8
9

# 1164. 指定日期的产品价格 (opens new window)

select p1.product_id, ifnull(p2.new_price, 10) as price
from (
    select distinct product_id
    from products
) as p1 -- 所有的产品
left join (
    select product_id, new_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
    )
) as p2 -- 在 2019-08-16 之前有过修改的产品和最新的价格
on p1.product_id = p2.product_id

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 7.连接

# 603. 连续空余座位 (opens new window)

# Write your MySQL query statement below
select distinct a.seat_id
from cinema a join cinema b
  on abs(a.seat_id - b.seat_id) = 1
  and a.free = true and b.free = true
order by a.seat_id
;

1
2
3
4
5
6
7

# 1731. 每位经理的下属员工数量 (opens new window)

# Write your MySQL query statement below
select 
    e1.employee_id,
    e1.name,
    count(*) as reports_count,
    ROUND(avg(e2.age), 0) as average_age
from employees e1
join employees e2
on e2.reports_to = e1.employee_id
group by employee_id
order by employee_id

1
2
3
4
5
6
7
8
9
10
11
12

# 1747. 应该被禁止的 Leetflex 账户 (opens new window)

# Write your MySQL query statement below
select distinct l1.account_id from loginfo l1
join loginfo l2
on 
    l1.account_id = l2.account_id and 
    l1.ip_address != l2.ip_address and 
    ( l1.login >= l2.login and l1.login <= l2.logout )

1
2
3
4
5
6
7
8

# 181. 超过经理收入的员工 (opens new window)

select
     a.name as employee
from employee as a join employee as b
     on a.managerid = b.id
     and a.salary > b.salary
;

1
2
3
4
5
6

# 8.不等式连接

# 1459. 矩形面积 (opens new window)

# Write your MySQL query statement below
SELECT 
    p1.id P1,
    p2.id P2,
    ABS(p1.x_value-p2.x_value)*ABS(p1.y_value-p2.y_value) AREA
FROM points p1,points p2
WHERE p1.id<p2.id 
AND p1.x_value!=p2.x_value
AND p1.y_value!=p2.y_value
ORDER BY  AREA desc,p1.id,p2.id

1
2
3
4
5
6
7
8
9
10
11

# 180. 连续出现的数字 (opens new window)

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 l2.Num = l3.Num
;

1
2
3
4
5
6
7
8
9
10
11
12

# 1988. 找出每所学校的最低分数要求 (opens new window)

# Write your MySQL query statement below
SELECT
    school_id,
    IFNULL(min(score), -1) as score
FROM (
    SELECT
        s.school_id,
        e.score
    FROM schools s
    LEFT JOIN exam e ON s.capacity >= e.student_count
) t
GROUP BY school_id
ORDER BY school_id;

1
2
3
4
5
6
7
8
9
10
11
12
13

# 9.子查询

# 1549. 每件商品的最新订单 (opens new window)

select p.product_name, o.product_id, o.order_id, o.order_date from orders o
left join products p
on p.product_id = o.product_id
where (o.product_id, order_date) in 
(
    select product_id, max(order_date) from orders
    group by product_id
)
order by product_name asc, product_id asc, order_id asc

1
2
3
4
5
6
7
8
9
10

# 1321. 餐馆营业额变化增长 (opens new window)

select 
distinct a.visited_on,sum(b.amount) as amount,round(sum(b.amount)/7,2) as average_amount 
from Customer a
join Customer b
on datediff(a.visited_on,b.visited_on) <= 6 and datediff(a.visited_on,b.visited_on) >= 0
group by a.visited_on,a.customer_id     
having  count(distinct b.visited_on) = 7
order by visited_on

1
2
3
4
5
6
7
8

# 1045. 买下所有产品的客户 (opens new window)

select customer_id from customer
group by customer_id
having count(distinct product_key) = (select count(distinct product_key) from product)

1
2
3
4

# 10.子查询

# 1341. 电影评分 (opens new window)

select T.results FROM 
(
    SELECT U.name AS results,MR.user_id
    FROM Users U,MovieRating MR 
    WHERE U.user_id = MR.user_id
    GROUP BY MR.user_id,U.name
    ORDER BY COUNT(1) DESC,U.NAME ASC
    limit 1
) T
union all
select Q.results FROM 
(
    SELECT M.title AS results
    FROM Movies M,MovieRating MM 
    WHERE M.movie_id = MM.movie_id 
    AND MM.created_at LIKE '2020-02%'
    GROUP BY M.title
    ORDER BY AVG(MM.rating) DESC,M.title ASC
    limit 1
) Q

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

# 1867. 最大数量高于平均水平的订单 (opens new window)

select
    order_id
from (
    -- 每个订单的最大数量 > max(每个订单的平均数量)
    select
        *,
        max(quantity) > max(avg(quantity)) over() as flag
    from OrdersDetails
    group by order_id
) as base
where flag = 1

1
2
3
4
5
6
7
8
9
10
11

# 11.子查询

# 550. 游戏玩法分析 IV (opens new window)

# Write your MySQL query statement below
select round(avg(a.event_date is not null), 2) fraction
from 
    (select player_id, min(event_date) as login
    from activity
    group by player_id) p 
left join activity a 
on p.player_id=a.player_id and datediff(a.event_date, p.login)=1

1
2
3
4
5
6
7
8
9

# 262. 行程和用户 (opens new window)

select request_at 'Day',
round(count(if(status!='completed',status,null))/count(*),2) 'Cancellation Rate'
from Trips
where request_at between '2013-10-01' and '2013-10-03'
and client_id not in (select users_id from Users where banned='Yes') 
and driver_id not in (select users_id from Users where banned='Yes')
group by request_at;

1
2
3
4
5
6
7
8