任务完成了但也真学到恶心了，难过🙃

附近日感受：

1. 觉得某人直男/直女癌/无法交流/不能理解的时候，其实对方有和他顺利交流的人，只不过不是你/没把你当他朋友/懒得考虑你/讨厌你罢了。

2. 心里上放松和行动上认真有可能兼备

下面是遇到的中等&困难题：

262. 行程和用户 （需复习）

写一段 SQL 语句查出 "2013-10-01" 至 "2013-10-03" 期间非禁止用户（乘客和司机都必须未被禁止）的取消率。取消率的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。返回结果表中的数据可以按任意顺序组织。其中取消率 Cancellation Rate 需要四舍五入保留 两位小数 。

注意乘客和司机都必须未被禁止，这里 Users.Banned = "no" 相当于处理了乘客或者司机未被禁止。

-- 错误解答
select trips.request_at as 日期, 
	count(distinct (case when Status in ("cancelled_by_driver", "cancelled_by_client") then trips.id else full end))/count(distinct trips.id) as 当日取消率
from Trips 
left join Users 
on (trips.Client_Id  = Users.Users_Id or trips.Driver_Id  = Users.Users_Id)
and Users.Banned = "no"
group by trips.request_at;

-- 正确解答：
-- 方法一需要两次join；方法二对cliend_id和driver_id都做剔除
select Request_at as `Day`, 
    round(count(distinct (case when Status in ("cancelled_by_driver", "cancelled_by_client") then t1.id else null end)) / count(distinct t1.id),2) as `Cancellation Rate`
from trips t1
inner join Users t2 on t1.Client_Id = t2.users_id
inner join users t3 on t1.driver_id = t3.users_id
and t1.Request_at between "2013-10-01" and "2013-10-03"
and t2.banned = "No" and t3.banned = "No" 
group by t1.Request_at;

534.游戏玩法分析

编写一个 SQL 查询，同时报告每组玩家和日期，以及玩家在此日期之前玩家所玩的游戏总数。

-- 累计求和问题

select player_id, event_date,
	sum(games_played) over(partition by player_id order by event_date asc) as games_played_so_far
from Activity
order by player_id asc, event_date asc;

550.游戏玩法分析

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

select round(count(distinct t2.player_id) / count(distinct t1.player_id), 2) as fraction
from (
  select player_id, min(event_date) as event_date
  from Activity
  group by player_id
) t1
left join 
(
  select distinct player_id,event_date -- 习惯先去重
  from Activity
  group by player_id,event_date
)t2
on t1.player_id = t2.player_id
and datediff(t2.event_date, t1.event_date) = 1  -- t2-t1=1;

569. 员工薪水中位数

Employee 表有三列：员工Id，公司名和薪水。请编写SQL查询来查找每个公司的薪水中位数。挑战点：你是否可以在不使用任何内置的SQL函数的情况下解决此问题。

N 奇数：n+1/2，偶数 n/2 n/2+1 ——对所有n/2到n/2+1的数字求平均即可（实际这道题不需要求avg）

select t1.company, avg(salary)
from(
  select company, salary, row_number() over (partition by company order by salary asc) as rk
	from employee
)t1
left join 
(
  select company, count(id) as num
	from employee
 	group by company
)t2
on t1.company = t2.company
where t1.rk between t2.num/2 and (t2.num/2 +1)
group by t1.company;

570.至少有5名直接下属的经理

给定 Employee 表，请编写一个SQL查询来查找至少有5名直接下属的经理。对于上表，您的SQL查询应该返回：

select `name`
from(
  select t1.id as id, t1.`name` as `name`, count(distinct t2.id) as num
  from Employee t1
  left join Employee t2
  on t1.id = t2.ManagerId
  group by t1.id,t1.`name`
) tmp
where num>=5;

571.给定数字的频率查询中位数 （需复习）

Numbers 表保存数字的值及其频率，请编写一个查询来查找所有数字的中位数并将结果命名为 median

当某一数字的 正序和逆序累计 均大于 整个序列的数字个数的一半 时即为中位数

select avg(number) as median
from (
  select number, 
    sum(frequency) over(order by number asc) as freq1,
    sum(frequency) over(order by number desc) as freq2
  from numbers
) t1,
(
  select sum(frequency)*0.5 as num
	from numbers
)  t2
where freq1>= num and freq2>= num ;

