LeetCode数据库题解

题目来源：LeetCode-力扣，侵删。

175-combine-two-tables

题目

175-combine-two-tables

题解

# Write your MySQL query statement below
select firstName, lastName ,city,state 
from person 
left join address
on Person.PersonId=Address.PersonId;

176-second-highest-salary

题目

176-second-highest-salary

题解

# Write your MySQL query statement below

select 
    ifnull(
        (select distinct salary 
        from employee 
        order by salary desc 
        limit 1,1) 
        , null) 
        as SecondHighestSalary 
 
;

177-nth-highest-salary

题目

177-nth-highest-salary

题解

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set n=n-1;
  RETURN (
      select ifnull(
          (select distinct salary 
          from employee 
          order by salary desc
          limit 1 offset n),null)
      as getNthHighestSalary
      
  );
END

178-rank-scores

题目

178-rank-scores

题解

# Write your MySQL query statement below
select score,dense_rank() over (order by score desc) as 'rank'
from scores
;

180-consecutive-numbers

题目

180-consecutive-numbers

题解

select distinct kk.num as  ConsecutiveNums
from(select num,count(*)
    from(
        select num,row_number() over (order by id) -row_number() over (partition by num order by id) as new
        from logs) k
group by num,new
having count(*)>=3
    ) kk

181-employees-earning-more-than-their-managers

题目

181-employees-earning-more-than-their-managers

题解

# Write your MySQL query statement below
select e1.name as Employee
from Employee as e1, Employee as e2
where e1.managerId=e2.id and e1.salary > e2.salary;

182-duplicate-emails

题目

182-duplicate-emails

题解

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

183-customers-who-never-order

题目

183-customers-who-never-order

题解

# Write your MySQL query statement below
select name as Customers
from Customers left join Orders
on Orders.CustomerId=Customers.id
where CustomerId is null;

184-department-highest-salary

题目

184-department-highest-salary

题解

select d.name Department,e.name Employee,Salary
from Department d 
left join employee e
on e.Departmentid=d.id
where (e.Salary,Departmentid) in 
    (select max(Salary) Salary,Departmentid from employee group by Departmentid)

185-department-top-three-salaries

题目

185-department-top-three-salaries

题解

select d.name as Department,k.name as Employee,k.Salary
from Department d
join (select name,
    dense_rank() over(partition by departmentId 
    order by salary desc) as num,salary,departmentId 
    from employee) k
on d.Id=k.departmentId
where num between 1 and 3;

196-delete-duplicate-emails

题目

196-delete-duplicate-emails

题解

# 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;

197-rising-temperature

题目

197-rising-temperature

题解

# Write your MySQL query statement below
select w1.id from weather w1,weather w2
where w1.Temperature>w2.Temperature and dateDiff(w1.RecordDate,w2.RecordDate) = 1;

262-trips-and-users

题目

262-trips-and-users

题解

select 
    t.request_at as day,
    round(sum(case when status like 'cancelled%' then 1 else 0 end)/count(*),2) as "Cancellation Rate"
from trips t
join users u1
on u1.users_id=t.client_id and u1.banned='no'
join users u2
on u2.users_id=t.driver_id and u2.banned='no'
where request_at between '2013-10-01' and '2013-10-03'
group by request_at;

511-game-play-analysis-i

题目

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

题目

512-game-play-analysis-ii

题解

select player_id,device_id
from (select player_id,device_id,dense_rank() over(partition by player_id order by event_date asc) dr from activity ) k 
where dr=1;

534-game-play-analysis-iii

题目

534-game-play-analysis-iii

题解

select a1.player_id,a1.event_date,
   sum(a2.games_played) games_played_so_far 
from activity a1,activity a2
where a1.player_id=a2.player_id and a1.event_date>=a2.event_date
group by player_id,event_date
order by player_id,event_date desc;

550-game-play-analysis-iv

题目

550-game-play-analysis-iv

题解

select round(count(distinct t.player_id)/(select count(distinct player_id) from activity),2) fraction
from
    (select a.player_id,
        first_value(a.event_date) over(partition by a.player_id 
        order by a.event_date) first_date,
        lead(a.event_date,1) over(partition by a.player_id order by a.event_date) next_date
    from activity a) t
where datediff(t.next_date,t.first_date)=1;
 

569-median-employee-salary

题目

569-median-employee-salary

题解

select id,company,salary
from(select id,company,salary,
        row_number() over(partition by company order by salary) as rnk_asc,
        count(*) over(partition by company) as n
    from employee) k
where rnk_asc>=n/2 and rnk_asc<=n/2+1;

570-managers-with-at-least-5-direct-reports

题目

570-managers-with-at-least-5-direct-reports

题解

select distinct e.name as name
from (select managerid,count(*) over(partition by managerid) as num from employee) k
join employee e
where k.managerid=e.id and num>=5;

571-find-median-given-frequency-of-numbers

题目

571-find-median-given-frequency-of-numbers

题解

with temp as(
    select num,frequency,
        sum(frequency) over(order by num asc) s_asc
        ,sum(frequency) over(order by num desc) s_desc
        ,sum(frequency) over() s
    from numbers
)
select round(avg(num),1) as median
from temp
where s_asc>=s/2 and s_desc>=s/2;

574-winning-candidate

题目

574-winning-candidate

题解

select c.name name
from candidate c
join vote v
on v.candidateid=c.id
group by candidateid
order by count(*) desc
limit 1

577-employee-bonus

题目

577-employee-bonus

题解

select name,bonus
from employee e 
left join bonus b 
on b.empid=e.empid
where ifnull(bonus,0)<1000