574.当选者

select Name from Candidate
where id = (
    select CandidateId from Vote
    group by CandidateId
    order by count(id) desc
    limit 1
);

578.查询回答率最高的问题

回答率：回答数占显示数的比例

select question_id as survey_log
from(
    select question_id,
        count(case when action = 'answer' then question_id else null end) / count(case when action = 'show' then question_id else null end)  as 回答率
    from survey_log
    group by question_id
    order by 回答率 desc
    limit 1
) tmp;

579.查询员工的累计薪水

请你编写 SQL 语句，对于每个员工，查询他除最近一个月（即最大月）之外，剩下每个月的近三个月的累计薪水（不足三个月也要计算）。

结果请按 Id 升序，然后按 Month 降序显示。

select id, `month`, salary
from
(
  select t1.id,
		t2.`month`, 
  	sum(case when t1.`month` > t2.`month`-3 and t1.`month` <= t2.`month` then t1.salary else 0 end) as salary,
  	row_number() over(partition by t1.id order by t2.`month` desc) as rk
  from Employee t1
  left join  Employee t2
  on t1.id = t2.id 
  group by t1.id, t2.`month`
) tmp
where rk > 1;

580. 统计专业学生人数

写一个查询语句，查询 department 表中每个专业的学生人数 （即使没有学生的专业也需列出）。查询结果按照学生人数降序排列。

select t1.dept_name,
	(case when t2.num is null then 0 else t2.num end) as student_number
from department t1
left join 
(
  select dept_id, count(student_id) as num
  from student
  group by dept_id
) t2
on t1.dept_id = t2.dept_id
order by num desc;

585. 投资金额

写一个查询语句，将 2016 年 (TIV_2016) 所有成功投资的金额加起来，保留 2 位小数。

1.他在 2015 年的投保额 (TIV_2015) 至少跟一个其他投保人在 2015 年的投保额相同。2.他所在的城市必须与其他投保人都不同（也就是说维度和经度不能跟其他任何一个投保人完全相同）。

concat(a,b) ，同时匹配经纬度更合理

select sum(TIV_2016) as TIV_2016
from insurance
where concat(lat,lon) in (
    select concat(lat,lon)
    from insurance
    group by concat(lat,lon)
    having count(pid)=1 
)
and TIV_2015 in (
    select distinct TIV_2015
    from insurance
    group by TIV_2015
    having count(pid)>1
);

601. 体育馆的人流量

编写一个 SQL 查询以找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。

select t1.*
from Stadium t1
,
(
    select id as start_id, id+count(rk)-1 as end_id
    from(
        select row_number()over(order by visit_date asc) as rk, visit_date,id
        from Stadium
        where people>=100
    ) tmp
    group by id-rk
    having count(rk)>=3
) t2
where  (t1.id>=t2.start_id and  t1.id<=t2.end_id)

602.好友申请

表 request_accepted 存储了所有好友申请通过的数据记录。写一个查询语句，求出谁拥有最多的好友和他拥有的好友数目。好友申请只会被接受一次，所以不会有 requester_id 和 accepter_id 值都相同的重复记录。

Union all 不合并重复行

select id, sum(friend) as num
from
(
    select requester_id as id, count(requester_id) as friend
    from request_accepted
    group by requester_id
    union all
    select accepter_id as id, count(accepter_id) as friend
    from request_accepted
    group by accepter_id
) tmp
group by id
order by num desc
limit 1;

608.树节点

给定一个表 tree，id 是树节点的编号， p_id 是它父节点的 id 。树中每个节点属于以下三种类型之一：叶、根、内部节点。写一个查询语句，输出所有节点的编号和节点的类型，并将结果按照节点编号排序。

select distinct t1.id,
	(case 
   	when t2.id is null and t1.p_id is not null then "Leaf"
  	when t1.p_id is null then "Root"
  else "Inner" end) as `Type`
from tree t1
left join tree t2
on t2.p_id = t1.id;

612.平面上的最近距离

表 point_2d 保存了所有点（多于 2 个点）的坐标 (x,y) ，这些点在平面上两两不重合。

写一个查询语句找到两点之间的最近距离，保留 2 位小数。

select round(sqrt(min(dist)),2) as shortest
from  
(
  select distinct (t1.x-t2.x)*(t1.x-t2.x)+(t1.y-t2.y)*(t1.y-t2.y) as dist
  from point_2d t1, point_2d t2
) tmp
where dist>0;

614.二级关注者

表 follow 会有 2 个字段： followee，follower。对每一个关注者，查询关注他的关注者的数目

select t1.follower, count(distinct t2.follower) as num
from follow t1
left join follow t2
on t1.follower = t2.followee
group by t1.follower
having count(distinct t2.follower) >0;

615.平均工资：部门与公司比较

在每一个工资发放日，部门平均工资与公司平均工资的比较结果 （高 / 低 / 相同）。

select a.pay_month, department_id,
    (case when avg_dep>avg_comp then "higher"
        when avg_dep=avg_comp then "same"
        else "lower" 
    end) as comparison
from
(
  select date_format(t1.pay_date, "%Y-%m") as pay_month, t2.department_id, avg(t1.amount) as avg_dep
  from salary t1
  inner join employee t2
  on t1.employee_id = t2.employee_id
  group by date_format(t1.pay_date,"%Y-%m"), t2.department_id
) a
,
(
  select date_format(pay_date,"%Y-%m") as pay_month, avg(amount) as avg_comp
  from salary 
  group by date_format(pay_date,"%Y-%m") 
) b
where a.pay_month = b.pay_month;

618.学生地理信息报告（需复习）

长表变宽表，实现列的透视表操作，使得每个学生按照姓名的字母顺序依次排列在对应的大洲下面。输出的标题应依次为美洲（America）、亚洲（Asia）和欧洲（Europe）:

-- 错误解答
with tmp as(
select `name`, continent,
    row_number()over(partition by continent order by `name`) as rk
from student
)
select t1.name,t2.name,t3.name
from tmp t1, tmp t2, tmp t3
where t1.rk = t2.rk and t2.rk = t3.rk
and t1.continent = "America" 
and t2.continent = "Asia" 
and t3.continent = "Europe";


-- 正确解答(max函数可以从null和非null中取出非Null)
SELECT
    MAX(IF(continent = 'America', name, NULL)) America,
    MAX(IF(continent = 'Asia', name, NULL)) Asia,
    MAX(IF(continent = 'Europe', name, NULL)) Europe
FROM 
(
  select `name`, continent,
  	row_number()over(partition by continent order by `name`) as rk
	from student
) tmp
GROUP BY rk;

626.换座位

纵列的 id 是连续递增的，想改变相邻俩学生的座位。即12交换 34交换。。。

select id,
    (case  
        when (id = max_id and id%2=1) then student
        when (id < max_id and id%2=1) then next_anme
        else last_name
    end) as student
from
(
    select id,student,
        lead(student)over(order by id) next_anme,
        lag(student)over(order by id) last_name
    from seat
) t1
,
(
    select max(id) as max_id
    from seat
) t2;

1045.买下所有产品的客户

从 Customer 表中查询购买了 Product 表中所有产品的客户的 id。

select distinct customer_id
from(
  select customer_id, count(distinct Product_key) as type_num
  from Customer
  group by customer_id
) tmp
where type_num = (
  select count(distinct Product_key)
	from Product
);

1070.产品销售分析

选出每个销售产品的 第一年 的 产品 id、年份、数量 和 价格。

select product_id, 
    year as first_year,
    quantity, 
    price
from sales
where (product_id, year) in (
  select product_id, min(year)
  from sales
  group by product_id
)

1084.销售分析

报告仅在2019-01-01至2019-03-31（含）之间出售的商品。

select product_id, product_name
from product
where product_id not in (
    select distinct product_id
    from sales
    where (sale_date>"2019-03-31" or sale_date<"2019-01-01")
)
and product_id in (
    select distinct product_id
    from sales
    where (sale_date <="2019-03-31" and sale_date>="2019-01-01")
)

1097.游戏玩法分析——首登留存率

select t1.event_date as install_dt,
    count(distinct t1.player_id) as installs, 
    round(count(distinct t2.player_id) / count(distinct t1.player_id),2) as Day1_retention
from (
  select player_id, min(event_date) as event_date
  from Activity
  group by player_id
) t1-- 第一天登录
left join Activity t2 
on t1.player_id = t2.player_id
and datediff(t2.event_date, t1.event_date)=1
group by t1.event_date