578-get-highest-answer-rate-question

题目

578-get-highest-answer-rate-question

题解

select  question_id as survey_log
from surveylog

group by question_id
order by (count(answer_id))/count(*) desc,question_id
limit 1;

579-find-cumulative-salary-of-an-employee

题目

579-find-cumulative-salary-of-an-employee

题解

select id,month,salary  
from (select id,month,
    row_number() over(partition by e.id order by month desc) rn,
    sum(salary) over(partition by id order by month range 2 preceding) salary
    from employee e) k
where rn>1
order by id,month desc

580-count-student-number-in-departments

题目

580-count-student-number-in-departments

题解

select dept_name,count(student_id) student_number
from Department d
left join student a
using(dept_id)
group by dept_id
order by student_number desc,dept_name;

584-find-customer-referee

题目

584-find-customer-referee

题解

# Write your MySQL query statement below
select name from customer
where referee_id!=2 or referee_id is null;

585-investments-in-2016

题目

585-investments-in-2016

题解

select round(sum(tiv_2016),2) tiv_2016
from (select pid,tiv_2016,
    count(*) over(partition by tiv_2015) as cnt_2015,
    count(*) over(partition by lat,lon ) as city_cnt
    from insurance)k
    where cnt_2015>1 and city_cnt=1;

586-customer-placing-the-largest-number-of-orders

题目

586-customer-placing-the-largest-number-of-orders

题解

# Write your MySQL query statement below

select customer_number from Orders
group by customer_number
order by count(order_number) desc limit 1;

595-big-countries

题目

595-big-countries

题解

# Write your MySQL query statement below

select name,population,area from world
where area>=3000000 or population>=25000000;

596-classes-more-than-5-students

题目

596-classes-more-than-5-students

题解

# Write your MySQL query statement below
select class from courses
group by class
having count(student)>=5;

597-friend-requests-i-overall-acceptance-rate

题目

597-friend-requests-i-overall-acceptance-rate

题解

select round(ifnull(count(distinct requester_id,accepter_id)/count(distinct sender_id,send_to_id),0),2) as accept_rate
from FriendRequest f,RequestAccepted r
# on f.sender_id=r.requester_id and f.send_to_id=r.accepter_id

601-human-traffic-of-stadium

题目

601-human-traffic-of-stadium

题解

with kk as 
    (select id,visit_date,people,
    id-row_number() over(order by id)as x 
    from stadium 
    where people>=100
    ) 

select id,visit_date,people
from kk
where x in 
        (select x 
        from kk 
        group by x 
        having count(x)>=3
        );

602-friend-requests-ii-who-has-the-most-friends

题目

602-friend-requests-ii-who-has-the-most-friends

题解

select id,count(*) num
from
    (select requester_id as id from RequestAccepted
    union all
    select accepter_id as id from RequestAccepted) k
group by id
order by num desc
limit 1
    

603-consecutive-available-seats

题目

603-consecutive-available-seats

题解

with k as (select seat_id,seat_id-row_number() over() as x from cinema where free=1)
select seat_id
from k
where x in (select x from k group by x having count(*)>=2)
order by seat_id
;

607-sales-person

题目

607-sales-person

题解

# Write your MySQL query statement below

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

608-tree-node

题目

608-tree-node

题解

select id
    ,case when p_id is null then 'Root'
    when id not in (select p_id from tree where p_id is not null) then 'Leaf'
    else 'Inner' end 
     type
from tree;

610-triangle-judgement

题目

610-triangle-judgement

题解

select x,y,z,case when x+y>z and x+z>y and y+z>x then 'Yes' else 'No' end as triangle
from triangle

613-shortest-distance-in-a-line

题目

613-shortest-distance-in-a-line

题解

select min(abs(p1.x-p2.x)) shortest
from point p1
join point p2
on p1.x>p2.x

618-students-report-by-geography

题目

618-students-report-by-geography

题解

select max(case when continent='America' then name end) as America,
    max(case when continent='Asia' then name end) as Asia,
    max(case when continent='Europe' then name end) as Europe
from (select *,row_number() over(partition by continent order by name) rn 
        from student)k
group by rn

619-biggest-single-number

题目

619-biggest-single-number

题解

select ifnull(max(num),null) num
from (select num from mynumbers group by num having count(num)=1)k

620-not-boring-movies

题目

620-not-boring-movies

题解

select * from cinema
where description != 'boring' 
     and id%2!=0
order by rating desc;

626-exchange-seats

题目

626-exchange-seats

题解

select case 
        when mod(id,2)=1 and id=s.max then id
        when mod(id,2)=0 then id-1
        else id+1 end as id,student
from (select count(*) max from seat) s,seat
order by id;

627-swap-salary

题目

627-swap-salary

题解

update  salary
set sex =(case when sex='m' then 'f' else 'm' end);
# if(sex='m','f','m');

1045-customers-who-bought-all-products

题目

1045-customers-who-bought-all-products

题解

# Write your MySQL query statement below
select customer_id
from product p
left join customer c 
on p.product_key=c.product_key
group by customer_id
having count(distinct c.product_key)=(select count(*) from product);

1050-actors-and-directors-who-cooperated-at-least-three-times

题目

1050-actors-and-directors-who-cooperated-at-least-three-times

题解

select  actor_id,director_id from ActorDirector
group by actor_id,director_id
having count(*)>=3;

1068-product-sales-analysis-i

题目

1068-product-sales-analysis-i

题解

select product_name,year,price
from product
 join sales
using(product_id)

1069-product-sales-analysis-ii

题目

1069-product-sales-analysis-ii