这里记得考虑使用on and而不是where

1098.小众书籍

筛选出过去一年中订单总量少于10本的书籍 。注意：不考虑 上架（available from）距今 不满一个月 的书籍。并且 假设今天是 2019-06-23 。

date_SUB("2019-06-23", INTERVAL 365 DAY) = "2018-06-23"

select t1.book_id,
    t1.name
    -- , (case when t2.订单总量 is null then 0 else t2.订单总量 end ) as 订单总量
from 
(
    select book_id, name
    from books
    where available_from <= date_sub("2019-06-23", INTERVAL 1 month)
) t1
left join
(
    select book_id, sum(quantity) as "订单总量"
    from Orders
    where dispatch_date >= date_sub("2019-06-23", INTERVAL 365 DAY)  
    and dispatch_date <= "2019-06-23"
    group by book_id
) t2
on t1.book_id = t2.book_id
where (t2.订单总量 is null or t2.订单总量<10)

一定注意 on 和 where 的区别！

1107.每日新用户统计

从今天起最多 90 天内，每个日期该日期首次登录的用户数，今天是 2019-06-30.

select first_login as login_date,
	count(distinct user_id) as user_count
from
(
  select user_id, min(activity_date) as first_login
  from traffic
  where activity = "login"
  group by user_id
) tmp
where first_login<="2019-06-30" 
and first_login >= date_sub("2019-06-30",interval 90 day)
group by first_login;

1126. 查询活跃业务

如果一个业务的某个事件类型的发生次数大于此事件类型在所有业务中的平均发生次数，并且该业务至少有两个这样的事件类型，那么该业务就可被看做是活跃业务。

select business_id
    -- , count(distinct(case when occurences > avg_occr then events.event_type else null end)) 
from events
left join
(
    select event_type, avg(occurences) as avg_occr
    from events
    group by event_type
) t1
on events.event_type = t1.event_type
group by business_id
having count(distinct(case when occurences > avg_occr then events.event_type else null end))>=2

1127. 用户购买平台（需复习）

每天 仅 使用手机端用户、仅 使用桌面端用户和 同时 使用桌面端和手机端的用户人数和总支出金额，null的时候需要记作0

-- 构造日期*platform的笛卡尔积
with table_2 as(
    select t4.spend_date, t3.platform
    from 
	(
    select 'mobile' as platform
    union
    select 'desktop' as platform
    union
    select 'both' as platform
	) t3,
	(
    select distinct spend_date
    from Spending
    order by spend_date
	) t4
)
-- left join 
select table_2.spend_date as spend_date, 
    table_2.platform as platform, 
    ifnull(sum(table_1.amount),0) as total_amount,
    count(table_1.user_id) as total_users
from table_2
left join 
(
    select t1.spend_date, t1.user_id, sum(t1.amount) as amount,
        (case when t2.num=1 then t1.platform else "both" end) as platform
    from spending t1
  	inner join (
            select spend_date, user_id, 
                count(distinct platform) as num
            from Spending
            group by spend_date, user_id
      ) t2
    on t1.spend_date = t2.spend_date
    and t1.user_id = t2.user_id
    group by t1.spend_date, t1.user_id
) table_1
on table_1.spend_date = table_2.spend_date
and  table_1.platform = table_2.platform
group by table_2.spend_date, table_2.platform

1132.报告的记录2

在被报告为垃圾帖子（spam）中，被移除的帖子的比例的每日平均值，小数点后 2 位。

select round(avg(ratio)*100,2) as average_daily_percent
from 
(
    select action_date,
        avg(case when t2.remove_date is null then 0 else 1 end) as ratio
    from
    (
      select distinct action_date, post_id
      from Actions
      where extra = "spam"
    ) t1
    left join Removals t2
    on t1.post_id = t2.post_id
    group by action_date
) t2

1149. 文章浏览

找出在同一天阅读至少两篇文章的人，结果按照 id 升序排序

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

1158. 市场分析1

用户 buyer_id |注册日期 join_date | 2019 年作为买家的订单总数orders_in_2019

select t1.user_id as buyer_id, t1.join_date, ifnull(t2.orders_num,0) as orders_in_2019
from users t1
left join
(
  select buyer_id, count(distinct order_id) as orders_num
  from orders
  where order_date between "2019-01-01" and "2019-12-31"
  group by buyer_id
) t2
on t1.user_id = t2.buyer_id

1159.市场分析2

确定每个用户按日期顺序卖出的第二件商品的品牌是否是他们最喜爱的品牌。如果一个用户卖出少于两件商品，查询的结果是 no。

select t1.user_id as seller_id,
     (case when t1.favorite_brand=t3.item_brand then "yes"
else "no" end) as 2nd_item_fav_brand
from Users t1
left join 
(
    select seller_id, item_id
    from(
        select seller_id, item_id, 
            row_number() over(partition by seller_id order by order_date asc) as rn
        from orders
    ) tmp
    where rn=2
) t2
on t1.user_id = t2.seller_id
left join Items t3
on t3.item_id = t2.item_id
order by seller_id

1164.指定日期的产品价格

Products 记录了 某产品 在某个日期 更改后 的新价格。查找在 2019-08-16 时全部产品的价格，假设所有产品在修改前的价格都是 10。

小于0816的所有日期里的最大值对应的价格，没有改过的话就是10

select t1.product_id, ifnull(t2.new_price,10) as price
from 
(
  select distinct product_id
  from Products
) t1
left join 
(
  -- 修改过价格的
  select product_id, new_price
  from(
        select product_id
          , row_number() over(partition by product_id order by change_date desc) as rn
          , new_price
      from Products
      where change_date<="2019-08-16"
  ) tmp
  where rn = 1
) t2
on t1.product_id = t2.product_id

1174.即时实物配送2

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

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

所有用户的首次订单中即时订单的比例。保留两位小数。

select round(avg(case when customer_pref_delivery_date=order_date then 1 else 0 end)*100, 2) as immediate_percentage 
from 
(
    select * ,
        row_number() over(partition by customer_id order by order_date) as rk
    from Delivery
) tmp
where rk = 1;

1193. 每月交易1

每个月和每个国家的事务数及其总金额、已批准的事务数及其总金额

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

1194. 锦标赛优胜者

每组的获胜者是在组内累积得分最高的选手。如果平局，player_id 最小 的选手获胜。

select group_id,player_id
from 
(
    select  t1.group_id, t1.player_id, 
        row_number() over(partition by t1.group_id order by ifnull(total_score,0) desc) as rn
    from Players t1
    left join
    (
        select player_id, sum(first_score)+sum(second_score) as total_score
        from(
            select first_player as player_id, sum(first_score) as first_score, 0 as second_score
            from matches 
            group by first_player
            union all
            select second_player as player_id, 0 as first_score, sum(second_score) as second_score
            from matches 
            group by second_player
        ) tmp
        group by player_id
    ) t2
    on t1.player_id = t2.player_id
) t3
where rn = 1;

1204.最后进入电梯的

电梯最大载重量为 1000。查找最后一个能进入电梯且不超过重量限制的 person_name 。

select person_name
from(
  select person_name, 
    sum(weight) over(order by turn asc) as weight_sum
  from queue
) tmp
where weight_sum <= 1000
order by weight_sum desc
limit 1;

1205. 每月交易2

以查找每个月和每个国家/地区的已批准交易的数量及其总金额、退单的数量及其总金额。

select `month`,country,
    sum(approved_count) as approved_count,
    sum(approved_amount) as approved_amount,
    sum(chargeback_count) as chargeback_count,
    sum(chargeback_amount) as chargeback_amount
from
(
  	-- 批准交易的
    select date_format(trans_date, "%Y-%m") as `month`, country,
        count(id) as  approved_count,
        sum(amount) as approved_amount,
        0 as chargeback_count,
        0 as chargeback_amount
    from Transactions
    where state = "approved"
    group by date_format(trans_date, "%Y-%m"), country
    
  	union all
    -- 退单的
  	select 
        date_format(t1.trans_date, "%Y-%m") as `month`,
        t2.country,
        0 as approved_count,
        0 as approved_amount,
        count(t2.id) as chargeback_count,
        sum(t2.amount) as chargeback_amount
    from Chargebacks t1
    inner join Transactions t2
    on t1.trans_id = t2.id
    group by date_format(t1.trans_date, "%Y-%m"), t2.country
) tmp
group by `month`,country

1212. 查询球队积分