题解

select product_id,sum(quantity) total_quantity
from sales
group by product_id

1070-product-sales-analysis-iii

题目

1070-product-sales-analysis-iii

题解

# Write your MySQL query statement below
with a as(
    select product_id,min(year) as first_year
    from sales
    group by product_id
)
select a.product_id,first_year,quantity,price
from a
left join sales s 
on a.product_id=s.product_id and a.first_year=s.year

1075-project-employees-i

题目

1075-project-employees-i

题解

select distinct project_id,
    round(avg( experience_years) over(partition by project_id),2) as average_years
from project p
left join employee e 
using(employee_id)

1076-project-employees-ii

题目

1076-project-employees-ii

题解

select project_id
from project
group by project_id
having count(distinct employee_id)>=all(select count(distinct employee_id) from project group by project_id)

1077-project-employees-iii

题目

1077-project-employees-iii

题解

select project_id,employee_id
from (
    select project_id,employee_id,
        rank() over(partition by project_id order by experience_years desc) as r
    from project p
    join employee e
    using(employee_id)
    )k
where r=1

1082-sales-analysis-i

题目

1082-sales-analysis-i

题解

select seller_id
from (
    select seller_id,rank() over(order by sum(price) desc) r
    from sales
    group by seller_id
) k
where r=1

1083-sales-analysis-ii

题目

1083-sales-analysis-ii

题解

select buyer_id
from product 
left join sales
using(product_id)
group by buyer_id
having sum(product_name='s8')>0 and sum(product_name='iphone')=0

1084-sales-analysis-iii

题目

1084-sales-analysis-iii

题解

select p.product_id,product_name 
from sales s  join  product p
# on s.product_id=p.product_id
using (product_id)
group by product_id
having sum(sale_date<'2019-01-01')=0 and sum(sale_date>'2019-03-31')=0;

# having sum(sale_date between '2019-01-01' and '2019-03-31')=count(sale_date);
# #sum(sale_date between '2019-01-01' and '2019-03-31') 有则返回1，无则返回0
# #出售记录次数和在春季出售记录次数相等即可

1097-game-play-analysis-v

题目

1097-game-play-analysis-v

题解

select install_dt,
    count(temp.player_id) installs,
    round(count(a.event_date)/count(install_dt),2) Day1_retention
from (
    select min(event_date) install_dt,player_id
    from activity
    group by player_id
)temp
left join activity a 
on a.player_id=temp.player_id and datediff(event_date,install_dt)=1
group by install_dt

1098-unpopular-books

题目

1098-unpopular-books

题解

select b.book_id,name
from books b
left join orders o 
on b.book_id=o.book_id and  dispatch_date>='2018-06-23'
where available_from<'2019-05-23'
group by book_id
having ifnull(sum(quantity),0)<10

1107-new-users-daily-count

题目

1107-new-users-daily-count

题解

# Write your MySQL query statement below
select login_date,sum(user_count) user_count
from(
    (select min(activity_date) login_date,count(distinct user_id) user_count
    from traffic
    where activity='login'
    group by user_id
    having datediff('2019-06-30',login_date)<=90)
    )k
group by login_date

1112-highest-grade-for-each-student

题目

1112-highest-grade-for-each-student

题解

with t as(
    select 
    student_id,course_id,
    rank() over(partition by student_id order by grade desc,course_id) r,grade
    from enrollments
)
select student_id,course_id,grade
from t
where r=1
# limit 1
# order by student_id

1113-reported-posts

题目

1113-reported-posts

题解

select extra report_reason,count(distinct post_id) report_count
from actions
where action_date='2019-07-04' and extra is not null and action='report'
group by extra

1126-active-businesses

题目

1126-active-businesses

题解

select business_id
from (
    select *,avg(occurences) over(partition by event_type) a
    from events ) k
group by business_id
having sum(occurences>a)>1;

1132-reported-posts-ii

题目

1132-reported-posts-ii

题解

with temp as(
    select count(distinct r.post_id)/count(distinct a.post_id)*100 as percent 
    from actions a
    left join removals r
    using(post_id)
    where extra='spam'
    group by action_date
)
select round(avg(percent),2) average_daily_percent
from temp

1141-user-activity-for-the-past-30-days-i

题目

1141-user-activity-for-the-past-30-days-i

题解

select activity_date day,count(distinct user_id) active_users
from 
    Activity    
where activity_date between '2019-06-28' and '2019-07-27'
group by activity_date;
    

1142-user-activity-for-the-past-30-days-ii

题目

1142-user-activity-for-the-past-30-days-ii

题解

select ifnull(round(cnt/people,2),0) as average_sessions_per_user
from (select user_id,activity_type,
    count(distinct session_id) as cnt,
    count(distinct user_id) people
    from activity
    where datediff('2019-07-27',activity_date)<30) k

1148-article-views-i

题目

1148-article-views-i

题解

select distinct author_id as id
from Views
where author_id=viewer_id
order by id;

1149-article-views-ii

题目

1149-article-views-ii

题解

select distinct viewer_id as id 
from views
group by viewer_id,view_date
having count(distinct article_id)>=2
order by id

1158-market-analysis-i

题目

1158-market-analysis-i

题解

select u.user_id buyer_id,join_date,ifnull(count(order_id),0) as orders_in_2019
from users u
left join orders o
on u.user_id=o.buyer_id and year(order_date)=2019
group by user_id;

1173-immediate-food-delivery-i

题目

1173-immediate-food-delivery-i

题解

select round(sum(case when order_Date=customer_pref_delivery_date then 1 else 0 end)/count(*)*100,2) as immediate_percentage
from delivery