赢一场得三分；平一场得一分；输一场不得分。
查询每个队的 team_id，team_name 和 num_points。结果根据 num_points 降序排序，如果有两队积分相同，那么这两队按 team_id 升序排序。

-- my answer
select 
    Teams.team_id,
    Teams.team_name,
    ifnull(sum(tmp.host_goals),0) + ifnull(sum(tmp.guest_goals),0) as num_points
from Teams
left join
(
  select host_team as team_id, 
    sum(case when host_goals>guest_goals then 3 
      when  host_goals=guest_goals then 1
    else 0 end) as host_goals,
    0 as guest_goals
  from matches
  group by host_team
  union all
  select guest_team as team_id, 
    0 as host_goals,
    sum(case when host_goals<guest_goals then 3 
      when  host_goals=guest_goals then 1
    else 0 end) as guest_goals
  from matches
  group by guest_team
) tmp
on Teams.team_id = tmp.team_id
group by team_id
order by num_points desc, team_id asc;

-- 写的有点复杂，参考题解
-- 不用 union 用 复杂的 or 
select t1.team_id,t1.team_name,
	sum((case when (t1.team_id = t2.host_team and host_goals>guest_goals) or (t1.team_id = t2.guest_team and host_goals<guest_goals)  then 3
   when host_goals=guest_goals then 1
   else 0
  end)) as num_points
from teams t1
left join matches t2
on (t1.team_id = t2.host_team or t1.team_id = t2.guest_team)
group by t1.team_id, t1.team_name
order by num_points desc, team_id asc

1225. 报告系统状态的连续日期（需复习）

系统 每天 运行一个任务。每个任务都独立于先前的任务。任务的状态可以是失败或是成功。

编写一个 SQL 查询 2019-01-01 到 2019-12-31 期间任务连续同状态 period_state 的起止日期（start_date 和 end_date）。即如果任务失败了，就是失败状态的起止日期，如果任务成功了，就是成功状态的起止日期。

最后结果按照起始日期 start_date 排序

with tmp as(
    select fail_date as date_day,
  -- 用datediff 而不是原本日期，以避免判定1.31和2.1不是连续的这种情况
        datediff(fail_date, "2019-01-01")+1 as date_day2,
         "failed" as period_state
    from Failed
    union 
    select success_date as date_day,
        datediff(success_date, "2019-01-01")+1 as date_day2,
        "succeeded" as period_state
    from Succeeded
) -- 先union
-- 连续按照day - row_number 计算
select period_state,
      min(date_day) as start_date,
      max(date_day) as end_date
from(
    select date_day, period_state, 
        row_number() over(partition by period_state order by date_day2 asc) as rn,
        date_day2 - row_number() over(partition by period_state order by date_day2 asc) as date_rn
    from tmp
    where date_day>="2019-01-01" and date_day<="2019-12-31"
) tmp2
group by period_state, date_rn 
order by start_date asc;

1264.页面推荐

写一段 SQL 向user_id = 1 的用户，推荐其朋友们喜欢的页面。不要推荐该用户已经喜欢的页面。你返回的结果中不应当包含重复项。

select distinct page_id as recommended_page
from likes
where user_id in (
    select distinct (case when user1_id = 1 then user2_id else user1_id end) as friend_id
    from Friendship
    where (user1_id = 1 or user2_id = 1)
)
and page_id not in (
    select distinct page_id
    from likes
    where user_id = 1
)

1270.向公司CEO汇报工作的所有人

用 SQL 查询出所有直接或间接向公司 CEO 汇报工作的职工的 employee_id 。由于公司规模较小，经理之间的间接关系不超过 3 个经理。可以以任何顺序返回的结果，不需要去重。

select distinct employee_id
from Employees
where manager_id  in (
    select distinct employee_id
    from Employees
    where manager_id  in (
        select distinct employee_id
        from Employees
        where manager_id = 1
    )
)
and employee_id != 1

1285. 连续区间的开始和结束数字

Logs 表中的连续区间的开始数字和结束数字。按照 start_id 排序。

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 asc) as log_rk
	from logs
) tmp
group by log_rk;

1308. 不同性别的每日分数统计

查询每种性别在每一天的总分，并按性别和日期对查询结果排序。

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

1321. 餐馆营业额变化增长—连续求和

写一条 SQL 查询计算以 7 天（某日期 + 该日期前的 6 天）为一个时间段的顾客消费平均值。输出按照日期排序。

连续求和：sum(amount) over(order by visited_on rows 6 preceding) 对当前和后6行连续求和

-- 普通解法
select 
    c1.visited_on, 
    sum(c2.amount) as amount,
    round(sum(c2.amount) / 7, 2) as average_amount
from 
(
    select distinct visited_on
    from Customer
    where datediff(visited_on,(select min(visited_on) from Customer))>=6
) c1,
Customer c2
where datediff(c1.visited_on, c2.visited_on)<=6
and c2.visited_on <= c1.visited_on
group by c1.visited_on
order by c1.visited_on asc


-- 窗口函数新解法
select * from
(
    select visited_on,
        sum(amount) over(order by visited_on rows 6 preceding) as amount,
        round(avg(amount) over(order by visited_on rows 6 preceding),2) as average_amount
    from(
         select visited_on, sum(amount) as amount 
         from Customer
         group by visited_on
    )t1
)t2 
where datediff(visited_on, (select min(visited_on)from Customer))>=6

1336.每次访问的交易次数

多少客户访问了银行但没有进行任何交易，多少客户访问了银行进行了一次交易等等

结果包含两列：

transactions_count： 客户在一次访问中的交易次数
visits_count： 在 transactions_count 交易次数下相应的客户数量
transactions_count 的值从 0 到所有用户一次访问中的 max(transactions_count)

select 
from visits t1
left join Transactions t2
on t1.user_id=t2.user_id
and 

1341. 电影评分

请你编写一组 SQL 查询：

查找评论电影数量最多的用户名。
如果出现平局，返回字典序较小的用户名。

查找在 2020 年 2 月 平均评分最高 的电影名称。
如果出现平局，返回字典序较小的电影名称。

查询分两行返回，查询结果格式如下例所示：

select `name` as results
from
(
    select `name`,
        count(1) as num
    from Movie_Rating
    left join Users on Movie_Rating.user_id = Users.user_id
    group by `name`
    order by num desc, `name` asc
    limit 1
) t1
union all
select title as results
from 
(
    select title,
        avg(case when date_format(created_at, "%Y-%m")="2020-02" then rating else null end ) as rating
    from Movie_Rating
    inner join Movies on Movie_Rating.movie_id = Movies.movie_id
    group by title
    order by rating desc, title asc
    limit 1
) t2

1355.活动参与者

写一条 SQL 查询那些既没有最多，也没有最少参与者的活动的名字。注意名称相同 id 不同的参与者算作两个人。

select activity
from
(
    select activity, num,
    rank() over(order by num asc ) as rk1,
    rank() over(order by num desc ) as rk2
    from (
        select activity, count(distinct id) as num
        from Friends
        group by activity
    ) t1
) t2
where rk1>1 and rk2>1

1364.顾客的可信联系人数

为每张发票 invoice_id 编写一个SQL查询以查找以下内容：

customer_name：与发票相关的顾客名称。
price：发票的价格。
contacts_cnt：该顾客的联系人数量。
trusted_contacts_cnt：可信联系人的数量：既是该顾客的联系人又是商店顾客的联系人数量（即：可信联系人的电子邮件存在于客户表中）。
将查询的结果按照 invoice_id 排序。

select t1.invoice_id,t3.customer_name,
    t1.price as price, 
    count(distinct t2.contact_email) as contacts_cnt,
    ifnull(t4.trusted_contacts_cnt,0) as trusted_contacts_cnt
from Invoices t1
left join Contacts t2
on t1.user_id = t2.user_id
left join Customers t3
on t3.customer_id = t1.user_id
left join (
    select Contacts.user_id, count(distinct Customers.customer_id) as trusted_contacts_cnt
    from Contacts
    inner join Customers
    on Contacts.contact_name = Customers.customer_name
    group by Contacts.user_id
) t4
on t4.user_id = t1.user_id
group by t1.invoice_id

1369. 获取第二次的活动

展示每一位用户 最近第二次 的活动，如果用户仅有一次活动，返回该活动

select username, activity,startDate,endDate
from (
    select *,
        row_number() over(partition by username order by startDate desc) as rk1,
        row_number() over(partition by username order by startDate asc) as rk2
    from UserActivity
) tmp
where (rk1 = rk2 and rk1 = 1)  or rk1=2