1179-reformat-department-table

题目

1179-reformat-department-table

题解

# Write your MySQL query statement below
select id,
sum(if(month='Jan',revenue,null)) as Jan_Revenue,
sum(if(month='Feb',revenue,null)) as Feb_Revenue,
sum(if(month='Mar',revenue,null)) as Mar_Revenue,
sum(if(month='Apr',revenue,null)) as Apr_Revenue,
sum(if(month='May',revenue,null)) as May_Revenue,
sum(if(month='Jun',revenue,null)) as Jun_Revenue,
sum(if(month='Jul',revenue,null)) as Jul_Revenue,
sum(if(month='Aug',revenue,null)) as Aug_Revenue,
sum(if(month='Sep',revenue,null)) as Sep_Revenue,
sum(if(month='Oct',revenue,null)) as Oct_Revenue,
sum(if(month='Nov',revenue,null)) as Nov_Revenue,
sum(if(month='Dec',revenue,null)) as Dec_Revenue
from Department
group by id;

1204-last-person-to-fit-in-the-bus

题目

1204-last-person-to-fit-in-the-bus

题解

select person_name
from (select person_name,turn,sum(weight) over(order by turn) s
    from queue) k
where s<=1000
order by turn desc
limit 1

1211-queries-quality-and-percentage

题目

1211-queries-quality-and-percentage

题解

select query_name,
    round(avg(rating/position),2) as quality,
    round(sum(case when rating<3 then 1 else 0 end)/count(*)*100,2) as poor_query_percentage
from queries
group by query_name

1241-number-of-comments-per-post

题目

1241-number-of-comments-per-post

题解

select distinct s1.sub_id as post_id,count(distinct s2.sub_id) as number_of_comments
from submissions s1
left join submissions s2
on s1.sub_id=s2.parent_id
where s1.parent_id is null
group by post_id
order by post_id

1251-average-selling-price

题目

1251-average-selling-price

题解

select p.product_id,round(sum(price*units)/sum(units),2) as average_price
from unitssold u 
join prices p 
on u.product_id=p.product_id and purchase_date between start_date and end_date
group by product_id

1270-all-people-report-to-the-given-manager

题目

1270-all-people-report-to-the-given-manager

题解

select e.employee_id
from employees e,employees e1,employees e2
where e1.employee_id=e.manager_id and e2.employee_id=e1.manager_id
and e.employee_id<>1 and e2.manager_id=1

1280-students-and-examinations

题目

1280-students-and-examinations

题解

# Write your MySQL query statement below
select st.student_id,student_name,su.subject_name,ifnull(count(e.student_id),0) as attended_exams
from subjects su
join students st
left join examinations e
# using(student_id,subject_name)
on st.student_id=e.student_id and e.subject_name=su.subject_name
group by st.student_id,su.subject_name
order by st.student_id,su.subject_name

1285-find-the-start-and-end-number-of-continuous-ranges

题目

1285-find-the-start-and-end-number-of-continuous-ranges

题解

select min(log_id) as start_id,max(log_id) as end_id
from (select log_id,log_id-row_number() over(order by log_id) as k from logs) t
group by k;

1294-weather-type-in-each-country

题目

1294-weather-type-in-each-country

题解

# Write your MySQL query statement below
select country_name,
    case when avg(weather_state)<=15 then'Cold'
        when avg(weather_state)>=25 then 'Hot'
        else 'Warm' end as weather_type
from countries
left join weather
using(country_id)
where day like '2019-11%'
group by country_name

1303-find-the-team-size

题目

1303-find-the-team-size

题解

select employee_id,count(employee_id) over(partition by team_id) as team_size
from employee

1308-running-total-for-different-genders

题目

1308-running-total-for-different-genders

题解

select gender,day,sum(score_points) over(partition by gender order by day) total
from scores
group by gender,day
order by gender,day

1322-ads-performance

题目

1322-ads-performance

题解

# Write your MySQL query statement below
select ad_id,
    ifnull(round(sum(action='clicked')/sum(action<>'ignored')*100,2),0.00) as ctr
from ads
group by ad_id
order by ctr desc,ad_id asc

1327-list-the-products-ordered-in-a-period

题目

1327-list-the-products-ordered-in-a-period

题解

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

1350-students-with-invalid-departments

题目

1350-students-with-invalid-departments

题解

select s.id,s.name
from students s 
left join departments d 
on d.id=s.department_id
where d.name is null

1355-activity-participants

题目

1355-activity-participants

题解

select activity
from (select activity,rank() over(order by count(id)desc) r1,
        rank() over(order by count(id) asc) r2
    from friends
    group by activity
    )f 
# join activities a 
# on f.activity=a.name
where r1<>1 and r2<>1

1364-number-of-trusted-contacts-of-a-customer

题目

1364-number-of-trusted-contacts-of-a-customer

题解

with t as(
    select customer_id,customer_name,
    ifnull(count(contact_name) over(partition by customer_id),0) as contacts_cnt,
    sum(case when contact_email in (select email from customers) then 1 else 0 end) over(partition by con.user_id) as trusted_contacts_cnt
    from customers cus 
    left join contacts con 
    on cus.customer_id=con.user_id
)
select invoice_id,customer_name,
    price,contacts_cnt,trusted_contacts_cnt
from invoices i 
left join t 
on t.customer_id=i.user_id
group by invoice_id,price
order by invoice_id

1378-replace-employee-id-with-the-unique-identifier

题目

1378-replace-employee-id-with-the-unique-identifier

题解

select unique_id,name
from employees e 
left join employeeuni eu 
# on eu.id=e.id
using(id)

1393-capital-gainloss

题目

1393-capital-gainloss

题解

select stock_name,
    sum(case when operation='buy' then -price
        when operation='sell' then price else null end) as capital_gain_loss
from stocks
group by stock_name;

1398-customers-who-bought-products-a-and-b-but-not-c

题目

1398-customers-who-bought-products-a-and-b-but-not-c

题解

select c.customer_id,customer_name
from customers c 
join orders o 
using(customer_id)
group by o.customer_id 
having sum(product_name='A')>0 and sum(product_name="B")>0 and sum(product_name='C')=0

1407-top-travellers

题目

1407-top-travellers

题解

# Write your MySQL query statement below

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

1421-npv-queries

题目

1421-npv-queries

题解

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

1440-evaluate-boolean-expression

题目

1440-evaluate-boolean-expression

题解

select left_operand,operator,right_operand,
    case when operator='=' and a1.value=a2.value then 'true'
    when operator='>' and a1.value>a2.value then 'true' 
    when operator='<' and a1.value<a2.value then 'true'
    else 'false' end as value
from expressions e 
left join variables a1
on a1.name=left_operand
left join variables a2
on a2.name=right_operand

1445-apples-oranges

题目

1445-apples-oranges

题解

# Write your MySQL query statement below

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

1454-active-users

题目

1454-active-users

题解

select a.id,a.name
from (select distinct id 
    from(
        select id,login_date,
        dense_rank() over(partition by id order by login_date) dr     
        from logins l
    ) t
    group by t.id,date_add(login_date,interval-dr day)
    having count(distinct login_date)>=5
) k

join accounts as a
using(id)
order by id

1468-calculate-salaries

题目

1468-calculate-salaries

题解

select company_id,employee_id,employee_name,
    case when max(salary) over(partition by company_id)<1000 then salary
        when max(salary) over(partition by company_id) between 1000 and 10000 then round(salary*(1-0.24),0)
        when max(salary) over(partition by company_id)>10000 
        then round(salary*(1-0.49),0) end as salary
from salaries

1484-group-sold-products-by-the-date

题目

1484-group-sold-products-by-the-date

题解

# Write your MySQL query statement below

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;

1495-friendly-movies-streamed-last-month

题目

1495-friendly-movies-streamed-last-month

题解

select distinct title
from content
left join tvprogram
using(content_id)
where program_date like '2020-06%'
and content_type='movies'
and kids_content='Y'

1501-countries-you-can-safely-invest-in

题目

1501-countries-you-can-safely-invest-in

题解

with temp as(
    select id,p.phone_number,country_code,c.name country,duration
    from person p
    join country c,calls
    where left(p.phone_number,3)=c.country_code and (calls.caller_id=p.id or p.id=calls.callee_id)
)

select distinct country
from (select country,avg(duration) over(partition by country) c_avg,
        avg(duration) over() all_avg
        from temp) k
    
where c_avg>all_avg;

1511-customer-order-frequency

题目

1511-customer-order-frequency

题解

with temp as(
    select p.product_id,price,quantity,customer_id,order_date
    from orders
    left join product p
    using(product_id)
    where order_date between '2020-06-01' and '2020-07-31'
    group by customer_id,month(order_date)
    having sum(price*quantity)>=100

)
select customer_id,name
from temp
join customers
using(customer_id)
group by customer_id
having count(*)=2

1527-patients-with-a-condition

题目

1527-patients-with-a-condition

题解

# Write your MySQL query statement below


select patient_id,patient_name,conditions 
from Patients
where conditions like '% DIAB1%' or conditions like 'DIAB1%' ;

1549-the-most-recent-orders-for-each-product

题目

1549-the-most-recent-orders-for-each-product

题解

with t as(
    select order_id,product_id,order_date,
        rank() over(partition by product_id order by order_date desc) as r
    from orders
)
select distinct product_name,t.product_id,order_id,order_date
from t
join products
using(product_id)
where r=1
order by product_name,product_id,order_id

1565-unique-orders-and-customers-per-month

题目

1565-unique-orders-and-customers-per-month

题解

select left(order_date,7) month,
    count(invoice) order_count,
    count(distinct customer_id) customer_count
from orders
where invoice>20
group by month

1571-warehouse-manager

题目

1571-warehouse-manager

题解

select w.name warehouse_name,sum(Width*Length*Height*units) volume
from Warehouse w
join Products p 
using(Product_id)
group by w.name

1581-customer-who-visited-but-did-not-make-any-transactions

题目

1581-customer-who-visited-but-did-not-make-any-transactions

题解

# Write your MySQL query statement below


select customer_id, 
if(transaction_id is null,count(visit_id),0) as count_no_trans 
from visits left join Transactions
using(visit_id)
where transaction_id is null
group by customer_id;

1587-bank-account-summary-ii

题目

1587-bank-account-summary-ii

题解

select NAME,sum(amount) as BALANCE
from Transactions left join users
using(account)
group by (account)
having sum(amount)>10000;

1596-the-most-frequently-ordered-products-for-each-customer

题目

1596-the-most-frequently-ordered-products-for-each-customer

题解

with ot as(
        select product_id,customer_id,
        rank() over(partition by customer_id order by count(product_id)desc)r
        from orders
        group by customer_id,product_id
)
select ot.customer_id,ot.product_id,p.product_name
from ot
left join products p
on p.product_id=ot.product_id
where r=1

1607-sellers-with-no-sales

题目

1607-sellers-with-no-sales

题解

select seller_name
from seller s
left join orders o 
on s.seller_id=o.seller_id and year(sale_date)='2020'
group by s.seller_id
having count(order_id)=0
order by seller_name

1613-find-the-missing-ids

题目

1613-find-the-missing-ids

题解

with recursive t as(
    select 1 as n
    union all
    select n+1 from t where n<100
)
select n ids
from t
where n not in(select customer_id from customers)
and n<=(select max(customer_id) from customers)

1623-all-valid-triplets-that-can-represent-a-country

题目

1623-all-valid-triplets-that-can-represent-a-country

题解

select s1.student_name as member_A,
    s2.student_name as member_B,
    s3.student_name as member_C
from SchoolA s1,
    SchoolB s2,
    SchoolC s3
where s1.student_name!=s2.student_name 
    and s1.student_name!=s3.student_name 
    and s2.student_name!=s3.student_name
    and s1.student_id!=s2.student_id 
    and s1.student_id!=s3.student_id 
    and s2.student_id!=s3.student_id

1633-percentage-of-users-attended-a-contest

题目

1633-percentage-of-users-attended-a-contest

题解

select contest_id,
    round(count(user_id)/(select count(distinct user_id) from users)*100,2) as percentage
from register
group by contest_id
order by percentage desc,contest_id asc

1661-average-time-of-process-per-machine

题目

1661-average-time-of-process-per-machine

题解

# Write your MySQL query statement below
select machine_id,
    round(avg(if(activity_type='start',-timestamp,timestamp))*2,3) as processing_time
from activity
group by machine_id

1667-fix-names-in-a-table

题目

1667-fix-names-in-a-table

题解

select user_id, concat(
                        upper(left(name,1)),
                        lower(right(name,length(name)-1))
                        )  as name
from users
order by user_id;

1683-invalid-tweets

题目

1683-invalid-tweets

题解

# Write your MySQL query statement below
select tweet_id
from tweets
where length(content)>15

1693-daily-leads-and-partners

题目

1693-daily-leads-and-partners

题解

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

1699-number-of-calls-between-two-persons

题目

1699-number-of-calls-between-two-persons

题解

select case when from_id<to_id then from_id else to_id end person1,
    case when from_id>to_id then from_id else to_id end person2,
    count(*) call_count,
    sum(duration) total_duration
from calls
group by person1,person2

1709-biggest-window-between-visits

题目

1709-biggest-window-between-visits

题解

with t as (
    select *,lead(visit_date,1,'2021-01-01') over(partition by user_id order by visit_date) lead_date
    from uservisits
)
select user_id,max(datediff(lead_date,visit_date)) biggest_window
from t
group by user_id

1715-count-apples-and-oranges

题目

1715-count-apples-and-oranges

题解

select sum(b.apple_count)+ifnull(sum(c.apple_count),0) apple_count,
        sum(b.orange_count)+ifnull(sum(c.orange_count),0) orange_count
from boxes b 
left join chests c 
using(chest_id)

1729-find-followers-count

题目

1729-find-followers-count

题解

# Write your MySQL query statement below



select user_id, count(follower_id) as followers_count
from Followers
group by user_id
order by user_id ;

1731-the-number-of-employees-which-report-to-each-employee

题目

1731-the-number-of-employees-which-report-to-each-employee

题解

# Write your MySQL query statement below
select e1.reports_to as employee_id,
    e2.name,
    count(distinct e1.employee_id) as reports_count,
    round(avg(e1.age),0) as average_age
from employees e1,employees e2
where e1.reports_to=e2.employee_id and e1.reports_to is not null
group by e1.reports_to
order by e1.reports_to

1741-find-total-time-spent-by-each-employee

题目

1741-find-total-time-spent-by-each-employee

题解

# Write your MySQL query statement below

select event_day as day,emp_id,
    sum(-(in_time-out_time)) as total_time
from Employees
group by emp_id,event_day;

1757-recyclable-and-low-fat-products

题目

1757-recyclable-and-low-fat-products

题解

# Write your MySQL query statement below

select product_id from products
where low_fats='y' and recyclable='y';

1777-products-price-for-each-store

题目

1777-products-price-for-each-store

题解

# Write your MySQL query statement below
select  product_id,
    max(case when store='store1' then price else null end) as store1,
    max(case when store='store2' then price else null end) as store2,
    max(case when store='store3' then price else null end) as store3
from products 
group by product_id

1783-grand-slam-titles

题目

1783-grand-slam-titles

题解

select player_id,player_name,
    sum(wimbledon=player_id)+sum(Fr_open=player_id)+sum(US_open=player_id)+sum(Au_open=player_id) as grand_slams_count
from players,championships
group by player_id,player_name
having grand_slams_count>0

1789-primary-department-for-each-employee

题目

1789-primary-department-for-each-employee

题解

# Write your MySQL query statement below
select employee_id,department_id
from employee
group by employee_id
having count(*)=1
union
select employee_id,department_id
from employee
where primary_flag='Y'

1795-rearrange-products-table

题目

1795-rearrange-products-table

题解

# Write your MySQL query statement below


select product_id,'store1'  store,store1 as price  from Products where store1 is not null
union
select product_id,'store2'  store,store2 as price  from Products where store2 is not null
union
select product_id,'store3'  store,store3 as price  from Products where store3 is not null;

1809-ad-free-sessions

题目

1809-ad-free-sessions

题解

select session_id
from playback p 
left join ads a 
on a.customer_id=p.customer_id and timestamp between start_time and end_time
where ad_id is null

1811-find-interview-candidates

题目

1811-find-interview-candidates

题解

with ktable as(
select contest_id,gold_medal user_id from contests
union all
select contest_id,silver_medal user_id from contests
union all
select contest_id,bronze_medal user_id from contests
)

select distinct name,mail from users
left join ktable
using(user_id)
where user_id in (
            select gold_medal 
            from contests 
            group by gold_medal 
            having count(gold_medal)>=3
            union
            select t2.user_id 
            from(
                select user_id,
                    contest_id-row_number() over(partition by user_id order by contest_id asc) as tag
                from ktable )t2
            group by user_id,tag
            having count(*)>=3               
    )

1821-find-customers-with-positive-revenue-this-year

题目

1821-find-customers-with-positive-revenue-this-year

题解

select customer_id
from customers
where year='2021'
group by customer_id
having sum(revenue)>0

1831-maximum-transaction-each-day

题目

1831-maximum-transaction-each-day

题解

# Write your MySQL query statement below
select transaction_id
from (select transaction_id,rank() over(partition by date_format(day,'%y-%m-%d') order by amount desc)rr
    from transactions )k
where rr=1
order by transaction_id

1853-convert-date-format

题目

1853-convert-date-format

题解

select date_format(day,'%W, %M %e, %Y') as day from Days

1867-orders-with-maximum-quantity-above-average

题目

1867-orders-with-maximum-quantity-above-average

题解

select distinct order_id
from ordersdetails
group by order_id
having max(quantity)>(select avg(quantity) a from ordersdetails group by order_id order by a desc limit 1)

1873-calculate-special-bonus

题目

1873-calculate-special-bonus

题解

select employee_id,
    if (employee_id%2=1 and name not like 'M%',salary,0 ) as bonus
from Employees
order by employee_id;

1890-the-latest-login-in-2020

题目

1890-the-latest-login-in-2020

题解

select user_id,max(time_stamp) as last_stamp 
from Logins
where year(time_stamp)=2020 
group by user_id;

1934-confirmation-rate

题目

1934-confirmation-rate

题解

select user_id,round(ifnull(sum(action='confirmed')/count(*),0),2) confirmation_rate
from signups s
left join confirmations c 
using(user_id)
group by user_id

1939-users-that-actively-request-confirmation-messages

题目

1939-users-that-actively-request-confirmation-messages

题解

select distinct user_id
from (select 
        user_id,
        count(*) over(partition by user_id  order by time_stamp range between interval 24 hour preceding and current row) as cnt 
    from confirmations) k
where cnt>=2

1965-employees-with-missing-information

题目

1965-employees-with-missing-information

题解

select Employees.employee_id 
from Employees left join Salaries
using(employee_id)
where salary is null
union 
select Salaries.employee_id 
from Salaries left join Employees  
using(employee_id)
where name is null 

order by employee_id;

1978-employees-whose-manager-left-the-company

题目

1978-employees-whose-manager-left-the-company

题解

select employee_id
from employees
where salary<30000 and manager_id not in (select employee_id from employees)
order by employee_id

2020-number-of-accounts-that-did-not-stream

题目

2020-number-of-accounts-that-did-not-stream

题解

select count(*) accounts_count
from subscriptions a 
join streams b 
using(account_id)
where start_date<='2021-12-31' and end_date>='2021-01-01'
and account_id  in (select account_id from streams where year(stream_date )!=2021)

2026-low-quality-problems

题目

2026-low-quality-problems

题解

select problem_id
from problems

where (likes/(likes+dislikes))<0.6
order by problem_id

2041-accepted-candidates-from-the-interviews

题目

2041-accepted-candidates-from-the-interviews

题解

# Write your MySQL query statement below
select candidate_id
from candidates
left join rounds
using(interview_id)
where years_of_exp>=2
group by interview_id
having sum(score)>15

2051-the-category-of-each-member-in-the-store

题目

2051-the-category-of-each-member-in-the-store

题解

with a as(
    select member_id,count(visit_id) over(partition by member_id) as visit_cnt,sum(charged_amount is not null) over(partition by member_id) purchase_cnt
    from visits
    left join purchases
    using(visit_id)
)
select member_id,name,
    case when 100*purchase_cnt/visit_cnt>=80 then 'Diamond'
        when 100*purchase_cnt/visit_cnt>=50 and 100*purchase_cnt/visit_cnt<80 then 'Gold'
        when 100*purchase_cnt/visit_cnt<50 then 'Silver'
        else 'Bronze' end as category
from members m
left join a  using(member_id)
group by member_id

2066-account-balance

题目

2066-account-balance

题解

# Write your MySQL query statement below
select account_id,day,sum(case when type='deposit' then amount else -amount end) over(partition by account_id order by day) balance
from transactions

2072-the-winner-university

题目

2072-the-winner-university

题解

with k as(
    select sum(n.score>=90) as ng,sum(c.score>=90) as cg
    from newyork n,california c
)
select  case when ng>cg then 'New York University'
        when ng<cg then 'California University'
        else 'No Winner' end as winner
    from k

2082-the-number-of-rich-customers

题目

2082-the-number-of-rich-customers

题解

select count(distinct customer_id) rich_count
from store
where amount>500

2084-drop-type-1-orders-for-customers-with-type-0-orders

题目

2084-drop-type-1-orders-for-customers-with-type-0-orders

题解

select * from orders where order_type=0
union all
select * 
from orders 
where customer_id not in(select customer_id 
                from orders where order_type=0
                )

2112-the-airport-with-the-most-traffic

题目

2112-the-airport-with-the-most-traffic

题解

with t as(
    select departure_airport airport_id,flights_count
    from flights
    union all
    select arrival_airport airport_id,flights_count
    from flights
)
select airport_id
from (select airport_id,rank() over(order by sum(flights_count) desc) r
    from t
    group by airport_id
    ) tt
where r=1

2205-the-number-of-users-that-are-eligible-for-discount

题目

2205-the-number-of-users-that-are-eligible-for-discount

题解

CREATE FUNCTION getUserIDs(startDate DATE, endDate DATE, minAmount INT) RETURNS INT
BEGIN
  RETURN (
      select count(distinct user_id)
      from purchases
      where (time_stamp between startdate and enddate)
      and amount>=minAmount
  );
END

2230-the-users-that-are-eligible-for-discount

题目

2230-the-users-that-are-eligible-for-discount

题解

CREATE PROCEDURE getUserIDs(startDate DATE, endDate DATE, minAmount INT)
BEGIN
	select distinct user_id
    from purchases
    where (time_stamp between startDate and endDate)
    and amount>=minAmount
    order by user_id;
	
END

2238-number-of-times-a-driver-was-a-passenger

题目

2238-number-of-times-a-driver-was-a-passenger

题解

select distinct r2.driver_id,ifnull(cnt,0) cnt
from rides r1
left join (select count(passenger_id) over(partition by passenger_id) cnt,passenger_id from rides)k
using(passenger_id)
right join rides r2
on r2.driver_id=r1.passenger_id

2292-products-with-three-or-more-orders-in-two-consecutive-years

题目

2292-products-with-three-or-more-orders-in-two-consecutive-years

题解

with temp as
(select product_id,year(purchase_date) yyy 
from orders 
group by product_id,year(purchase_date)
having count(distinct order_id)>=3
)
select distinct p1.product_id
from temp p1,temp p2
where abs(p1.yyy-p2.yyy)=1 and p1.product_id=p2.product_id

2298-tasks-count-in-the-weekend

题目

2298-tasks-count-in-the-weekend

题解

with a as(
    select *,dayofweek(submit_date) d
    from tasks
)
select sum(d in (1,7)) as weekend_cnt,
    sum(d between 2 and 6) as working_cnt
from a

2308-arrange-table-by-gender

题目

2308-arrange-table-by-gender

题解

# Write your MySQL query statement below
with a as(
    select *,
        row_number() over(partition by gender order by user_id) rn 
    from genders
)
select user_id,gender
from (select user_id,gender,
        case when gender='female' then rn
            when gender='other' then rn+0.5
            else rn+0.6 end as r
    from a)aa
order by r,user_id

2314-the-first-day-of-the-maximum-recorded-degree-in-each-city

题目

2314-the-first-day-of-the-maximum-recorded-degree-in-each-city

题解

select city_id,min(day) day,degree
from(select *,
        rank() over(partition by city_id order by degree desc) r
    from weather
    )k
where r=1 
group by city_id

2324-product-sales-analysis-iv

题目

2324-product-sales-analysis-iv

题解

with k as(
    select user_id,
    product_id,
    rank() over(partition by user_id order by sum(price*quantity) desc) r 
    from sales s 
    left join product p 
    using(product_id)
    group by s.user_id,s.product_id
)

select user_id,product_id
from k
where r=1

2329-product-sales-analysis-v

题目

2329-product-sales-analysis-v

题解

select user_id,sum(price*quantity) as spending
from sales
left join product
using(product_id)
group by user_id
order by spending desc,user_id

2339-all-the-matches-of-the-league

题目

2339-all-the-matches-of-the-league

题解

select t1.team_name as home_team,t2.team_name as away_team
from teams t1,teams t2
where t1.team_name<>t2.team_name

2346-compute-the-rank-as-a-percentage

题目

2346-compute-the-rank-as-a-percentage

题解

with a as(
    select student_id,department_id,
        rank() over(partition by department_id order by mark desc) r,
        count(student_id) over(partition by department_id) cnt
    from students
)
select student_id,department_id,round(ifnull((r-1)*100/(cnt-1),0),2) percentage
from a 

2356-number-of-unique-subjects-taught-by-each-teacher

题目

2356-number-of-unique-subjects-taught-by-each-teacher

题解

select teacher_id,count(distinct subject_id) as cnt 
from teacher
group by teacher_id

2372-calculate-the-influence-of-each-salesperson

题目

2372-calculate-the-influence-of-each-salesperson

题解

# Write your MySQL query statement below
select distinct sp.salesperson_id,name,ifnull(sum(price) over(partition by sp.salesperson_id),0) total
from salesperson sp 
left join customer c
on sp.salesperson_id=c.salesperson_id
left join sales s 
on c.customer_id=s.customer_id

2377-sort-the-olympic-table

题目

2377-sort-the-olympic-table

题解

# Write your MySQL query statement below
select *
from olympic
group by country
order by max(gold_medals)desc,max(silver_medals)desc,max(bronze_medals)desc,country

2388-change-null-values-in-a-table-to-the-previous-value

题目

2388-change-null-values-in-a-table-to-the-previous-value

题解

select id,@temp:=ifnull(drink,@temp) drink
from coffeeshop
;

2394-employees-with-deductions

题目

2394-employees-with-deductions

题解

with a as(
    select employee_id,sum(ceiling(timestampdiff(second,in_time,out_time)/60))/60 s
    from logs
    group by employee_id
)
select employee_id
from employees
left join a 
using(employee_id)
where s<needed_hours or s is null

2504-concatenate-the-name-and-the-profession

题目

2504-concatenate-the-name-and-the-profession

题解

# Write your MySQL query statement below
select person_id,concat(name,'(',left(profession,1),')') as name
from person
order by person_id desc