Leetcode题库（数据库合集）

难度：简单

1. 组合两个表

表1：Person

PersonId 是上表主键
表2： Address

AddressId 是上表主键
编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：
FirstName, LastName, City, State

select a.FirstName, a.LastName, b.City, b.State 
from Person a 
left join Address b 
on a.PersonID = b.PersonID

2. 第二高的薪水

编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。

例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

方法一：
因为排序可能会出现薪资相同的情况，

select max(Salary) as SecondHighestSalary
from (
select Salary, row_number() over (order by Salary desc) as rnk
from employee b
group by Salary
) a
where a.rnk = 2

方法二：
通过取最大值再去排除最大值去找到第二高的薪水。

select max(Salary) as SecondHighestSalary 
from Employee
where Salary < (select max(Salary) from Employee)

3. 第N高的薪水

有如下两张表T
编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。

例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。

方法一：

CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT AS
BEGIN    
RETURN (        
select Salary as getNthHighestSalary        
from (select  Salary ,dense_rank() over(order by Salary desc) as rnk        
from Employee        
group by Salary) a        
where rnk = @N  );
END

方法二：

CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT AS
BEGIN
RETURN ( select distinct Salary 
from Employee
order by Salary desc
Offset @N-1 rows
Fetch next 1 rows only);
END

4. 分数排名

编写一个 SQL 查询来实现分数排名。
如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

select Score,DENSE_RANK() OVER(ORDER BY Score desc) as Rank
from Scores

5. 连续出现的数字

表：Logs

编写一个 SQL 查询，查找所有至少连续出现三次的数字。
返回的结果表中的数据可以按 任意顺序 排列。
查询结果格式如下面的例子所示：

方法一：
如果是连续100次，1000次数值相同，那么这种方法就不适用了

select distinct a.Num as  ConsecutiveNums
from Logs a 
inner join Logs b 
on a.ID = B.ID +1 and a.NUm = b.Num
inner join Logs c 
on a.ID = C.ID +2 and b.Num = c.Num 

方法二：

SELECT DISTINCT Num as ConsecutiveNums 
FROM (SELECT Num,COUNT(1) as SerialCount 
FROM (SELECT Id,Num,row_number() over(order by id) -ROW_NUMBER() over(partition by Num order by Id) as SerialNumberSubGroup
FROM Logs) as Sub
GROUP BY Num,SerialNumberSubGroup HAVING COUNT(1) >= 3) as Result

6. 超过经理收入的员工

Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。

给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

7. 重新

if object_id('department','u') is not null drop table department
create table department (
    id int
    ,revenue INT
    ,MONTH VARCHAR(10)

)
INSERT INTO DEPARTMENT(id,REVENUE,MONTH)
VALUES
 (1,8000    , 'Jan'  )
,(2,9000    , 'Jan'  )
,(3,10000   , 'Feb'  )
,(1,7000    , 'Feb'  )
,(1,6000    , 'Mar'  )
select id
,sum(case when month = 'Jan' then revenue else null end) as jan_revenue
,sum(case when month = 'Feb' then revenue else null end) as Feb_revenue
,sum(case when month = 'Mar' then revenue else null end) as Mar_revenue
,sum(case when month = 'Apr' then revenue else null end) as Apr_revenue
,sum(case when month = 'May' then revenue else null end) as May_revenue
,sum(case when month = 'Jun' then revenue else null end) as Jun_revenue
,sum(case when month = 'Jul' then revenue else null end) as Jul_revenue
,sum(case when month = 'Aug' then revenue else null end) as Aug_revenue
,sum(case when month = 'Sep' then revenue else null end) as Sep_revenue
,sum(case when month = 'Oct' then revenue else null end) as Oct_revenue
,sum(case when month = 'Nov' then revenue else null end) as Nov_revenue
,sum(case when month = 'Dec' then revenue else null end) as Dec_revenue
from DEPARTMENT
group by id

8. 寻找用户推荐人

给定表 customer ，里面保存了所有客户信息和他们的推荐人。

写一个查询语句，返回一个客户列表，列表中客户的推荐人的编号都 不是 2。

对于上面的示例数据，结果为：

--方法一：执行耗时852ms
select name 
from customer 
where isnull(referee_id,0) <> 2

--方法二：执行耗时1038ms
select name 
from customer 
where id not in (
    select id from customer where referee_id = 2
)

9. 销售员

编写一个SQL查询，报告没有任何与名为 “RED” 的公司相关的订单的所有销售人员的姓名。

以 任意顺序 返回结果表。

--方法一：运行耗时903ms

SELECT s.name
FROM salesperson s
WHERE s.sales_id NOT IN (SELECT
            o.sales_id
        FROM orders o
        LEFT JOIN company c 
        ON o.com_id = c.com_id
        WHERE c.name = 'RED')
;

10. 排名靠前的旅行者

表：Users

表：Rides

写一段 SQL , 报告每个用户的旅行距离。

返回的结果表单，以 travelled_distance 降序排列 ，如果有两个或者更多的用户旅行了相同的距离, 那么再以 name 升序排列 。

查询结果格式如下例所示。

select name,travelled_distance from (
select b.id,b.name,isnull(sum(distance),0) as travelled_distance 
from users b
left join rides a 
on a.user_id = b.id 
group by b.id,b.name ) a 
order by travelled_distance desc,name asc

11. 患某种疾病的患者

患者信息表： Patients

写一条 SQL 语句，查询患有 I 类糖尿病的患者 ID （patient_id）、患者姓名（patient_name）以及其患有的所有疾病代码（conditions）。I 类糖尿病的代码总是包含前缀 DIAB1 。

按 任意顺序 返回结果表。

select * 
from patients 
where conditions like 'DIAB1%'
or conditions like '% DIAB1%'

12. 修复表中的名字

表： Users

编写一个 SQL 查询来修复名字，使得只有第一个字符是大写的，其余都是小写的。

返回按 user_id 排序的结果表。

select user_id,
concat(upper(left(name, 1)), lower(right(name, len(name) - 1))) name
from Users
order by user_id

13. 求关注者的数量

表： Followers

写出 SQL 语句，对于每一个用户，返回该用户的关注者数量。

按 user_id 的顺序返回结果表。

select user_id ,isnull(count(*),0) as followers_count
from Followers 
group by user_id

14. 可回收且低脂的产品

表：Products

写出 SQL 语句，查找既是低脂又是可回收的产品编号。

返回结果 无顺序要求 。

select product_id from Products 
where low_fats ='Y' and recyclable ='Y'

15. 计算特殊奖金

表: Employees

写出一个SQL 查询语句，计算每个雇员的奖金。如果一个雇员的id是奇数并且他的名字不是以’M’开头，那么他的奖金是他工资的100%，否则奖金为0。

Return the result table ordered by employee_id.

返回的结果集请按照employee_id排序。

select employee_id  
,case when employee_id % 2 = 1 and left(name  ,1) <>'M'
then salary  else 0 end as bonus 
from Employees 
order by employee_id

16. 丢失信息的雇员

表: Employees

表: Salaries

写出一个查询语句，找到所有 丢失信息 的雇员id。当满足下面一个条件时，就被认为是雇员的信息丢失：

雇员的 姓名 丢失了，或者
雇员的 薪水信息 丢失了，或者
返回这些雇员的id employee_id ， 从小到大排序 。

select employee_id from 
(
    select employee_id from Employees
    union all
    select employee_id from Salaries
)as t
group by employee_id
having count(employee_id) = 1
order by employee_id

17. 每个产品在不同商店的价格

表：Products

请你重构 Products 表，查询每个产品在不同商店的价格，使得输出的格式变为(product_id, store, price) 。如果这一产品在商店里没有出售，则不输出这一行。

输出结果表中的 顺序不作要求 。

select *from (
select product_id,store,price
from Products 
unpivot(price for store in(store1  ,store2,store3  )) c)a 
where price is not null

18. 文章浏览

请编写一条 SQL 查询以找出所有浏览过自己文章的作者，结果按照 id 升序排列。

查询结果的格式如下所示：

--distinct 去重
select distinct author_id as id 
from Views 
where author_id = viewer_id
order by author_id 

--group by 去重
select  author_id as id 
from Views 
where author_id = viewer_id
group by author_id
order by author_id 

19. 上升的温度

编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 id 。
返回结果 不要求顺序 。
查询结果格式如下例。

select a.id from weather a
left join weather b 
on a.recordDate = dateadd(day,1,b.recordDate)
where a.temperature > b.temperature

20. 按日期分组销售产品

编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称。
每个日期的销售产品名称应按词典序排列。
返回按 sell_date 排序的结果表。
查询结果格式如下例所示。

--MS SQL SERVER
select sell_date ,count(distinct product) as  num_sold 
,STUFF((select distinct ','+product 
from activities B 
where  A.sell_date = B.sell_date 
FOR XML PATH('')),1,1,'') as products                     
from activities a
group by sell_date

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

21. 员工奖金

选出所有 bonus < 1000 的员工的 name 及其 bonus。

--建表
if object_id('Employee','u') is not null drop table Employee
go
create table Employee(
    empId int,  name  varchar(20), supervisor int , salary  int
)
go
insert into Employee
values
 (1  ,'John'   ,3       , 1000 )
,(2  ,'Dan'    ,3       , 2000 )
,(3  ,'Brad'   ,null    , 4000 )
,(4  ,'Thomas' ,3       , 4000 )
go
if object_id('Bonus','u') is not null drop table Bonus
go
create table Bonus (
    empId int , bonus  int
)
go
insert into Bonus
values
 (2    ,500 )
,(4    ,2000)
go
--查询
select a.name,b.bonus
from employee a
left join bonus b
on a.empid = b.empid
where isnull(b.bonus,0) < 1000

22. 使用唯一标识码替换员工Id

写一段SQL查询来展示每位用户的 唯一标识码（unique ID ）；如果某位员工没有唯一标识码，使用 null 填充即可。
你可以以 任意 顺序返回结果表。

--建表
if object_id('Employees','u') is not null drop table Employees
go
create table Employees  (
 id            int
, name         varchar(20)
)
go
insert into Employees
values
(1  ,'Alice'    )
,(7  ,'Bob'      )
,(11 ,'Meir'     )
,(90 ,'Winston'  )
,(3  ,'Jonathan' )
go
if object_id('EmployeeUNI','u') is not null drop table EmployeeUNI
go
create table EmployeeUNI(
  id           int
, unique_id     int
)
go
insert into EmployeeUNI
values
 (3  , 1   )
,(11 , 2   )
,(90 , 3   )
go
--查询
select b.unique_id,a.name
from Employees a
left join EmployeeUNI b
on a.id = b.id

23. 订单最多的客户

编写一个SQL查询，为下了 最多订单 的客户查找 customer_number 。
测试用例生成后， 恰好有一个客户 比任何其他客户下了更多的订单。

--建表
if object_id('Orders','u') is not null drop table Orders
go
create table Orders(
  order_number    int
, customer_number int
)
go
insert into Orders
values
 (1   ,1   )
,(2   ,2   )
,(3   ,3   )
,(4   ,3   )
go
--查询
--方法一
select customer_number from (
select *,row_number() over(order by cnt desc ) as rnk
from (select customer_number ,count(distinct order_number) as cnt
        from Orders
        group by customer_number ) a ) a
where rnk = 1
--方法二
select top 1 customer_number 
from orders 
group by customer_number 
order by count(order_number) desc

24. 判断三角形

写一个SQL查询，每三个线段报告它们是否可以形成一个三角形。
以 任意顺序 返回结果表。

--建表
if object_id('Triangle','u') is not null drop table Triangle
go
create table Triangle(
 x         int
,y         int
,z         int
)
go
insert into Triangle
values
 ( 13, 15 ,30 )
,( 10, 20 ,15 )
go
--查询
select *,
       case when x+y>z and  x+z>y and  y+z >x then 'Yes' else 'No' END as triangle
FROM Triangle

25. 只出现一次的最大数字

单一数字 是在 MyNumbers 表中只出现一次的数字。
请你编写一个 SQL 查询来报告最大的 单一数字 。如果不存在 单一数字 ，查询需报告 null 。

if object_id('MyNumbers','u') is not null drop table MyNumbers
go
create table MyNumbers  (
     num        int
)
go
insert into MyNumbers
values
 ( 8  )
,( 8  )
,( 3  )
,( 3  )
,( 1  )
,( 4  )
,( 5  )
,( 6  )
go
--查询
select max(num) as num
from (select num
        from MyNumbers
        group by num
        having count(*) = 1 ) a

26. 平均售价

编写SQL查询以查找每种产品的平均售价。
average_price 应该四舍五入到小数点后两位。

--建表
if object_id('Prices','u') is not null drop table Prices
go
create table Prices(
  product_id     int
, start_date     date
, end_date       date
, price          int
)
go
insert into Prices
values
 (1    ,'2019-02-17','2019-02-28', 5      )
,(1    ,'2019-03-01','2019-03-22', 20     )
,(2    ,'2019-02-01','2019-02-20', 15     )
,(2    ,'2019-02-21','2019-03-31', 30     )
go
if object_id('UnitsSold','u') is not null drop table UnitsSold
go
create table UnitsSold (
    product_id     int
,purchase_date  date
, units          int
)
go
insert into UnitsSold
values
 (1 ,'2019-02-25', 100)
,(1 ,'2019-03-01', 15 )
,(2 ,'2019-02-10', 200)
,(2 ,'2019-03-22', 30 )
go
--查询

select product_id ,cast(sum(price * units ) * 1.0 /sum(units) as decimal(19,2)) average_price
from (
select a.*,b.units
from Prices  a
left join UnitsSold b
on a.product_id = b.product_id
and b.purchase_date between a.start_date and a.end_date ) a
group by product_id

27. 查找拥有有效邮箱的用户

写一条 SQL 语句，查询拥有有效邮箱的用户。
有效的邮箱包含符合下列条件的前缀名和域名：
前缀名是包含字母（大写或小写）、数字、下划线 ‘_’、句点 ‘.’ 和/或横杠 ‘-’ 的字符串。前缀名必须以字母开头。
域名是 ‘@leetcode.com’ 。
按任意顺序返回结果表。

--建表
if object_id('Users','u') is not null drop table Users
go
create table Users (
  user_id       int
, name          varchar(100)
, mail          varchar(100)
)
go
insert into Users
values
 ( 1    ,'Winston'   ,'winston@leetcode.com'    )
,( 2    ,'Jonathan'  ,'jonathanisgreat'         )
,( 3    ,'Annabelle' ,'bella-@leetcode.com'     )
,( 4    ,'Sally'     ,'sally.come@leetcode.com' )
,( 5    ,'Marwan'    ,'quarz#2020@leetcode.com' )
,( 6    ,'David'     ,'david69@gmail.com'       )
,( 7    ,'Shapiro'   ,'.shapo@leetcode.com'     )
go
--查询
SELECT *
FROM Users
WHERE mail LIKE '[A-Za-z]%@leetcode.com' AND mail NOT LIKE '%[^A-Za-z0-9._/-]%@%'

28. 查询结果的质量和占比

将查询结果的质量 quality 定义为：
各查询结果的评分与其位置之间比率的平均值。

将劣质查询百分比 poor_query_percentage 为：
评分小于 3 的查询结果占全部查询结果的百分比。

编写一组 SQL 来查找每次查询的名称(query_name)、质量(quality) 和 劣质查询百分比(poor_query_percentage)。
质量(quality) 和劣质查询百分比(poor_query_percentage) 都应四舍五入到小数点后两位。

--建表
if object_id('Queries','u') is not null drop table Queries
go
create table Queries(
 query_name   varchar(100)
,result       varchar(100)
,position     int
,rating       int
)
go
insert into     Queries
values
 ('Dog',       'Golden Retriever'  ,1    ,5  )
,('Dog',       'German Shepherd'   ,2    ,5  )
,('Dog',       'Mule'              ,200  ,1  )
,('Cat',       'Shirazi'           ,5    ,2  )
,('Cat',       'Siamese'           ,3    ,3  )
,('Cat',       'Sphynx'            ,7    ,4  )
go
--查询
select query_name, cast(avg(rating *1.0 / position )  as decimal(19,2)) as Quality
,cast(sum(case when rating < 3 then 1 else 0 end ) * 100.0 /count(*) as decimal(19,2)) as poor_query_percentage
from Queries
group by query_name

29. 列出指定时间段内所有的下单产品

写一个解决方案，要求获取在 2020 年 2 月份下单的数量不少于 100 的产品的名字和数目。
返回结果表单的 顺序无要求 。

--建表
if object_id('Products','U') is not null drop table Products
go
create table Products(
     product_id       int
 ,product_name     varchar(100)
 ,product_category varchar(100)
)
go
insert into Products
values
 (1      ,'Leetcode Solutions'    ,'Book'       )
,(2      ,'Jewels of Stringology' ,'Book'       )
,(3      ,'HP'                    ,'Laptop'     )
,(4      ,'Lenovo'                ,'Laptop'     )
,(5      ,'Leetcode Kit'          ,'T-shirt'    )
go
if object_id('Orders','u') is not null drop table Orders
go
create table Orders(
  product_id    int
 ,order_date    date
 ,unit          int
)
go
insert into Orders
values
 ( 1   ,'2020-02-05',60 )
,( 1   ,'2020-02-10',70 )
,( 2   ,'2020-01-18',30 )
,( 2   ,'2020-02-11',80 )
,( 3   ,'2020-02-17',2  )
,( 3   ,'2020-02-24',3  )
,( 4   ,'2020-03-01',20 )
,( 4   ,'2020-03-04',30 )
,( 4   ,'2020-03-04',60 )
,( 5   ,'2020-02-25',50 )
,( 5   ,'2020-02-27',50 )
,( 5   ,'2020-03-01',50 )
go
--查询
select a.Product_name,sum(b.Unit) as Unit
from Products  a
left join orders b
on a.product_id = b.product_id
where year(order_date ) = 2020 and month(order_date) = 2
group by a.product_name
having sum(b.Unit) >= 100

30. 最高薪水差异

编写一个解决方案，计算 市场部门 和 工程部门 中 最高 工资之间的差异。输出工资的绝对差异。
返回结果表。
返回结果格式如下示例所示。

--建表
if object_id('Salaries','u') is not null drop table Salaries
go
create table Salaries(
 emp_name     varchar(20)
, department   varchar(20)
, salary      int
)
go
insert into Salaries
values
 (  'Kathy'    ,'Engineering' ,50000  )
,(  'Roy'      ,'Marketing'   ,30000  )
,(  'Charles'  ,'Engineering' ,45000  )
,(  'Jack'     ,'Engineering' ,85000  )
,(  'Benjamin' ,'Marketing'   ,34000  )
,(  'Anthony'  ,'Marketing'   ,42000  )
,(  'Edward'   ,'Engineering' ,102000 )
,(  'Terry'    ,'Engineering' ,44000  )
,(  'Evelyn'   ,'Marketing'   ,53000  )
,(  'Arthur'   ,'Engineering' ,32000  )
go
--查询
select ABS(max(a.salary) - max(b.salary)) as salary_difference
from salaries a
full outer join salaries b
on b.department = 'Marketing'
where a.department = 'Engineering'

31. 总旅行距离

编写一个解决方案，计算每个用户的旅行距离 distance 。如果有用户没有任何旅行记录，那么他们的 distance 应被视为 0 。输出 user_id, name 和总旅行距离 traveled distance 。
按 升序排序 的 user_id 返回结果表。
结果格式如下示例。

--建表
if object_id('Users','u') is not null drop table Users
go
create table Users(
user_id      int
, name        varchar(20)
)
go
insert into Users
values
 ( 17  ,'Addison' )
,( 14  ,'Ethan'   )
,( 4   ,'Michael' )
,( 2   ,'Avery'   )
,( 10  ,'Eleanor' )
go
if object_id('Rides','u') is not null drop table Rides
go
create table Rides (
ride_id       int
,user_id       int
,distance      int
)
go
insert into Rides
values
 ( 72  ,17   ,160  )
,( 42  ,14   ,161  )
,( 45  ,4    ,59   )
,( 32  ,2    ,197  )
,( 15  ,4    ,357  )
,( 56  ,2    ,196  )
,( 10  ,14   ,25   )
go
--查询
select a.user_id ,a.name
, isnull(sum(b.distance ),0) as [traveled distance]
from Users  a
left join Rides b
on a.user_id = b.user_id
group by a.user_id ,a.name
order by a.user_id

32. 自行车的最后使用时间

编写一个解决方案，找出每辆自行车 最近一次被使用 的时间。
返回结果表按 最近被使用 的自行车进行排序。
返回结果的格式如下所示：

--建表
if object_id('Bikes' ,'u') is not null drop table Bikes
go
create table Bikes(
    ride_id     int
, bike_number  varchar(20)
, start_time   datetime
, end_time     datetime
)
go
insert into Bikes
values
 ( 1,'W00576', '2012-03-25 11:30:00',  '2012-03-25 12:40:00')
,( 2,'W00300', '2012-03-25 10:30:00',  '2012-03-25 10:50:00')
,( 3,'W00455', '2012-03-26 14:30:00',  '2012-03-26 17:40:00')
,( 4,'W00455', '2012-03-25 12:30:00',  '2012-03-25 13:40:00')
,( 5,'W00576', '2012-03-25 08:10:00',  '2012-03-25 09:10:00')
,( 6,'W00576', '2012-03-28 02:30:00',  '2012-03-28 02:50:00')
go
--查询
select bike_number
,max(end_time            ) as end_time
from Bikes
group by bike_number
order by end_time desc

33. 统计 Spotify 排行榜上艺术家出现次数

编写解决方案来查找每个艺术家在Spotify排行榜上出现的次数。
返回结果表，其中包含艺术家的名称以及相应的出现次数，按出现次数 降序 排列。如果出现次数相等，则按艺术家名称 升序 排列。
返回结果格式如下所示：

--建表
if object_id('Spotify','u') is not null drop table Spotify
go
create table Spotify(
 id         int
, track_name   varchar(20)
, artist      varchar(20)
)
go
insert into  Spotify
values
 ( 303651  ,'Heart Won''t Forget' ,'Sia'       )
,( 1046089 ,'Shape of you'       ,'Ed Sheeran')
,( 33445   ,'I''m the one'        ,'DJ Khalid' )
,( 811266  ,'Young Dumb & Broke' ,'DJ Khalid' )
,( 505727  ,'Happier'            ,'Ed Sheeran')
go
--查询
select artist     ,count(id ) as occurrences
from Spotify
group by artist
order by count(id ) desc,artist

34. 查询员工当前薪水

找出每个员工的当前薪水，假设薪水每年增加。输出他们的 emp_id 、firstname 、lastname 、salary 和 department_id 。
按 emp_id 升序排序 返回结果表。
返回结果格式如下所示。

--建表
if object_id('Salary','u') is not null drop table Salary
go
create table Salary(
 emp_id         int
, firstname     varchar(20)
, lastname      varchar(20)
, salary         varchar(20)
, department_id  varchar(20)
)
go
insert into Salary
values
 ( 1, 'Todd'      ,'Wilson'   ,110000 ,'D1006')
,( 1, 'Todd'      ,'Wilson'   ,106119 ,'D1006')
,( 2, 'Justin'    ,'Simon'    ,128922 ,'D1005')
,( 2, 'Justin'    ,'Simon'    ,130000 ,'D1005')
,( 3, 'Kelly'     ,'Rosario'  ,42689  ,'D1002')
,( 4, 'Patricia'  ,'Powell'   ,162825 ,'D1004')
,( 4, 'Patricia'  ,'Powell'   ,170000 ,'D1004')
,( 5, 'Sherry'    ,'Golden'   ,44101  ,'D1002')
,( 6, 'Natasha'   ,'Swanson'  ,79632  ,'D1005')
,( 6, 'Natasha'   ,'Swanson'  ,90000  ,'D1005')
go
--查询

select
emp_id , firstname, lastname , salary ,department_id
from (select
        emp_id , firstname, lastname , salary ,department_id
        ,rank() over(partition by emp_id order by salary desc ) as rnk
        from Salary  ) a
where rnk =1
order by emp_id

35. 把名字和职业联系起来

编写一个解决方案报告每个人的名字，后面是他们职业的第一个字母，用括号括起来。
返回按 person_id 降序排列 的结果表。
返回结果格式示例如下。

--建表
if object_id('Person' ,'u') is not null drop table Person
go
create table Person (
 person_id   int
, name         varchar(20)
, profession  varchar(20)
)
go
insert into Person
values
 (  1  ,'Alex'  ,'Singer'    )
,(  3  ,'Alice' ,'Actor'     )
,(  2  ,'Bob'   ,'Player'    )
,(  4  ,'Messi' ,'Doctor'    )
,(  6  ,'Tyson' ,'Engineer'  )
,(  5  ,'Meir'  ,'Lawyer'    )
go
--查询
select person_id
     , name + '(' + left(profession ,1) +')' as name
from Person
order by person_id  desc

36. 形成化学键

如果一个元素是 ‘Metal’，另外一个元素是 ‘Nonmetal’ ，那么它们可以形成键。
编写一个解决方案找出所有可以形成键的元素对。
以 任意顺序 返回结果表。
查询结果格式如下所示。

--建表
if object_id('Elements','u') is not null drop table Elements
go
create table Elements(
   symbol     varchar(20)
, type       varchar(20)
, electrons   int
)
go
insert into Elements
values
 ( 'He'  ,'Noble'    ,0   )
,( 'Na'  ,'Metal'    ,1   )
,( 'Ca'  ,'Metal'    ,2   )
,( 'La'  ,'Metal'    ,3   )
,( 'Cl'  ,'Nonmetal' ,1   )
,( 'O'   ,'Nonmetal' ,2   )
,( 'N'   ,'Nonmetal' ,3   )
go
--查询
select  a.symbol as metal,b.symbol as nonmetal
from Elements  a
cross join (select distinct symbol  from Elements
    where type     = 'Nonmetal'
    ) b
where a.type     = 'Metal'

37. 整理奥运表

奥运名次表的排序规则如下:

• 金牌越多的国家排名第一。
• 如果金牌数持平，银牌多的国家排名第一。
• 如果银牌数量持平，铜牌数量最多的国家排名第一。
• 如果铜牌中出现并列，那么并列的国家将按照字典的升序进行排序。
  写一个解决方案对奥运表进行排序

返回结果格式示例如下。

--建表
if object_id('Olympic','u') is not null drop table Olympic
go
create table Olympic (
  country      varchar(20)
, gold_medals  int
, silver_medals int
, bronze_medals  int
)
go
insert into Olympic
values
  ( 'China'       , 10   , 10   ,20  )
 ,( 'South Sudan' , 0    , 0    ,1   )
 ,( 'USA'         , 10   , 10   ,20  )
 ,( 'Israel'      , 2    , 2    ,3   )
 ,( 'Egypt'       , 2    , 2    ,2   )
 go
--查询
select country   ,gold_medals   , silver_medals  , bronze_medals
from (
select *
,rank() over(order by gold_medals desc ) as gold_rank
,rank() over(order by silver_medals desc ) as silver_rank
,rank() over(order by bronze_medals desc) as bronze_rank
from Olympic ) a
order by gold_rank ,silver_rank ,bronze_rank ,country

38. 每位教师所教授的科目种类的数量

查询每位老师在大学里教授的科目种类的数量。
以 任意顺序 返回结果表。
查询结果格式示例如下。

--建表
if object_id('Teacher','u') is not null drop table Teacher
go
create table Teacher(
 teacher_id   int
, subject_id   int
, dept_id     int
)
go
insert into Teacher
values
 (  1   ,2   ,3   )
,(  1   ,2   ,4   )
,(  1   ,3   ,3   )
,(  2   ,1   ,1   )
,(  2   ,2   ,1   )
,(  2   ,3   ,1   )
,(  2   ,4   ,1   )
go
--查询
select teacher_id ,count(distinct subject_id ) as cnt
from Teacher
group by teacher_id

39. 联赛的所有比赛

编写解决方案，获取联赛中所有比赛。每两支球队进行两场比赛，其中一支球队是主队 home_team ，另一支是客场队 away_team。
按 任意顺序 返回结果表。
返回结果格式如下例所示。

--建表
if object_id('Teams','u') is not null drop table Teams
go
create table Teams(
 team_name    varchar(20)
)
go
insert into Teams
values
 ( 'Leetcode FC' )
,( 'Ahly SC'     )
,( 'Real Madrid' )
go
--查询
select a.team_name as home_team      ,b.team_name as away_team
from Teams a
cross join Teams b
where a.team_name   <> b.team_name

39. 产品销售分析 ⑤

编写解决方案，获取每个用户的消费额。
按用户消费额 spending 递减的顺序返回结果。在消费额相等的情况下，以 user_id 递增的顺序将其排序。
结果的格式如下面例子所示：

在这里插入代码片

难度：中等

1.股票的资本损益

Stocks 表：

编写一个SQL查询来报告每支股票的资本损益。

股票的资本损益是一次或多次买卖股票后的全部收益或损失。

以任意顺序返回结果即可。

SELECT stock_name,
        SUM(
            CASE operation WHEN 'sell'  
            THEN price ELSE -price  
            END                  
           ) AS capital_gain_loss     
FROM Stocks
GROUP BY stock_name

2. 当选者

编写一个SQL查询来报告获胜候选人的名字(即获得最多选票的候选人)。

生成测试用例以确保 只有一个候选人赢得选举。

查询结果格式如下所示。

--MS SQL Server
select name
from Candidate
where id in (
    select candidateId
    from (select top 1  candidateId,count(*) as cnt
               from vote
               group by candidateId
               order by count(*) desc)a
  )

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

3. 页面推荐

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

select distinct page_id as recommended_page 
from Likes 
where user_id in (select user2_id 
                    from (select user1_id , user2_id from friendship
                          union 
                          select  user2_id ,user1_id  from friendship) a 
                    where user1_id = 1 )
and page_id not in (select page_id from Likes where user_id = 1 )

4. 2016年的投资

写一个查询语句，将 2016 年 (TIV_2016) 所有成功投资的金额加起来，保留 2 位小数。
对于一个投保人，他在 2016 年成功投资的条件是：
他在 2015 年的投保额 (TIV_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
他所在的城市必须与其他投保人都不同（也就是说维度和经度不能跟其他任何一个投保人完全相同）。

IF OBJECT_ID('insurance','U') IS NOT NULL DROP TABLE  insurance
GO

CREATE TABLE insurance(
 PID         INT
,TIV_2015    NUMERIC(15,2)
,TIV_2016    NUMERIC(15,2)
,LAT         NUMERIC(5,2)
,LON         NUMERIC(5,2)
)
GO

INSERT INTO insurance
VALUES
 ( 1   , 224.17   , 952.73   , 32.4 , 20.2 )
,( 2   , 224.17   , 900.66   , 52.4 , 32.7 )
,( 3   , 824.61   , 645.13   , 72.4 , 45.2 )
,( 4   , 424.32   , 323.66   , 12.4 , 7.7  )
,( 5   , 424.32   , 282.9    , 12.4 , 7.7  )
,( 6   , 625.05   , 243.53   , 52.5 , 32.8 )
,( 7   , 424.32   , 968.94   , 72.5 , 45.3 )
,( 8   , 624.46   , 714.13   , 12.5 , 7.8  )
,( 9   , 425.49   , 463.85   , 32.5 , 20.3 )
,( 10  , 624.46   , 776.85   , 12.4 , 7.7  )
,( 11  , 624.46   , 692.71   , 72.5 , 45.3 )
,( 12  , 225.93   , 933      , 12.5 , 7.8  )
,( 13  , 824.61   , 786.86   , 32.6 , 20.3 )
,( 14  , 824.61   , 935.34   , 52.6 , 32.8 )


select cast(sum(TIV_2016) as numeric(15,2)) as  TIV_2016
from (SELECT *,COUNT(*) over(partition by TIV_2015 ) as cnt1
      FROM (SELECT PID,TIV_2015,TIV_2016,LAT,LON ,COUNT(*) OVER(PARTITION BY LAT,LON) AS CNT
            FROM insurance
            GROUP BY PID,TIV_2015,TIV_2016,LAT,LON) A
        WHERE CNT = 1
        GROUP BY PID,TIV_2015,TIV_2016,LAT,LON,cnt ) a
where cnt1 >= 2

5. 买下所有产品的人

写一条 SQL 查询语句，从 Customer 表中查询购买了 Product 表中所有产品的客户的 id。

if object_id('Customer','u') is not null drop table Customer
go
create  table Customer(
customer_id  int    ,
product_key  int
)
go
insert into   Customer
values
 (1    ,      5   )
,(2    ,      6   )
,(3    ,      5   )
,(3    ,      6   )
,(1    ,      6   )
go
if object_id ('Product','u') is not null drop table Product
go
create table Product(
   product_key  int
)
go
insert into Product
values
 (5)
,(6)
go

select a.customer_id
from Customer a
group by a.customer_id
having  count( distinct a.product_key )  = (select count(*) from Product)

6. 电影评分

请你编写一组 SQL 查询：
查找评论电影数量最多的用户名。如果出现平局，返回字典序较小的用户名。
查找在 February 2020 平均评分最高 的电影名称。如果出现平局，返回字典序较小的电影名称。
字典序 ，即按字母在字典中出现顺序对字符串排序，字典序较小则意味着排序靠前。

select name as results       from (
select a.user_id,b.name,rank()  over(order by count(*) desc) as rnk1
,row_number() over(order by b.name ) as rnk
from MovieRating a
left join Users b
on a.user_id = b.user_id
group by a.user_id ,b.name ) a
where rnk1 = 1 and rnk = 1
union all
select title from (
select  b.title ,sum(rating)*1.0/count(*) as Score
,rank()  over(order by count(*) desc) as rnk1
,row_number() over(order by b.title ) as rnk
from MovieRating a
left join Movies  b
on a.movie_id = b.movie_id
where left(created_at,7) = '2020-02'
group by b.title ) a
where rnk1 = 1 and rnk = 1

6. 确认率

用户的 确认率 是 ‘confirmed’ 消息的数量除以请求的确认消息的总数。没有请求任何确认消息的用户的确认率为 0 。确认率四舍五入到 小数点后两位 。
编写一个SQL查询来查找每个用户的 确认率 。
以 任意顺序 返回结果表。

if object_id('Signups','u') is not null drop table Signups
go
create table Signups (
  user_id        int
, time_stamp     datetime
)
go
insert into Signups
values
 (3    ,'2020-03-21 10:16:13')
,(7    ,'2020-01-04 13:57:59')
,(2    ,'2020-07-29 23:09:44')
,(6    ,'2020-12-09 10:39:37')
go
if object_id ('Confirmations','u') is not null drop table Confirmations
go
create table Confirmations(
  user_id         int
, time_stamp     datetime
, action          varchar(20) )
go
insert into Confirmations
values
 ( 3    , '2021-01-06 03:30:46', 'timeout'  )
,( 3    , '2021-07-14 14:00:00', 'timeout'  )
,( 7    , '2021-06-12 11:57:29', 'confirmed')
,( 7    , '2021-06-13 12:58:28', 'confirmed')
,( 7    , '2021-06-14 13:59:27', 'confirmed')
,( 2    , '2021-01-22 00:00:00', 'confirmed')
,( 2    , '2021-02-28 23:59:59', 'timeout'  )
go
--Output
select a.user_id
, cast(sum(case when b.action = 'confirmed' then 1 else 0 end )*1.0/count(*) as decimal(19,2)) as confirmation_rate
from Signups a
left join Confirmations b
on a.user_id = b.user_id
group by a.user_id
order by a.user_id

7. 按分类统计薪水

写出一个 SQL 查询，来报告每个工资类别的银行账户数量。 工资类别如下：
“Low Salary”：所有工资 严格低于 20000 美元。
“Average Salary”： 包含 范围内的所有工资 [$20000, $50000] 。
“High Salary”：所有工资 严格大于 50000 美元。
结果表 必须 包含所有三个类别。 如果某个类别中没有帐户，则报告 0 。
按 任意顺序 返回结果表。

if object_id('Accounts','u') is not null drop table Accounts
go
create table Accounts (
 account_id   int
,income    int
)
go
insert  into  Accounts
values
 (3        , 108939 )
,(2        , 12747  )
,(8        , 87709  )
,(6        , 91796  )
go
select * from Accounts


with t as (
select 'Low Salary' as Category
union
select 'Average Salary'
union
select 'High Salary'
)

select a.Category ,isnull(count(b.Category),0) as accounts_count
from t a
left join (select *,case when income < 20000 then'Low Salary'
            when income between  20000 and 50000 then 'Average Salary'
            else 'High Salary' end as Category
            from Accounts) b
on a.category = b.category
group by a.category

8. 餐馆营业额的变化增长

你是餐馆的老板，现在你想分析一下可能的营业额变化增长（每天至少有一位顾客）。
写一条 SQL 查询计算以 7 天（某日期 + 该日期前的 6 天）为一个时间段的顾客消费平均值。average_amount 要 保留两位小数。
查询结果按 visited_on 排序。

SELECT visited_on,amount,average_amount
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
    ) TABLE_1
) TABLE_2
WHERE DATEDIFF(day,(SELECT MIN(visited_on) FROM Customer) , visited_on) >=6

SELECT TOP 1 person_name
FROM (
SELECT *,
SUM(weight) OVER (ORDER BY turn ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS curr_weight
FROM Queue) t
WHERE curr_weight <= 1000
ORDER BY curr_weight DESC

8. 即时食物配送 ①

如果顾客期望的配送日期和下单日期相同，则该订单称为 「即时订单」，否则称为「计划订单」。
「首次订单」是顾客最早创建的订单。我们保证一个顾客只会有一个「首次订单」。
写一条 SQL 查询语句获取即时订单在所有用户的首次订单中的比例。保留两位小数。

if object_id ('Delivery','u') is not null drop table Delivery
go
create table Delivery (
 delivery_id                  int
,customer_id                  int
, order_date                   date
,customer_pref_delivery_date  date
)
go
insert into Delivery
values
 ( 1      ,1    , '2019-08-01' ,'2019-08-02')
,( 2      ,2    , '2019-08-02' ,'2019-08-02')
,( 3      ,1    , '2019-08-11' ,'2019-08-12')
,( 4      ,3    , '2019-08-24' ,'2019-08-24')
,( 5      ,3    , '2019-08-21' ,'2019-08-22')
,( 6      ,2    , '2019-08-11' ,'2019-08-13')
,( 7      ,4    , '2019-08-09' ,'2019-08-09')
go

select cast(sum(case when order_date = customer_pref_delivery_date then 1 else 0 end )
   * 1.0 /count(*)*100 as decimal(19,2))
    as immediate_percentage
from (select *
    ,row_number() over(Partition by customer_id  order by order_date ) as rnk
    from  Delivery) a
where rnk = 1

9. 至少有5名直系下属的经理

编写一个SQL查询，查询至少有5名直接下属的经理 。
以 任意顺序 返回结果表。

--建表
if object_id('Employee','u') is not null drop table Employee
go
create table Employee (
    id           int
, name         varchar(20)
, department   varchar(20)
, managerId    int
)
go
insert into Employee
values
(101 ,'John'  ,'A',    null )
,(102 ,'Dan'   ,'A',    '101'  )
,(103 ,'James' ,'A',    '101'  )
,(104 ,'Amy'   ,'A',    '101'  )
,(105 ,'Anne'  ,'A',    '101'  )
,(106 ,'Ron'   ,'B',    '101'  )
go
--方法一
select distinct name from Employee
where id in (
select managerid
from Employee
group by managerid
having count(distinct id ) >= 5)
--方法二
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

10. 游戏玩法分析

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

--建表
if object_id('Activity','u') is not null drop table Activity
go
create table Activity (
  player_id     int
, device_id     int
, event_date    date
, games_played  int
)
go
insert into Activity
values
 ( 1     ,2   ,'2016-03-01',5  )
,( 1     ,2   ,'2016-03-02',6  )
,( 2     ,3   ,'2017-06-25',1  )
,( 3     ,1   ,'2016-03-02',0  )
,( 3     ,4   ,'2018-07-03',5  )
go
--查询
with t as (select player_id id,min(event_date) mn
            from activity
            group by player_id)
select convert(decimal(4,2),(
    select convert(float,count(0))
    from t b
    inner join activity a
    on b.id=a.player_id and datediff(d,b.mn,a.event_date)=1
) / (select count(0) from t)) fraction

11. 好友申请：谁有最多的好友

在 Facebook 或者 Twitter 这样的社交应用中，人们经常会发好友申请也会收到其他人的好友申请。
写一个查询语句，找出拥有最多的好友的人和他拥有的好友数目。
生成的测试用例保证拥有最多好友数目的只有 1 个人。

--建表
if object_id('RequestAccepted','u') is not null drop table RequestAccepted
go
create table RequestAccepted(
  requester_id    int
, accepter_id     int
, accept_date     date
)
go
insert into RequestAccepted
values
 (1  , 2  ,'2016/06/03')
,(1  , 3  ,'2016/06/08')
,(2  , 3  ,'2016/06/08')
,(3  , 4  ,'2016/06/09')
go
--查询
select top 1 requester_id as id ,count(*) as num
from (
select requester_id,accepter_id
from RequestAccepted
union
select accepter_id,requester_id
from RequestAccepted )a
group by requester_id
order by count(*) desc

12. 指定日期的产品价格

写一段 SQL来查找在 2019-08-16 时全部产品的价格，假设所有产品在修改前的价格都是 10 。
以 任意顺序 返回结果表。

--建表
if object_id('Products','u') is not null drop table Products
go
create table Products(
  product_id    int
, new_price     int
, change_date    date
)
go
insert into Products
values
 ( 1   ,20  ,'2019-08-14')
,( 2   ,50  ,'2019-08-14')
,( 1   ,30  ,'2019-08-15')
,( 1   ,35  ,'2019-08-16')
,( 2   ,65  ,'2019-08-17')
,( 3   ,20  ,'2019-08-18')
go
--查询
with t as (
    select * from (
    select *,row_number() over(partition by product_id order by change_date desc ) as rnk
    from Products
    where change_date <= '2019-08-16') a
    where rnk = 1
)
select distinct a.Product_id ,isnull(b.new_price,10) as Price
from Products a
left join T B
ON a.product_id = b.product_id

13. 每月交易

编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。
以 任意顺序 返回结果表。

-- 建表
if object_id('Transactions','u') is not null drop table Transactions
go
create table Transactions(
 id            int
,country       varchar (100)
,state         varchar(100)
,amount        int
,trans_date    date
)
go
insert into Transactions
values
 ( 121  ,'US','approved', 1000,'2018-12-18')
,( 122  ,'US','declined', 2000,'2018-12-19')
,( 123  ,'US','approved', 2000,'2019-01-01')
,( 124  ,'DE','approved', 2000,'2019-01-07')
go
--查询
SELECT LEFT(trans_date ,7) AS month   , country
     ,COUNT(*) AS trans_count
,SUM(CASE WHEN STATE = 'approved' THEN 1 ELSE 0 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 LEFT(trans_date ,7) , country

14.市场分析

请写出一条SQL语句以查询每个用户的注册日期和在 2019 年作为买家的订单总数。
以 任意顺序 返回结果表。

if object_id('Users','u') is not null drop table Users
go
create table Users(
 user_id         int
,join_date       date
,favorite_brand  varchar(20)
)
go
insert into Users
values
 (1  ,'2018-01-01', 'Lenovo'  )
,(2  ,'2018-02-09', 'Samsung' )
,(3  ,'2018-01-19', 'LG'      )
,(4  ,'2018-05-21', 'HP'      )
go
if object_id ('Orders','u') is not null drop table Orders
go
create table Orders(
 order_id    int
,order_date  date
,item_id     int
,buyer_id    int
,seller_id   int
)
go
insert  into   Orders
values
 (1    ,'2019-08-01',4 ,1 ,2  )
,(2    ,'2018-08-02',2 ,1 ,3  )
,(3    ,'2019-08-03',3 ,2 ,3  )
,(4    ,'2018-08-04',1 ,4 ,2  )
,(5    ,'2018-08-04',1 ,3 ,4  )
,(6    ,'2019-08-05',2 ,2 ,4  )
go
if object_id('Items','u') is not null drop table Items
go
create table Items(
    item_id      int
 ,item_brand     varchar(20)
)
go
insert into Items
values
 (1 ,'Samsung'   )
,(2 ,'Lenovo'    )
,(3 ,'LG'        )
,(4 ,'HP'        )
go
--查询
--方法一
select a.user_id as buyer_id  ,a.join_date,isnull(b.num,0) as orders_in_2019
from Users a
left join (select buyer_id ,sum(case when left(order_date,4) = 2019 then 1 else 0 end  ) as num
    from Orders
    group by buyer_id) b
on a.user_id = b.buyer_id
--方法二
SELECT Users.user_id AS buyer_id, join_date,
 COUNT(CASE WHEN YEAR(Orders.order_date) = 2019 THEN 1 END) AS orders_in_2019
FROM Users
LEFT OUTER JOIN Orders 
ON Users.user_id = Orders.buyer_id
GROUP BY Users.user_id,join_date
ORDER BY Users.user_id

15. 即时食物配送 ②

如果顾客期望的配送日期和下单日期相同，则该订单称为 「即时订单」，否则称为「计划订单」。
「首次订单」是顾客最早创建的订单。我们保证一个顾客只会有一个「首次订单」。
写一条 SQL 查询语句获取即时订单在所有用户的首次订单中的比例。保留两位小数。

--建表
if object_id('Delivery','u') is not null drop table Delivery
go
create table Delivery(
 delivery_id                 int
,customer_id                 int
,order_date                  date
,customer_pref_delivery_date date

)
go
insert into Delivery
values
 ( 1    ,1    ,'2019-08-01','2019-08-02')
,( 2    ,2    ,'2019-08-02','2019-08-02')
,( 3    ,1    ,'2019-08-11','2019-08-12')
,( 4    ,3    ,'2019-08-24','2019-08-24')
,( 5    ,3    ,'2019-08-21','2019-08-22')
,( 6    ,2    ,'2019-08-11','2019-08-13')
,( 7    ,4    ,'2019-08-09','2019-08-09')
go
--查询
select cast(sum(case when order_date = customer_pref_delivery_date then 1 else 0 end )*1.0/count(*)*100 as decimal(19,2))as immediate_percentage
from (select *,row_number() over(partition by customer_id order by order_date) as rnk
        from Delivery)a
where rnk = 1

16. 计算税后工资

查找出每个员工的税后工资
每个公司的税率计算依照以下规则：
如果这个公司员工最高工资不到 $1000 ，税率为 0%
如果这个公司员工最高工资在 [1000, 10000] 之间，税率为 24%
如果这个公司员工最高工资大于 $10000 ，税率为 49%
按 任意顺序 返回结果。

--建表
if object_id('Salaries','u') is not null drop table Salaries
go
create table Salaries(
 company_id    int
,employee_id   int
,employee_name varchar(20)
,salary        int
)
go
insert into Salaries
values
 (1   ,1   ,'Tony'       ,2000  )
,(1   ,2   ,'Pronub'     ,21300 )
,(1   ,3   ,'Tyrrox'     ,10800 )
,(2   ,1   ,'Pam'        ,300   )
,(2   ,7   ,'Bassem'     ,450   )
,(2   ,9   ,'Hermione'   ,700   )
,(3   ,7   ,'Bocaben'    ,100   )
,(3   ,2   ,'Ognjen'     ,2200  )
,(3   ,13  ,'Nyancat'    ,3300  )
,(3   ,15  ,'Morninngcat',7777  )
go
--查询
select company_id,employee_id,employee_name
,cast (case when MaxSalary < 1000 then salary * (1-0)
when MaxSalary between  1000 and 10000 then Salary * (1-0.24)
else salary * (1-0.49) end  as decimal(19,0)) as Salary
from (
select company_id,employee_id,employee_name,salary
     ,max(salary) over(partition by company_id) as MaxSalary
from Salaries) a

17. 选举结果

选举在一个城市进行，每个人都可以投票给 一个或多个 候选人，也可以选择 不 投票。每个人都有 1 票，所以如果他们投票给多个候选人，他们的选票会被平均分配。例如，如果一个人投票给 2 个候选人，这些候选人每人获得 0.5 张选票。
编写一个 SQL 查询来查找获得最多选票并赢得选举的候选人 candidate 。输出 候选人 的姓名，或者如果多个候选人的票数 相等 ，则输出所有候选人的姓名。
返回按 candidate 升序排序 的结果表。

if object_id('Votes','u') is not null drop table Votes
go
create table Votes (
  voter      varchar(20)
, candidate  varchar (20)
)
go
insert into Votes
values
 ('Kathy'    ,null      )
,('Charles'  ,'Ryan'      )
,('Charles'  ,'Christine' )
,('Charles'  ,'Kathy'     )
,('Benjamin' ,'Christine' )
,('Anthony'  ,'Ryan'      )
,('Edward'   ,'Ryan'      )
,('Terry'    ,null      )
,('Evelyn'   ,'Kathy'     )
,('Arthur'   ,'Christine' )
go
--查询
select candidate from (
select candidate ,rank() over(order by sum(1.0/cnt)  desc) as rnk  from (
select * ,sum(case when candidate is null then 0 else 1 end)  over(partition by voter) as Cnt
from votes) a
where cnt <> 0
group by candidate) a
where rnk  = 1
order by candidate

18. 航班入座率和等待名单分析

乘客提前预订航班机票。如果乘客预订了某个航班的机票，并且该航班还有空座位，乘客的机票将会得到 确认 。然而，如果航班已经满员，乘客将会进入 等待名单 。
编写解决方案，报告每个航班已成功预订（获得座位）的乘客数以及处于等待名单上的乘客数。
按照 flight_id 升序排序 返回结果表。

--建表
if object_id('Flights','u') is not null drop table Flights
go
create table Flights(
flight_id   int
,capacity     int
)
go
insert into Flights
values
 (1  ,2   )
,(2  ,2   )
,(3  ,1   )
go
if object_id ('Passengers','u') is not null drop table Passengers
go
create table Passengers(
 passenger_id  int
,flight_id     int
)
go
insert into  Passengers
values
 (101     ,1    )
,(102     ,1    )
,(103     ,1    )
,(104     ,2    )
,(105     ,2    )
,(106     ,3    )
,(107     ,3    )
go
--查询
select a.flight_id
     ,isnull(sum(case when b.capacity>= cnt then 1 else 0 end ) ,0) as booked_cnt
    ,isnull(sum(case when b.capacity < cnt then 1 else 0 end),0) as waitlist_cnt
from Flights a
left join (
select a.*,b.capacity ,count(*) over(partition by a.flight_id order by passenger_id) as cnt
from Flights b
left join Passengers a
on a.flight_id = b.flight_id)b
on a.flight_id = b.flight_id
group by a.flight_id
order by a.flight_id

19. 统计文本中单词出现的次数

编写解决方案，找出单词 ‘bull’ 和 ‘bear’ 作为 独立词 出现的次数，不考虑任何它出现在两侧没有空格的情况（例如，‘bullet’, ‘bears’, ‘bull.’，或者 ‘bear’ 在句首或句尾 不会 被考虑）。
返回单词 ‘bull’ 和 ‘bear’ 以及它们对应的出现次数，顺序没有限制 。

--建表
if object_id('Files','u') is not null drop table Files
go
create table Files(
  file_name   varchar(20)
, content     text
)
go
insert into Files
values
 ( 'draft1.txt' ,'The stock exchange predicts a bull market which would make many investors happy.' )
,( 'draft2.txt' ,'The stock exchange predicts a bull market which would make many investors happy,but analysts warn of possibility of too much optimism and that in fact we are awaiting a bear market.'    )
,( 'draft3.txt' , 'The stock exchange predicts a bull market which would make many investors happy,but analysts warn of possibility of too much optimism and that in fact we are awaiting a bear market. As always predicting the future market is an uncertain game and all investors should follow their instincts and best practices.'         )
go
--查询
select 'bull' as word ,sum((  len(content)   -  len(replace(content,' bull ','') )) )/ len(' bull ') as count
from (select file_name,cast(content as varchar(100)) as content from files ) a
union
select 'bear' ,sum((  len(content)   -  len(replace(content,' bear ','') )) ) * 1.0/ len(' bear ')
from (select file_name,cast(content as varchar(100)) as content from files ) a

20. 查找活跃用户

编写一个解决方案，找出活跃用户。活跃用户是指在其任何一次购买之后的 七天内 进行了第二次购买的用户。
例如，如果结束日期是 2023年5月31日，那么在 2023年5月31日 和 2023年6月7日之间（包括这两天）的任何日期都被视为"在7天内"。
返回 任意顺序 的 user_id 列表，表示活跃用户列表。

--建表
if object_id('Users','u') is not null drop table Users
go
create table Users (
  user_id      int
, item         varchar(20)
, created_at  datetime
, amount      int
)
go
insert into Users
values
 (5    ,'Smart Crock Pot'   ,'2021-09-18', 698882 )
,(6    ,'Smart Lock'        ,'2021-09-14', 11487  )
,(6    ,'Smart Thermostat'  ,'2021-09-10', 674762 )
,(8    ,'Smart Light Strip' ,'2021-09-29', 630773 )
,(4    ,'Smart Cat Feeder'  ,'2021-09-02', 693545 )
,(4    ,'Smart Bed'         ,'2021-09-13', 170249 )
go
--查询
with T as (select *
          ,row_number()  over(partition by user_id order by created_at)  as rnk
          ,dateadd(day, 7, created_at) as eddate
          from users )
select distinct a.user_id
from T a
left join T b
on a.user_id = b.user_id and b.rnk <> a.rnk
where a.created_at between b.created_at and b.eddate

21. 计算每个销售人员的影响力

编写一个 SQL 查询用来报告每个销售人员的客户所支付的价格总和。如果销售人员没有任何客户，则总值应该为 0 。
以 任意顺序 返回结果表。

--建表
if object_id('Salesperson','u') is not null drop table Salesperson
go
create table Salesperson (
 salesperson_id  int
,name            varchar(20)
)
go
insert into Salesperson
values
 ( 1         ,'Alice' )
,( 2         ,'Bob'   )
,( 3         ,'Jerry' )
go
if object_id('Customer','u') is not null drop table Customer
go
create table Customer(
 customer_id     int
,salesperson_id  int
)
go
insert into Customer
values
(1    , 1     )
,(2    , 1     )
,(3    , 2     )
go
if object_id( 'Sales','u') is not null drop table Sales
go
create table Sales(
  sale_id      int
, customer_id  int
, price        int
)
go
insert into Sales
values
 ( 1   ,2   , 892  )
,( 2   ,1   , 354  )
,( 3   ,3   , 988  )
,( 4   ,3   , 856  )
go
--查询
select a.salesperson_id,name,isnull(total,0) as total
from Salesperson a
left join (select b.salesperson_id,sum(price ) as total
from Sales   a
left join Customer  b
on a.customer_id = b.customer_id
GROUP BY B.salesperson_id) b
on a.salesperson_id = b.salesperson_id

22. 坚定的友谊

如果 x 和 y 为朋友且他们至少有三个共同的朋友 ，那么 x 和 y 之间的友谊就是坚定的。
写一个 SQL 查询来找到所有的坚定的友谊。
注意，结果表不应该包含重复，并且 user1_id < user2_id。
以任何顺序返回结果表。

--建表  如果 x  和 y 为朋友且他们至少有三个共同的朋友 ，那么 x 和 y 之间的友谊就是坚定的。
if object_id('Friendship','u') is not null drop table Friendship
go
create table Friendship (
  user1_id     int
, user2_id     int
)
go
insert into Friendship
values
 (1  ,2  )
,(1  ,3  )
,(2  ,3  )
,(1  ,4  )
,(2  ,4  )
,(1  ,5  )
,(2  ,5  )
,(1  ,7  )
,(3  ,7  )
,(1  ,6  )
,(3  ,6  )
,(2  ,6  )
go
--查询
with f as(
    select user1_id,user2_id from Friendship
    union all
    select user2_id,user1_id from Friendship
),
t as (
    select a.user1_id a1, a.user2_id a2, b.user1_id b1, b.user2_id b2
    from f a left join f b on a.user2_id = b.user1_id
),
data as (
    select a1,a2,count(user2_id) common_friend
    from t left join f c on t.a1 = c.user1_id and t.b2 = c.user2_id
    group by a1,a2 having count(user2_id) >= 3
)
select a1 user1_id, a2 user2_id, common_friend
from data
where a1 < a2;

23. 苹果和桔子

编写解决方案报告每一天 苹果 和 桔子 销售的数目的差异.
返回的结果表, 按照格式为 (‘YYYY-MM-DD’) 的 sale_date 排序.

--建表
if object_id('Sales','u') is not null drop table Sales
go
create table Sales (
 sale_date    date
,fruit        varchar(20)
,sold_num      int
)
go
insert into Sales
values
   (  '2020-05-01' ,'apples'     ,10   )
  ,(  '2020-05-01' ,'oranges'    ,8    )
  ,(  '2020-05-02' ,'apples'     ,15   )
  ,(  '2020-05-02' ,'oranges'    ,15   )
  ,(  '2020-05-03' ,'apples'     ,20   )
  ,(  '2020-05-03' ,'oranges'    ,0    )
  ,(  '2020-05-04' ,'apples'     ,15   )
  ,(  '2020-05-04' ,'oranges'    ,16   )
  go
--查询
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

24. 小众书籍

编写解决方案，筛选出过去一年中订单总量 少于 10 本 的 书籍，并且 不考虑 上架距今销售 不满一个月 的书籍 。假设今天是 2019-06-23 。
返回结果表 无顺序要求 。

--建表
if object_id('Books','u') is not null drop table Books
go
create table Books(
    book_id         int
 ,name            varchar(20)
 ,available_from  date
)
go
insert into Books
values
 (1     ,'"Kalila And Demna"','2010-01-01')
 ,(2     ,'"28 Letters"'      ,'2012-05-12')
 ,(3     ,'"The Hobbit"'      ,'2019-06-10')
 ,(4     ,'"13 Reasons Why"'  ,'2019-06-01')
 ,(5     ,'"The Hunger Games"','2008-09-21')
go
if object_id('Orders','u') is not null drop table Orders
go
create table Orders(
 order_id        int
, book_id        int
, quantity      int
, dispatch_date   date
)
go
insert into Orders
values
 ( 1  ,1   ,2    ,'2018-07-26')
,( 2  ,1   ,1    ,'2018-11-05')
,( 3  ,3   ,8    ,'2019-06-11')
,( 4  ,4   ,6    ,'2019-06-05')
,( 5  ,4   ,5    ,'2019-06-20')
,( 6  ,5   ,9    ,'2009-02-02')
,( 7  ,5   ,8    ,'2010-04-13')
go
--查询 编写解决方案，筛选出过去一年中订单总量 少于 10 本 的 书籍，并且 不考虑 上架距今销售 不满一个月 的书籍 。假设今天是 2019-06-23 。
select  a.book_id,a.name
from books a
left join  Orders  b
on a.book_id = b.book_id and b.dispatch_date >= '2018-06-23'
where datediff(month ,available_from ,'2019-06-23') > 1
group by a.book_id,a.name
having isnull(sum(b.quantity),0) < 10

25. 矩形面积

编写解决方案，报告由表中任意两点可以形成的所有 边与坐标轴平行 且 面积不为零 的矩形。
结果表中的每一行包含三列 (p1, p2, area) 如下:
p1 和 p2 是矩形两个对角的 id
矩形的面积由列 area 表示
返回结果表请按照面积 area 大小 降序排列；如果面积相同的话, 则按照 p1 升序排序；若仍相同，则按 p2 升序排列。

在这里插入代码片

26. 最后一个能进入巴士的人

有一队乘客在等着上巴士。然而，巴士有1000 千克 的重量限制，所以其中一部分乘客可能无法上巴士。
编写解决方案找出 最后一个 上巴士且不超过重量限制的乘客，并报告 person_name 。题目测试用例确保顺位第一的人可以上巴士且不会超重。

--建表
if object_id('Queue','u') is not null drop table Queue
 go
create table Queue(
  person_id    int
, person_name  varchar(20)
, weight       int
, turn         int
)
go
insert into Queue
values
(5   ,'Alice'      ,250  ,1  )
,(4   ,'Bob'        ,175  ,5  )
,(3   ,'Alex'       ,350  ,2  )
,(6   ,'John Cena'  ,400  ,3  )
,(1   ,'Winston'    ,500  ,6  )
,(2   ,'Marie'      ,200  ,4  )
go
--查询
--select* from Queue
select top 1 person_name from (
select * ,sum(weight) over(order by turn ) as sumweight
from Queue )a
where sumweight <= 1000
order by turn desc

27. 滚动平均步数

编写一个解决方案，计算出每个用户的 3-day 滚动平均步数 。
计算 n-day 滚动平均值 的计算方式如下：
对于每一天，如果有可用数据的情况下，我们会计算以该天为结束的 n 天连续步数的平均值，否则，对于该天来说，n 天滚动平均步数是未定义的。
输出 user_id 、 steps_date 和滚动平均值。并将滚动平均值四舍五入到 两位小数。
返回结果表以user_id 和 steps_date 升序 排序。

--建表
if object_id('Steps','u') is not null drop table Steps
go
create table Steps(
  user_id      int
, steps_count  int
, steps_date   date
)
go
insert into Steps
values
 ( 1   ,687   ,'2021-09-02')
,( 1   ,395   ,'2021-09-04')
,( 1   ,499   ,'2021-09-05')
,( 1   ,712   ,'2021-09-06')
,( 1   ,576   ,'2021-09-07')
,( 2   ,153   ,'2021-09-06')
,( 2   ,171   ,'2021-09-07')
,( 2   ,530   ,'2021-09-08')
,( 3   ,945   ,'2021-09-04')
,( 3   ,120   ,'2021-09-07')
,( 3   ,557   ,'2021-09-08')
,( 3   ,840   ,'2021-09-09')
,( 3   ,627   ,'2021-09-10')
,( 5   ,382   ,'2021-09-05')
,( 6   ,480   ,'2021-09-01')
,( 6   ,191   ,'2021-09-02')
,( 6   ,303   ,'2021-09-05')
go
--查询
with temp as (
      SELECT user_id,steps_date, LAG(steps_date,2) OVER(PARTITION BY user_id ORDER BY steps_date) AS lag_date,
    cast(AVG(steps_count) OVER(PARTITION BY user_id ORDER BY steps_date ROWS 2 PRECEDING) as decimal(19,2)) AS rolling_average ,
    CASE WHEN abs(DATEDIFF(day,steps_date, LAG(steps_date,2) OVER(PARTITION BY user_id ORDER BY steps_date)))  = 2 THEN 1 ELSE 0 END AS di
    FROM Steps )
SELECT user_id, steps_date, rolling_average
FROM temp
WHERE di = 1
ORDER BY user_id, steps_date;

28. 开除员工

在公司里，每个员工每个月必须工作一定的小时数。员工在工作段中工作。员工工作的小时数可以通过员工在所有工作段中工作的分钟数的总和来计算。每个工作段的分钟数是向上取整的。
例如，如果员工在一个时间段中工作了 51 分 2 秒，我们就认为它是 52 分钟。
编写解决方案来报告将被开除的员工的 id。换句话说，报告没有工作所需时间的员工的 id。
以 任意顺序 返回结果表。

--建表
if object_id('Employees','u') is not null drop table Employees
go
create table Employees (
  employee_id   int
, needed_hours  int
)
go
insert into Employees
values
 ( 1    ,20   )
,( 2    ,12   )
,( 3    ,2    )
go
if object_id('Logs','u') is not null drop table Logs
go
create table Logs(
  employee_id  int
, in_time      datetime
, out_time     datetime
)
go
insert into Logs
values
 ( 1   ,'2022-10-01 09:00:00','2022-10-01 17:00:00')
,( 1   ,'2022-10-06 09:05:04','2022-10-06 17:09:03')
,( 1   ,'2022-10-12 23:00:00','2022-10-13 03:00:01')
,( 2   ,'2022-10-29 12:00:00','2022-10-29 23:59:58')
go
--查询

select a.employee_id from Employees a
left join (
select employee_id ,sum(ceiling(datediff(second,in_time,out_time)/60)) * 1.0/60 as workhour
from Logs
group by employee_id) b
on a.employee_id = b.employee_id
where a.needed_hours > isnull(b.workhour,0)

29.报告的记录

编写解决方案，统计在被报告为垃圾广告的帖子中，被移除的帖子的每日平均占比，四舍五入到小数点后 2 位。

在这里插入代码片

30. 二级关注者

二级关注者 是指满足以下条件的用户:
关注至少一个用户，
被至少一个用户关注。
编写一个解决方案来报告 二级用户 及其关注者的数量。
返回按 follower 字典序排序 的结果表。

--建表
if object_id('Follow','u') is not null drop table Follow
go
create table Follow (
  followee     varchar(20)
, follower     varchar(20)
)
go
insert into Follow
values
 ( 'Alice'    , 'Bob'     )
,( 'Bob'      , 'Cena'    )
,( 'Bob'      , 'Donald'  )
,( 'Donald'   , 'Edward'  )
go
--查询
with t1 as (select followee,count(*) as num from follow
group by followee
having count(*) >= 1 )
,t2 as (select follower  from follow
group by follower
 having count(*) >= 1   )
select follower,num from t1 a
inner join t2 b
on a.followee = b.follower
order by follower
--方法二
select f1.followee follower, count(distinct f1.follower) num
from follow f1
      join follow f2 on f1.followee = f2.follower
group by f1.followee

31. 部门工资最高的员工

查找出每个部门中薪资最高的员工。
按 任意顺序 返回结果表。
查询结果格式如下例所示。

--建表
if object_id('Employee','u') is not null drop table Employee
go
create table Employee(
 id            int
,name          varchar(20)
,salary        int
,departmentId  int
)
go
insert into Employee
values
 ( 1  ,'Joe'   ,70000  , 1   )
,( 2  ,'Jim'   ,90000  , 1   )
,( 3  ,'Henry' ,80000  , 2   )
,( 4  ,'Sam'   ,60000  , 2   )
,( 5  ,'Max'   ,90000  , 1   )
go
if object_id('Department','u') is not null drop table Department
go
create table Department(
  id          int
, name        varchar(20)
)
go
insert into Department
values
(1,'IT')
,(2,'Sales')
go
--查询
--注意：当有部门没有员工薪资信息时，最后查询结果中不需要显示该部门
select a.Department  ,a.name as Employee,a.Salary
from (select a.*,b.name as Department
,rank() over(partition by departmentid order by salary desc ) as rnk
from Employee a
left join department  b
on a.departmentId = b.id
) a
where rnk = 1

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

回答率 是指：同一问题编号中回答次数占显示次数的比率。
编写一个解决方案以报告 回答率 最高的问题。如果有多个问题具有相同的最大 回答率 ，返回 question_id 最小的那个。
查询结果如下例所示。

--建表
if object_id('SurveyLog','u') is not null drop table SurveyLog
go
create table SurveyLog(

  id           int
, action       varchar(20)
, question_id  int
, answer_id    int
, q_num        int
, timestamp    int
)
go
insert into SurveyLog
values
(5 , 'show'   , 285   ,null    ,1 , 123  )
,(5 , 'answer' , 285   ,124124  ,1 , 124  )
,(5 , 'show'   , 369   ,null    ,2 , 125  )
,(5 , 'skip'   , 369   ,null    ,2 , 126  )
go
--查询
--方法1
select question_id as survey_log
from (select *,row_number() over(order by 回答率  desc ,question_id asc ) as rnk
from (
select question_id
     ,sum(case when action = 'answer' then 1 else 0 end )* 1.0 /count(*)
as 回答率
from SurveyLog
group by question_id) a ) a
where rnk = 1
--方法2 ：
select
top 1 question_id as survey_log
from SurveyLog
group by question_id
order by sum(case when action = 'answer' then 1.00 else 0.00 end ) / sum(case when action = 'show' then 1 else 0 end) desc ,question_id

33. 统计各专业学生人数

编写解决方案，为 Department 表中的所有部门(甚至是没有当前学生的部门)报告各自的部门名称和每个部门的学生人数。
按 student_number 降序 返回结果表。如果是平局，则按 dept_name 的 字母顺序 排序。
结果格式如下所示。

--建表
if object_id('Student','u') is not null drop table Student
go
create table Student(
   student_id    int
, student_name  varchar(20)
, gender        varchar(20)
, dept_id       int
)
insert into Student
values
 (1    ,'Jack','M',1   )
,(2    ,'Jane','F',1   )
,(3    ,'Mark','M',2   )
go
if object_id('Department','u') is not null
drop table Department
go
create table Department(
 dept_id     int
,dept_name   varchar(20)
)
go
insert into Department
values
 (1  ,'Engineering' )
,(2  ,'Science'     )
,(3  ,'Law'         )
go
--查询
select a.dept_name,isnull(count(b.student_id),0) as student_number
from department a
left join Student b
on a.dept_id = b.dept_id
group by a.dept_name
order by isnull(count(b.student_id),0) desc ,dept_name asc

34. 平面上的最近距离

p1(x1, y1) 和 p2(x2, y2) 这两点之间的距离是 sqrt((x2 - x1)2 + (y2 - y1)2) 。
编写解决方案，报告 Point2D 表中任意两点之间的最短距离。保留 2 位小数 。
返回结果格式如下例所示。

--建表
if object_id('Point2D','u') is not null drop table Point2D
go
create table Point2D(
  x            int
, y            int
)
go
insert into Point2D
values
 ( -1 , -1 )
,( 0  , 0  )
,( -1 , -2 )
go
--查询
select cast(min(sqrt((b.x-a.x)*(b.x-a.x) +(b.y-a.y)*(b.y-a.y))) as decimal(19,2)) as shortest
from Point2D a
cross join point2d b
where not (a.x = b.x and a.y = b.y )

35. 换座位

编写解决方案来交换每两个连续的学生的座位号。如果学生的数量是奇数，则最后一个学生的id不交换。
按 id 升序 返回结果表。
查询结果格式如下所示。

--建表
if object_id('Seat','u') is not null drop table Seat
go
create table Seat (
  id          int
, student     varchar(20)
)
go
insert into Seat
values
 ( 1  ,'Abbot'   )
,( 2  ,'Doris'   )
,( 3  ,'Emerson' )
,( 4  ,'Green'   )
,( 5  ,'Jeames'  )
go
--查询
--交换每两个连续学生座位号，如果是奇数最后一个学生的ID 不交换
--方法1
select
     case  when (select count(*) from Seat) % 2 = 1 and
            a.id = (select count(*) from Seat)  then a.id
            when  a.id %2 = 0 then a.id -1
            else a.id+1
      end as id  ,student
from Seat a
order by   case  when (select count(*) from Seat) % 2 = 1 and
            a.id = (select count(*) from Seat)  then a.id
            when  a.id %2 = 0 then a.id -1
            else a.id+1
      end
--方法2
select rank() over(order by newid) as id ,student
from(select case when id % 2 = 0 then id - 1 else id + 1 end as newid,student from Seat
) as  t

36. 项目员工 ③

编写解决方案，报告在每一个项目中 经验最丰富 的雇员是谁。如果出现经验年数相同的情况，请报告所有具有最大经验年数的员工。
返回结果表 无顺序要求 。
结果格式如下示例所示。

--建表
if object_id('Project','u') is not null drop table Project
go
create table Project(
 project_id   int
,employee_id  int
)
go
insert into Project
values
 ( 1    ,1     )
,( 1    ,2     )
,( 1    ,3     )
,( 2    ,1     )
,( 2    ,4     )
go
if object_id('Employee','u') is not null drop table Employee
go
create table Employee (
  employee_id      int
,name              varchar(20)
,experience_years  int
)
go
insert into Employee
values
 ( 1  ,'Khaled' ,3  )
,( 2  ,'Ali'    ,2  )
,( 3  ,'John'   ,3  )
,( 4  ,'Doe'    ,2  )
go
--查询
select project_id  ,employee_id
from (select a.*,rank() over(partition by project_id order by b.experience_years desc )  as rnk
        from Project a
        left join  Employee  b
        on a.employee_id = b.employee_id )a
where rnk = 1

37. 每日新用户统计

编写解决方案，找出从今天起最多 90 天内，每个日期该日期首次登录的用户数。假设今天是 2019-06-30 。
以 任意顺序 返回结果表。
结果格式如下所示。

--建表
if object_id('Traffic ','u') is not null drop table Traffic
go
create table Traffic (
   user_id       int
, activity       varchar(20)
, activity_date  date
)
go
insert into Traffic
values
( 1   ,'login'    ,'2019-05-01')
,( 1   ,'homepage' ,'2019-05-01')
,( 1   ,'logout'   ,'2019-05-01')
,( 2   ,'login'    ,'2019-06-21')
,( 2   ,'logout'   ,'2019-06-21')
,( 3   ,'login'    ,'2019-01-01')
,( 3   ,'jobs'     ,'2019-01-01')
,( 3   ,'logout'   ,'2019-01-01')
,( 4   ,'login'    ,'2019-06-21')
,( 4   ,'groups'   ,'2019-06-21')
,( 4   ,'logout'   ,'2019-06-21')
,( 5   ,'login'    ,'2019-03-01')
,( 5   ,'logout'   ,'2019-03-01')
,( 5   ,'login'    ,'2019-06-21')
,( 5   ,'logout'   ,'2019-06-21')
go
--查询
select login_date ,count(*) as user_count
from (
         select user_id, min(activity_date) as login_date
         from traffic
         where activity = 'login'
         group by user_id
         having min(activity_date) >= (select dateadd(day, -90, '2019-06-30'))
     ) a
group by login_date

38. 每位学生的最高成绩

编写解决方案，找出每位学生获得的最高成绩和它所对应的科目，若科目成绩并列，取 course_id 最小的一门。查询结果需按 student_id 增序进行排序。
以 任意顺序 返回结果表。
查询结果格式如下所示。

--建表
if object_id('Enrollments','u') is not null drop table Enrollments
go
create table Enrollments(
 student_id   int
,course_id    int
, grade       int
)
go
insert into Enrollments
values
 ( 2   ,2    ,95 )
,( 2   ,3    ,95 )
,( 1   ,1    ,90 )
,( 1   ,2    ,99 )
,( 3   ,1    ,80 )
,( 3   ,2    ,75 )
,( 3   ,3    ,82 )
go
--查询
select student_id , course_id , grade
from (select *,
       rank() over(partition by student_id  order by grade  desc ,course_id ) as rnk
from Enrollments ) a
where rnk = 1

39. 查询活跃业务

平均活动 是指有特定 event_type 的具有该事件的所有公司的 occurences 的均值。
活跃业务 是指具有 多个 event_type 的业务，它们的 occurences 严格大于 该事件的平均活动次数。
写一个解决方案，找到所有 活跃业务。
以 任意顺序 返回结果表。
结果格式如下所示。

--建表
if object_id( 'Events','u') is not null drop table Events
go
create table Events(
   business_id    int
, event_type    varchar(20)
, occurences     int
)
go
insert into Events
values
 (1  ,'reviews'    ,7   )
,(3  ,'reviews'    ,3   )
,(1  ,'ads'        ,11  )
,(2  ,'ads'        ,7   )
,(3  ,'ads'        ,6   )
,(1  ,'page views' ,3   )
,(2  ,'page views' ,12  )
go
--查询
select business_id
from (
select *
,avg(occurences) over(partition by event_type ) as avg_occurences
from Events ) a
where occurences > avg_occurences
group by business_id
having count(distinct event_type) >= 2

40. 查询球队积分

你希望在所有比赛之后计算所有球队的比分。积分奖励方式如下:

• 如果球队赢了比赛(即比对手进更多的球)，就得 3 分。
• 如果双方打成平手(即，与对方得分相同)，则得 1 分。
• 如果球队输掉了比赛(例如，比对手少进球)，就 不得分 。
  编写解决方案，以找出每个队的 team_id，team_name 和 num_points。
  返回的结果根据 num_points 降序排序，如果有两队积分相同，那么这两队按 team_id 升序排序。
  返回结果格式如下。
  

--建表
if object_id('Teams','u') is not null drop table Teams
go
create table Teams(
  team_id       int
, team_name     varchar(20)
)
go
insert into Teams
values
 ( 10     ,'Leetcode FC'  )
,( 20     ,'NewYork FC'   )
,( 30     ,'Atlanta FC'   )
,( 40     ,'Chicago FC'   )
,( 50     ,'Toronto FC'   )
go
if object_id('Matches','u') is not null drop table Matches
go
create table Matches(
  match_id     int
, host_team    int
, guest_team   int
, host_goals   int
, guest_goals  int
)
go
insert into Matches
values
 ( 1   ,10   ,20    , 3    , 0  )
,( 2   ,30   ,10    , 2    , 2  )
,( 3   ,10   ,50    , 5    , 1  )
,( 4   ,20   ,30    , 1    , 0  )
,( 5   ,50   ,30    , 1    , 0  )
go
--查询
with T as (
select *
,case when host_goals > guest_goals  then 3
when host_goals = guest_goals  then 1
else 0 end host_point
,case when host_goals < guest_goals  then 3
when host_goals = guest_goals  then 1
else 0 end guest_point
from Matches )

, T2 AS (
    select host_team as team_id    ,host_point as points from T
            union all
            select guest_team ,guest_point from T
)
select a.team_id  ,a.team_name,isnull(sum(points) ,0) as num_points
from Teams a
left join T2 B
on a.team_id = b.team_id
group by a.team_id  ,a.team_name
order by isnull(sum(points) ,0)  desc ,team_id

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

编写解决方案，找出所有直接或间接向公司 CEO 汇报工作的职工的 employee_id 。
由于公司规模较小，经理之间的间接关系 不超过 3 个经理 。
可以以 任何顺序 返回无重复项的结果。
返回结果示例如下。

--建表
if object_id('Employees','u') is not null drop table Employees
go
create table Employees(
  employee_id    int
, employee_name  varchar(20)
, manager_id     int
)
go
insert into Employees
values
 (1     ,'Boss'         , 1     )
,(3     ,'Alice'        , 3     )
,(2     ,'Bob'          , 1     )
,(4     ,'Daniel'       , 2     )
,(7     ,'Luis'         , 4     )
,(8     ,'Jhon'         , 3     )
,(9     ,'Angela'       , 8     )
,(77    ,'Robert'       , 1     )
go
--查询
select employee_id from (
select a.*
,b.manager_id  as L1BOSS,c.manager_id  as L2BOSS
from Employees  a
left join Employees  b
on a.manager_id  = b.employee_id
left join Employees  c
on b.manager_id  = c.employee_id ) a
where (manager_id = 1 or L1BOSS = 1 OR L2BOSS  = 1 )
and employee_id <> 1

42. 找到连续区间的开始和结束数字

编写解决方案，得到 Logs 表中的连续区间的开始数字和结束数字。
返回结果表按照 start_id 排序。
结果格式如下面的例子。

--建表
if object_id('Logs','u') is not null drop table  Logs
go
create table Logs(
    log_id        int
)
go
insert into Logs
values
 (1   )
,(2   )
,(3   )
,(7   )
,(8   )
,(10  )
go
--查询

select min(log_id) as start_id   ,max(log_id) as end_id
from (select *
,log_id - row_number() over(order by log_id) as diff
from logs) a
group by diff
order by min(log_id)

43. 不同性别每日分数总计

编写解决方案统计每种性别在每一天的总分。
返回按 gender 和 day 对查询结果 升序排序 的结果。
查询结果格式的示例如下。

--建表
if object_id('Scores','u') is not null drop table Scores
go
create table Scores(
 player_name   varchar(20)
, gender        varchar(20)
,day            date
, score_points   int
)
go
insert into Scores
values
  ('Aron'        ,'F' ,     '2020-01-01' ,17   )
 ,('Alice'       ,'F' ,     '2020-01-07' ,23   )
 ,('Bajrang'     ,'M' ,     '2020-01-07' ,7    )
 ,('Khali'       ,'M' ,     '2019-12-25' ,11   )
 ,('Slaman'      ,'M' ,     '2019-12-30' ,13   )
 ,('Joe'         ,'M' ,     '2019-12-31' ,3    )
 ,('Jose'        ,'M' ,     '2019-12-18' ,2    )
 ,('Priya'       ,'F' ,     '2019-12-31' ,23   )
 ,('Priyanka'    ,'F' ,     '2019-12-30' ,17   )
 --查询
select gender,day, sum(score_points) over(partition by gender order by day) as total
from scores

44. 活动参与者

找出那些既没有最多，也没有最少参与者的活动的名字。
Activities 表中的任意活动都有在 Friends 中参与过。
可以以 任何顺序 返回结果。
下面是返回结果格式的例子。

--建表
if object_id('Friends','u') is not null drop table  Friends
go
create table Friends (
   id            int
, name          varchar(20)
 ,activity       varchar(20)
)
go
insert into Friends
values

 ( 1    , 'Jonathan D.' , 'Eating'        )
,( 2    , 'Jade W.'     , 'Singing'       )
,( 3    , 'Victor J.'   , 'Singing'       )
,( 4    , 'Elvis Q.'    , 'Eating'        )
,( 5    , 'Daniel A.'   , 'Eating'        )
,( 6    , 'Bob B.'      , 'Horse Riding'  )
go
if object_id('Activities','u') is not null drop table Activities
go
create table Activities(
 id             int
 ,name          varchar(20)
)
go
insert into Activities
values
 (1   ,'Eating'       )
,(2   ,'Singing'      )
,(3   ,'Horse Riding' )
go
--查询
select activity from (
select  a.name  as activity
, rank() over(order by count(b.name) ) as R1
, rank() over(order by count(b.name)  desc)  as R2
from Activities  a
left join Friends  b
on a.name = b.activity
group by a.name ) a
where R1 <> 1 and R2 <> 1

45. 顾客的可信联系人数量

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

• customer_name：与发票相关的顾客名称。
• price：发票的价格。
• contacts_cnt：该顾客的联系人数量
• trusted_contacts_cnt：可信联系人的数量：既是该顾客的联系人又是商店顾客的联系人数量（即：可信联系人的电子邮件存在于 Customers 表中）。
  返回结果按照 invoice_id 排序。
  结果的格式如下例所示。
  

--建表
if object_id('Customers','u') is not null drop table Customers
go
create table Customers(
  customer_id    int
, customer_name  varchar(20)
, email          varchar(20)
)
go
insert into Customers
values
 ( 1      ,'Alice'        ,'alice@leetcode.com' )
,( 2      ,'Bob'          ,'bob@leetcode.com'   )
,( 13     ,'John'         ,'john@leetcode.com'  )
,( 6      ,'Alex'         ,'alex@leetcode.com'  )
go
if object_id('Contacts','u') is not null drop table Contacts
go
create table Contacts(
      user_id       int
, contact_name  varchar(20)
, contact_email varchar(20)
)
go
insert into Contacts
values
 ( 1   ,'Bob'      ,'bob@leetcode.com'   )
,( 1   ,'John'     ,'john@leetcode.com'  )
,( 1   ,'Jal'      ,'jal@leetcode.com'   )
,( 2   ,'Omar'     ,'omar@leetcode.com'  )
,( 2   ,'Meir'     ,'meir@leetcode.com'  )
,( 6   ,'Alice'    ,'alice@leetcode.com' )
go
if object_id('Invoices','u') is not null drop table Invoices
go
create table Invoices(

  invoice_id   int
, price        int
, user_id      int
)
go
insert into Invoices
values
 ( 77   ,100 ,1   )
,( 88   ,200 ,1   )
,( 99   ,300 ,2   )
,( 66   ,400 ,2   )
,( 55   ,500 ,13  )
,( 44   ,60  ,6   )
go
--查询
select a.invoice_id,b.customer_name,a.price
,isnull(c.contacts_cnt,0) as contacts_cnt
,isnull(d.trusted_contacts_cnt ,0) as trusted_contacts_cnt
from  Invoices a
left join Customers  b
on a.user_id  = b.customer_id
left join (select user_id,count(*) as contacts_cnt  from Contacts
            group by user_id ) c
on a.user_id = c.user_id
left join (select user_id,count(*) as trusted_contacts_cnt
             from Contacts
             where contact_email   in (select email from Customers)
                group by user_id  ) d
on a.user_id = d.user_id
order by a.invoice_id

46. 购买了产品A 和产品B 却没有购买产品C 的顾客

请你编写解决方案，报告购买了产品 “A”，“B” 但没有购买产品 “C” 的客户的 customer_id 和 customer_name，因为我们想推荐他们购买这样的产品。
返回按 customer_id 排序 的结果表。
返回结果格式如下所示。

--建表
if object_id('Customers','u') is not null drop table Customers
go
create table Customers (
 customer_id         int
,customer_name       varchar(20)
)
go
insert into Customers
values
 (1 ,'Daniel'    )
,(2 ,'Diana'     )
,(3 ,'Elizabeth' )
,(4 ,'Jhon'      )
go
if object_id('Orders','u') is not null drop table  Orders
go
create table Orders(
      order_id      int
, customer_id   int
, product_name  varchar(20)
)
go
insert into Orders
values
 (10   ,  1  ,  'A')
,(20   ,  1  ,  'B')
,(30   ,  1  ,  'D')
,(40   ,  1  ,  'C')
,(50   ,  2  ,  'A')
,(60   ,  3  ,  'A')
,(70   ,  3  ,  'B')
,(80   ,  3  ,  'D')
,(90   ,  4  ,  'C')
go
--查询
--需要考虑到比如顾客3 买了产品A 两次，但是没有买B 也没有买C 对的情况
select distinct customer_id,customer_name
from (
select a.*
,isnull(sum(case when Product_name ='A' OR  Product_name ='B' THEN 1 ELSE 0 END ) over(partition by a.customer_id),0) as S1
,isnull(sum(case when Product_name = 'c' then 1 else 0 end ) over(partition by a.customer_id) ,0) AS S2
,b.customer_name
from (select distinct Product_Name, customer_id from orders ) a
left join Customers  b
on a.customer_id = b.customer_id ) a
where S1 = 2 AND S2 <>1

--方法2
SELECT a.customer_id as customer_id, customer_name
FROM Customers a
INNER JOIN (SELECT customer_id
              FROM Orders
              GROUP BY customer_id
              HAVING sum(case when product_name='A' then 1 else 0 end) > 0
                and sum(case when product_name='B' then 1 else 0 end) > 0
                and sum(case when product_name='C' then 1 else 0 end) = 0  ) b
ON a.customer_id = b.customer_id
ORDER BY a.customer_id

47. 计算布尔表达式的值

计算表 Expressions 中的布尔表达式。
返回的结果表 无顺序要求 。
结果格式如下例所示。

--建表
if object_id('Variables','u') is not null drop table Variables
go
create table Variables(
 name           varchar(20)
,value          int
)
go
insert into Variables
values
( 'x',    66   )
,( 'y',    77    )
go
if object_id('Expressions','u') is not null drop table Expressions
go
create table Expressions(
 left_operand   varchar(20)
,operator       varchar(20)
,right_operand  varchar(20)
)
go
insert into Expressions
values
 ( 'x',  '>','y')
,( 'x',  '<','y'  )
,( 'x',  '=','y' )
,( 'y',  '>','x'  )
,( 'y',  '<','x')
,( 'x',  '=','x' )
go
--查询

select left_operand ,operator ,right_operand
,case when a.operator  = '<' and   b.Value < c.Value  then 'true'
when  a.operator  = '>' and   b.Value > c.Value  then 'true'
when  a.operator  = '=' and   b.Value = c.Value  then 'true'
else 'false' end as value
 from expressions   a
left join Variables b
on a.left_operand = b.name
left join Variables c
on a.right_operand  = c.name

48. 可以放心投资的国家

一家电信公司想要投资新的国家。该公司想要投资的国家是: 该国的平均通话时长要严格地大于全球平均通话时长。
写一个解决方案, 找到所有该公司可以投资的国家。
返回的结果表 无顺序要求。
结果格式如下例所示。

--建表
if object_id('Person','u') is not null drop table Person
go
create table Person(
  id              int
, name            varchar(20)
, phone_number    varchar(20)
)
go
insert into Person
values
( 3 ,'Jonathan' ,'051-1234567')
,( 12,'Elvis'    ,'051-7654321')
,( 1 ,'Moncef'   ,'212-1234567')
,( 2 ,'Maroua'   ,'212-6523651')
,( 7 ,'Meir'     ,'972-1234567')
,( 9 ,'Rachel'   ,'972-0011100')
go
if object_id('Country','u') is not null drop table Country
go
create table Country(
  name           varchar(20)
, country_code   varchar(20)
)
go
insert into Country
values
 ( 'Peru'     ,'051'  )
,( 'Israel'   ,'972'  )
,( 'Morocco'  ,'212'  )
,( 'Germany'  ,'049'  )
,( 'Ethiopia' ,'251'  )
go
if object_id('Calls','u') is not null drop table Calls
go
create table Calls(
 caller_id    int
,callee_id    int
,duration     int
)
go
insert into Calls
values
 ( 1    , 9    ,33   )
,( 2    , 9    ,4    )
,( 1    , 2    ,59   )
,( 3    , 12   ,102  )
,( 3    , 12   ,330  )
,( 12   , 3    ,5    )
,( 7    , 9    ,13   )
,( 7    , 1    ,3    )
,( 9    , 7    ,1    )
,( 1    , 7    ,7    )
go
--查询
with T AS (
SELECT caller_id  , duration  from Calls
 union all
SELECT callee_id  , duration  from Calls
)
select distinct country   from (
select c.country_code, c.name as country
,avg(duration) over(partition by c.name) as C_AVG
,avg(duration) over() as a_AVG
from T a
left join Person b
on a.caller_id = b.id
left join Country  c
on left(b.phone_number,3)= c.country_code )  a
where C_AVG > a_avg

49. 最近的三笔订单

写一个解决方案，找到每个用户的最近三笔订单。如果用户的订单少于 3 笔，则返回他的全部订单。
返回的结果按照 customer_name 升序 排列。如果有相同的排名，则按照 customer_id 升序 排列。如果排名还有相同，则按照 order_date 降序 排列。
结果格式如下例所示：

--建表
if object_id('Customers','u') is not null drop table Customers
go
create table Customers(
 customer_id    int
,name           varchar(20)
)
go
insert into Customers
values
( 1     ,'Winston'   )
,( 2     ,'Jonathan'  )
,( 3     ,'Annabelle' )
,( 4     ,'Marwan'    )
,( 5     ,'Khaled'    )
go
if object_id('Orders','u') is not null drop table Orders
go
create table Orders(
   order_id      int
, order_date    date
, customer_id    int
, cost           int
)
go
insert into Orders
values
 ( 1    ,'2020-07-31', 1    ,30   )
,( 2    ,'2020-07-30', 2    ,40   )
,( 3    ,'2020-07-31', 3    ,70   )
,( 4    ,'2020-07-29', 4    ,100  )
,( 5    ,'2020-06-10', 1    ,1010 )
,( 6    ,'2020-08-01', 2    ,102  )
,( 7    ,'2020-08-01', 3    ,111  )
,( 8    ,'2020-08-03', 1    ,99   )
,( 9    ,'2020-08-07', 2    ,32   )
,( 10   ,'2020-07-15', 1    ,2    )
go
--查询
select  customer_name ,customer_id , order_id , order_date
from (
select a.* ,b.name as customer_name
,rank() over(partition by a.customer_id order by order_date desc ) as rnk
from Orders a
left join Customers b
on a.customer_id = b.customer_id ) a
where rnk<=3
order by customer_name ,customer_id asc,order_date desc

50. 每件商品的最新订单

写一个解决方案, 找到每件商品的最新订单(可能有多个).
返回的结果以 product_name 升序排列, 如果有排序相同, 再以 product_id 升序排列. 如果还有排序相同, 再以 order_id 升序排列.
查询结果格式如下例所示。

if object_id('Customers','u') is not null drop table Customers
go
create table Customers(
  customer_id    int
, name           varchar(20)
)
go
insert into Customers
values
 ( 1    ,'Winston'   )
,( 2    ,'Jonathan'  )
,( 3    ,'Annabelle' )
,( 4    ,'Marwan'    )
,( 5    ,'Khaled'    )
go
if object_id('Orders','u') is not null drop table Orders
go
create table Orders (
   order_id       int
, order_date     date
, customer_id    int
, product_id     int
)
go
insert into Orders
values

 ( 1    ,'2020-07-31',1   ,1   )
,( 2    ,'2020-07-30',2   ,2   )
,( 3    ,'2020-08-29',3   ,3   )
,( 4    ,'2020-07-29',4   ,1   )
,( 5    ,'2020-06-10',1   ,2   )
,( 6    ,'2020-08-01',2   ,1   )
,( 7    ,'2020-08-01',3   ,1   )
,( 8    ,'2020-08-03',1   ,2   )
,( 9    ,'2020-08-07',2   ,3   )
,( 10   ,'2020-07-15',1   ,2   )
go
if object_id('Products','u') is not null drop table Products
go
create table Products(
  product_id     int
, product_name   varchar(20)
, price          int
)
go
insert into Products
values


( 1   ,'keyboard'    ,120 )
,( 2   ,'mouse'       ,80  )
,( 3   ,'screen'      ,600 )
,( 4   ,'hard disk'   ,450 )
go
--查询
select product_name , product_id , order_id , order_date
from (
select a.*,b.product_name
,rank() over(partition by a.product_id  order by order_date desc ) as rnk
from orders a
left join Products b
on a.product_id = b.product_id) a
where rnk =1
order by product_name asc ,product_id asc ,order_id asc

51. 银行账户概要

力扣银行 (LCB) 帮助程序员们完成虚拟支付。我们的银行在表 Transaction 中记录每条交易信息，我们要查询每个用户的当前余额，并检查他们是否已透支（当前额度小于 0）。

编写解决方案报告：

• user_id 用户 ID
• user_name 用户名
• credit 完成交易后的余额
• credit_limit_breached 检查是否透支 （“Yes” 或 “No”）
  以任意顺序返回结果表。

结果格式见如下所示。

--建表
if object_id('Users','u') is not null drop table Users
go
create table Users(
 user_id     int
, user_name    varchar( 20)
, credit      int
)
go
insert into Users
values

 (1    ,'Moustafa'    , 100     )
,(2    ,'Jonathan'    , 200     )
,(3    ,'Winston'     , 10000   )
,(4    ,'Luis'        , 800     )
go
if object_id('Transactions','u') is not null drop table Transactions
go
create table Transactions(

 trans_id      int
,paid_by       int
,paid_to       int
,amount        int
,transacted_on date
)
go
insert into Transactions
values
 ( 1   , 1    ,3  ,400 ,'2020-08-01')
,( 2   , 3    ,2  ,500 ,'2020-08-02')
,( 3   , 2    ,1  ,200 ,'2020-08-03')
go
--查询
with T AS (
select paid_by  as user_id  ,-1*amount as amount
from Transactions
union all
select paid_to,amount
from Transactions )

select a.user_id , a.user_name
     ,a.credit + isnull(SUM(AMOUNT) ,0) AS credit
,case when a.credit + isnull(SUM(AMOUNT) ,0) < 0 then 'Yes'
else 'No' end as credit_limit_breached
FROM Users a
left join t b
on a.user_id = b.user_id
group by a.user_id, a.user_name  ,a.credit

52. 每位顾客最经常订购的商品

写一个解决方案，找到每一个顾客最经常订购的商品。
结果表单应该有每一位至少下过一次单的顾客 customer_id , 他最经常订购的商品的 product_id 和 product_name。
返回结果 没有顺序要求。
查询结果格式如下例所示。

--建表
if object_id('Customers','u') is not null drop table Customers
go
create table Customers(
customer_id    int
,name         varchar(20)
)
go
insert into Customers
values
( 1  ,'Alice' )
,( 2  ,'Bob'   )
,( 3  ,'Tom'   )
,( 4  ,'Jerry' )
,( 5  ,'John'  )
go
if object_id('Orders','u') is not null drop table Orders
go
create table Orders(
   order_id      int
, order_date     date
, customer_id    int
, product_id     int
)
go
insert into Orders
values
(1     ,'2020-07-31', 1   ,1    )
,(2     ,'2020-07-30', 2   ,2    )
,(3     ,'2020-08-29', 3   ,3    )
,(4     ,'2020-07-29', 4   ,1    )
,(5     ,'2020-06-10', 1   ,2    )
,(6     ,'2020-08-01', 2   ,1    )
,(7     ,'2020-08-01', 3   ,3    )
,(8     ,'2020-08-03', 1   ,2    )
,(9     ,'2020-08-07', 2   ,3    )
,(10    ,'2020-07-15', 1   ,2    )
go
if object_id('Products','u') is not null drop table Products
go
create table Products(
  product_id     int
, product_name   varchar(20)
, price         int
)
go
insert into Products
values
( 1  ,'keyboard'    ,120  )
,( 2  ,'mouse'       ,80   )
,( 3  ,'screen'      ,600  )
,( 4  ,'hard disk'   ,450  )
GO
--查询
select customer_id , product_id ,product_name from(
select * ,rank() over(partition by customer_id order by cnt desc ) as rnk
from (
select a.customer_id ,a.product_id,b.Product_Name,count(*) as cnt
from orders a
left join Products b
on a.Product_id = b.Product_id
group by a.customer_id ,a.product_id,b.Product_Name) a )a
where rnk = 1

53. 找到遗失的ID

编写一个解决方案, 找到所有遗失的顾客 id。遗失的顾客 id 是指那些不在 Customers 表中, 值却处于 1 和表中 最大 customer_id 之间的 id.
注意: 最大的 customer_id 值不会超过 100.
返回结果按 ids 升序 排列
查询结果格式如下例所示。

--建表
if object_id('Customers','u') is not null drop table Customers
go
create table Customers(
 customer_id    int
, customer_name  varchar(20)
)
go
insert into Customers
values
 ( 1    ,'Alice'     )
,( 4    ,'Bob'       )
,( 5    ,'Charlie'   )
go
--查询
--方法1 
select number as ids
from master..spt_values a
where type = 'P'
and  NUMBER BETWEEN 1
    AND (SELECT max(customer_id ) from customers )
and  not exists (select * from customers  b
where a.number = b.customer_id)

--方法2
with temp(customer_id) as (
    select 1 as customer_id
    union all
    select customer_id+1 from temp where customer_id<100
)
select customer_id as ids
from temp 
where customer_id not in(select customer_id from customers) and customer_id<=(select max(customer_id) from customers )

54. 两人之间的通话次数

编写解决方案，统计每一对用户 (person1, person2) 之间的通话次数和通话总时长，其中 person1 < person2 。
以 任意顺序 返回结果表。
返回结果格式如下示例所示。

--建表
if object_id('Calls','u') is not null drop table Calls
go
create table Calls(
  from_id     int
,to_id        int
, duration     int
)
go
insert into Calls
values
 ( 1,2  ,59    )
,( 2,1  ,11    )
,( 1,3  ,20    )
,( 3,4  ,100   )
,( 3,4  ,200   )
,( 3,4  ,200   )
,( 4,3  ,499   )
go
--查询
with T as (
select from_id  as person1 ,to_id as person2,duration from calls
where from_id < to_id
    union all
select to_id,from_id ,duration from calls
    where from_id > to_id
)
select person1  ,person2,count(*) as call_count
,sum(duration) as total_duration 
from T
GROUP BY person1  ,person2

55. 访问日期之间最大的空档期

假设今天的日期是 ‘2021-1-1’ 。
编写解决方案，对于每个 user_id ，求出每次访问及其下一个访问（若该次访问是最后一次，则为今天）之间最大的空档期天数 window 。
返回结果表，按用户编号 user_id 排序。
结果格式如下示例所示：

--建表
if object_id('UserVisits','u') is not null drop table UserVisits
go
create table
    UserVisits(
 user_id      int
, visit_date  date
)
go
insert into UserVisits
values
 ( 1 ,'2020-11-28')
,( 1 ,'2020-10-20')
,( 1 ,'2020-12-3' )
,( 2 ,'2020-10-5' )
,( 2 ,'2020-12-9' )
,( 3 ,'2020-11-11')
go
--查询
with t as (select *
            ,rank() over(partition by user_id order by visit_date) as rnk
            from (select * from UserVisits
                  union all
                  select distinct user_id,'2021-1-1'
                  from UserVisits ) a )
select A.user_id
     ,max(DATEDIFF(DAY,b.VISIT_DATE,a.VISIT_DATE)) AS biggest_window
from T  A
LEFT JOIN T B
ON A.USER_ID = B.USER_ID AND A.RNK = B.RNK+ 1
group by a.user_id
order by a.user_id
--方法2

SELECT user_id, MAX(diff) AS biggest_window
FROM (
SELECT user_id, visit_date
     , DATEDIFF(day,visit_date, LEAD(visit_date, 1, '2021-01-01') OVER(partition by user_id order by visit_date)) as diff
FROM UserVisits) t1
GROUP BY user_id
ORDER BY user_id;

56. 应该被禁止的Leetflex 账户

编写解决方案，查找那些应该被禁止的Leetflex帐户编号 account_id 。 如果某个帐户在某一时刻从两个不同的网络地址登录了，则这个帐户应该被禁止。
可以以 任何顺序 返回结果。
查询结果格式如下例所示。

--建表
if object_id('LogInfo','u') is not null drop table LogInfo
go
create table LogInfo(
account_id   int
, ip_address   int
, login       datetime
, logout       datetime
)
go
insert into LogInfo
values
 ( 1  ,1   ,'2021-02-01 09:00:00', '2021-02-01 09:30:00')
,( 1  ,2   ,'2021-02-01 08:00:00', '2021-02-01 11:30:00')
,( 2  ,6   ,'2021-02-01 20:30:00', '2021-02-01 22:00:00')
,( 2  ,7   ,'2021-02-02 20:30:00', '2021-02-02 22:00:00')
,( 3  ,9   ,'2021-02-01 16:00:00', '2021-02-01 16:59:59')
,( 3  ,13  ,'2021-02-01 17:00:00', '2021-02-01 17:59:59')
,( 4  ,10  ,'2021-02-01 16:00:00', '2021-02-01 17:00:00')
,( 4  ,11  ,'2021-02-01 17:00:00', '2021-02-01 17:59:59')
go
--查询
select distinct a.account_id
from LogInfo a
join LogInfo b
on a.account_id = b.account_id
and a.ip_address != b.ip_address
and a.login between b.login and b.logout

57. 苹果和橘子的个数

编写 SQL 语句，查询每个大箱子中苹果和橘子的个数。如果大箱子中包含小盒子，还应当包含小盒子中苹果和橘子的个数。
以任意顺序返回结果表。
查询结果的格式如下示例所示。

--建表
IF OBJECT_ID('Boxes','U') IS NOT NULL DROP TABLE Boxes
GO
CREATE TABLE Boxes(
 box_id        int
, chest_id      int
, apple_count   int
, orange_count  int
)
go
insert into Boxes
values
( 2  ,null ,6    ,15  )
,( 18 ,14   ,4    ,15  )
,( 19 ,3    ,8    ,4   )
,( 12 ,2    ,19   ,20  )
,( 20 ,6    ,12   ,9   )
,( 8  ,6    ,9    ,9   )
,( 3  ,14   ,16   ,7   )
go
if object_id('Chests','u') is not null drop table Chests
go
create table Chests(
 chest_id      int
,apple_count   int
,orange_count  int
)
go
insert into Chests
values
( 6     ,5     , 6       )
,( 14    ,20    , 10      )
,( 2     ,8     , 8       )
,( 3     ,19    , 4       )
,( 16    ,19    , 19      )
go
--查询
select sum(a.apple_count + isnull(b.apple_count ,0)) as apple_count
,sum(a.orange_count + isnull(b.orange_count,0)) as orange_count
from boxes a
left join chests  b
on a.chest_id = b.chest_id

58. 大满贯数量

编写解决方案，找出每一个球员赢得大满贯比赛的次数。结果不包含没有赢得比赛的球员的ID 。
结果集 无顺序要求 。
结果的格式，如下所示。

--建表
if object_id('Players','u') is not null drop table Players
go
create table Players(
  player_id       int
, player_name     varchar(20)
)
go
insert into Players
values
 ( 1         , 'Nadal'      )
,( 2         , 'Federer'    )
,( 3         , 'Novak'      )
go
if object_id('Championships','u') is not null drop table Championships
go
create table Championships(
      year        int
, Wimbledon   int
, Fr_open     int
, US_open     int
, Au_open     int
)
go
insert into Championships
values
 ( 2018  , 1     , 1    , 1   , 1   )
,( 2019  , 1     , 1    , 2   , 2   )
,( 2020  , 2     , 1    , 2   , 2   )
 go
 --查询
 select a.player_id,b.player_name
 ,count(*) as grand_slams_count
 from (
          select year, match, player_id
          from Championships
                   unpivot (player_id for Match in (Wimbledon ,Fr_open ,US_open , Au_open)) p
      ) a
inner join Players  b
on a.player_id = b.player_id
group by a.player_id ,b.player_name

59. 寻找面试候选人

编写 SQL 语句来返回 所有面试候选人 的姓名 name 和邮件 mail 。当用户满足以下两个要求中的 任意一条 ，其成为 面试候选人 :

• 该用户在 连续三场及更多 比赛中赢得 任意 奖牌。
• 该用户在 三场及更多不同的 比赛中赢得 金牌（这些比赛可以不是连续的）
  可以以 任何顺序 返回结果。

查询结果格式如下例所示。

--建表
if object_id('Contests','u') is not null drop table Contests
go
create table Contests(
 contest_id   int
, gold_medal    int
, silver_medal  int
, bronze_medal  int
)
go
insert into Contests
values
 ( 190  , 1  ,5   ,2   )
,( 191  , 2  ,3   ,5   )
,( 192  , 5  ,2   ,3   )
,( 193  , 1  ,3   ,5   )
,( 194  , 4  ,5   ,2   )
,( 195  , 4  ,2   ,1   )
,( 196  , 1  ,5   ,2   )
go
if object_id('Users','u') is not null drop table Users
go
create table Users(
 user_id      int
,mail         varchar(50)
,name         varchar(20)
)
go
insert into Users
values
 (  1   ,'sarah@leetcode.com' ,'Sarah')
,(  2   ,'bob@leetcode.com'   ,'Bob'   )
,(  3   ,'alice@leetcode.com' ,'Alice' )
,(  4   ,'hercy@leetcode.com' ,'Hercy' )
,(  5   ,'quarz@leetcode.com' ,'Quarz' )
go
--查询
--select * from users
--select * from contests

with t as (select a.* ,b.name,b.mail
            from ( select contest_id, medaltype, user_id
                    from Contests
                    unpivot (user_id for medaltype in ( gold_medal , silver_medal , bronze_medal)) p )a
            left join users b
            on a.user_id = b.user_id )
--连续三场获得任意奖牌
select distinct name,mail
from (select name,mail ,diff
      from (select *,contest_id-row_number() over(partition by user_id order by contest_id) as diff
            from T) a
        group by name,mail,diff
        having count(*) >= 3 ) a
union
--三场金牌(可以不连续)
select name,mail
from T
group by name,mail
having sum(case when medaltype = 'gold_medal' then 1  else 0 end ) >= 3

60. 每天的最大交易

编写一个解决方案，报告每天交易金额 amount 最大 的交易 ID 。如果一天中有多个这样的交易，返回这些交易的 ID 。
返回结果根据 transaction_id 升序排列。
返回格式如下示例所示：

--建表
if object_id('Transactions','u') is not null drop table Transactions
go
create table Transactions(
 transaction_id  int
,day             datetime
,amount          int
)
go
insert into Transactions
values
 ( 8  ,'2021-4-3 15:57:28' ,57  )
,( 9  ,'2021-4-28 08:47:25',21  )
,( 1  ,'2021-4-29 13:28:30',58  )
,( 5  ,'2021-4-28 16:39:59',40  )
,( 6  ,'2021-4-29 23:39:28',58  )
go
--查询
select transaction_id
from (select *
    ,rank() over(partition by datepart(day,day) order by amount desc ) as rnk
    from Transactions ) a
where rnk = 1
order by transaction_id

61. 联赛信息统计

写一段SQL，用来报告联赛信息. 统计数据应使用已进行的比赛来构建，其中 获胜 球队获得 三分 ，而失败球队获得 零分 。如果 打平 ，两支球队都得 一分 。
result 表的每行应包含以下信息:

• team_name - Teams 表中的队伍名字
• matches_played - 主场与客场球队进行的比赛次数.
• points - 球队获得的总分数.
• goal_for - 球队在所有比赛中获取的总进球数
• goal_against - 球队在所有比赛中，他的对手球队的所有进球数
• goal_diff - goal_for - goal_against.
  按 points 降序 返回结果表。 如果两队或多队得分相同，则按 goal_diff 降序 排列。 如果仍然存在平局，则以 team_name 按字典顺序 排列它们。

查询的结果格式如下例所示。

--建表
if object_id('Teams','u') is not null drop table Teams
go
create table Teams(
 team_id       int
, team_name     varchar(20)
)
go
insert into Teams
values
 ( 1    , 'Ajax'     )
,( 4    , 'Dortmund' )
,( 6    , 'Arsenal'  )
go
if object_id('Matches','u') is not null drop table Matches
go
create table Matches(
home_team_id    int
,away_team_id    int
,home_team_goals  int
,away_team_goals  int
)
go
insert into Matches
values
 (  1      ,4    ,0     ,1    )
,(  1      ,6    ,3     ,3    )
,(  4      ,1    ,5     ,2    )
,(  6      ,1    ,0     ,0    )
go
--查询

with t as (select *
             ,case when home_team_goals = away_team_goals  then 1
                   when home_team_goals > away_team_goals  then 3
                    else 0 end as home_team_points
            ,case when  home_team_goals = away_team_goals then 1
                    when home_team_goals < away_team_goals then 3
            else 0 end as away_team_points
            from Matches a )

select * from (
SELECT B.team_name,COUNT(*) AS matches_played
,SUM(home_team_points) AS points
,SUM(home_team_goals) AS goal_for
,SUM(away_team_goals) AS goal_against
,SUM(home_team_goals) - SUM(away_team_goals) as goal_diff
FROM (select  home_team_id , away_team_id  ,home_team_goals  , away_team_goals
        ,home_team_points  ,away_team_points
        from T a
        union all
        select away_team_id  ,home_team_id , away_team_goals, home_team_goals
        ,away_team_points,home_team_points
        from T  ) A
LEFT JOIN Teams B
ON A.home_team_id = B.TEAM_ID
GROUP BY B.team_name ) a
order by points  desc,goal_diff  desc ,team_name  asc

62. 可疑银行账户

如果一个账户在 连续两个及以上 月份的 总收入 超过最大收入（max_income），那么认为这个账户 可疑。 账户当月 总收入 是当月存入资金总数（即 transactions 表中 type 字段的 ‘Creditor’）。
编写一个解决方案，报告所有的 可疑 账户。
以 任意顺序 返回结果表
返回结果格式如下示例所示。

--建表
if object_id('Accounts','u') is not null drop table Accounts
go
create table Accounts(
  account_id      int
, max_income      int
)
go
insert into Accounts
values
(3,21000    )
,( 4,10400    )
go
if object_id('Transactions','u') is not null drop table Transactions
go
create table Transactions (
 transaction_id  int
, account_id      int
, type            varchar(20)
, amount          int
, day             datetime
)
go
insert into Transactions
values
 ( 2     ,3    ,'Creditor' ,107100 ,'2021-06-02 11:38:14')
,( 4     ,4    ,'Creditor' ,10400  ,'2021-06-20 12:39:18')
,( 11    ,4    ,'Debtor'   ,58800  ,'2021-07-23 12:41:55')
,( 1     ,4    ,'Creditor' ,49300  ,'2021-05-03 16:11:04')
,( 15    ,3    ,'Debtor'   ,75500  ,'2021-05-23 14:40:20')
,( 10    ,3    ,'Creditor' ,102100 ,'2021-06-15 10:37:16')
,( 14    ,4    ,'Creditor' ,56300  ,'2021-07-21 12:12:25')
,( 19    ,4    ,'Debtor'   ,101100 ,'2021-05-09 15:21:49')
,( 8     ,3    ,'Creditor' ,64900  ,'2021-07-26 15:09:56')
,( 7     ,3    ,'Creditor' ,90900  ,'2021-06-14 11:23:07')
go
--查询
SELECT distinct account_id
FROM (select a.account_id,format(day,'yyMM') AS Month,sum(amount ) as amount
        ,b.max_income
        ,format(day,'yyMM')  - RANK() OVER(PARTITION BY A.ACCOUNT_ID ORDER BY format(day,'yyMM') ) AS diff
        from Transactions A
        left join Accounts b
        on a.account_id = b.account_id
        where a.type = 'Creditor'
        group by a.account_id ,format(day,'yyMM'),b.max_income
        having sum(amount ) > b.max_income) A
group by account_id,diff
having count(*) >= 2
order by a.account_id

63. 最大数量高于平均水平的订单

您正在运行一个电子商务网站，该网站正在寻找不平衡的订单。不平衡订单的订单最大数量严格大于每个订单（包括订单本身）的平均数量。
订单的平均数量计算为（订单中所有产品的总数量）/（订单中不同产品的数量）。订单的最大数量是订单中任何单个产品的最高数量。
编写SQL查询以查找所有不平衡订单的订单id。
按任意顺序返回结果表。
查询结果格式如下例所示。

--建表
if object_id('OrdersDetails','u') is not null drop table OrdersDetails
go
create table OrdersDetails (
  order_id     int
, product_id   int
, quantity     int
)
go
insert into OrdersDetails
values
 ( 1   , 1    , 12   )
,( 1   , 2    , 10   )
,( 1   , 3    , 15   )
,( 2   , 1    , 8    )
,( 2   , 4    , 4    )
,( 2   , 5    , 6    )
,( 3   , 3    , 5    )
,( 3   , 4    , 18   )
,( 4   , 5    , 2    )
,( 4   , 6    , 8    )
,( 5   , 7    , 9    )
,( 5   , 8    , 9    )
,( 3   , 9    , 20   )
,( 2   , 9    , 4    )
go
--查询
SELECT order_id
FROM OrdersDetails
GROUP BY order_id
HAVING max(quantity) >  ALL (SELECT AVG(quantity) FROM OrdersDetails GROUP BY order_id)

64. 将工资相同的雇员分组

这家公司想要将 工资相同 的雇员划分到同一个组中。每个组需要满足如下要求：

• 每个组需要由 至少两个 雇员组成。
• 同一个组中的所有雇员的 工资相同。
• 工资相同的所有雇员必须被分到同一个组中。
• 如果某位雇员的工资是独一无二的，那么它 不 被分配到任何一个组中。
• 组ID的设定基于这个组的工资相对于其他组的 工资的排名，即工资 最低 的组满足 team_id = 1 。注意，排名时 不需要考虑 没有组的雇员的工资。
  编写一个解决方案来获取每一个被分配到组中的雇员的 team_id 。
  返回的结果表按照 team_id 升序排列。如果相同，则按照 employee_id 升序排列。
  返回结果格式如下示例所示。

--建表
if object_id('Employees','u') is not null drop table Employees
go
create table Employees(
  employee_id  int
, name         varchar(20)
, salary       int
)
go
insert into Employees
values
 (2    , 'Meir'    ,3000   )
,(3    , 'Michael' ,3000   )
,(7    , 'Addilyn' ,7400   )
,(8    , 'Juan'    ,6100   )
,(9    , 'Kannon'  ,7400   )
go
--查询
select employee_id,name   , salary ,dense_rank() over(order by salary) as team_id
from (
select *
,count(*) over(partition by salary) as cnt
from Employees ) a
where cnt >  1
order by team_id  ,employee_id

65. 查询具有最多共同关注者的所有两两结对组

写出一个查询语句，找到具有最多共同关注者的所有两两结对组。换句话说，如果有两个用户的共同关注者是最大的，我们应该返回所有具有此最大值的两两结对组

结果返回表，每一行应该包含user1_id和 user2_id，其中user1_id < user2_id.
返回结果 不要求顺序 。
查询结果格式如下例：

--建表
if object_id('Relations','u') is not null drop table Relations
go
create table Relations(
     user_id     int
, follower_id  int
)
go
insert into Relations
values
( 1    ,3      )
,( 2    ,3      )
,( 7    ,3      )
,( 1    ,4      )
,( 2    ,4      )
,( 7    ,4      )
,( 1    ,5      )
,( 2    ,6      )
,( 7    ,5      )
go
--查询
select user1_id  ,  user2_id
from (select a.user_id as user1_id ,b.user_id as user2_id
        ,rank() over(order by count(*)desc ) as rnk
        from  Relations  a
        inner join Relations b
        on a.follower_id = b.follower_id
        and a.user_id < b.user_id
        group by a.user_id ,b.user_id ) a
where rnk = 1

66. 找出每所学校的最低分数要求

每年，学校会公布学生申请所需的最低分数要求。学校根据所有学生的考试成绩来决定其最低分数要求。

1. 学校希望确保即使 每 一个满足分数要求的学生都申请该学校，学校也有足够的能力接纳每一个学生。
2. 学校也希望 尽可能多 的学生能申请该学校。
3. 学校 必须 使用在 Exam 表中的 score 来作为最低分数要求。
   编写一个解决方案，报告每所学校的 最低分数要求。如果同时有多个 score 值满足上述要求，则选择其中 最小的一个。如果数据不足以决定 最低分数要求，那么输出 -1。
   返回的结果表可以按 任意顺序 排序。
   结果格式如下例所示：
   

--建表
if object_id('Schools','u') is not null drop table Schools
go
create table Schools(
  school_id    int
,capacity     int
)
go
insert into Schools
values
 (11    ,151 )
,(5     ,48  )
,(9     ,9   )
,(10    ,99  )
go
if object_id('Exam','u') is not null drop table Exam
go
create table Exam(
 score          int
,student_count  int
)
go
insert into Exam
values
 ( 975  ,10   )
,( 966  ,60   )
,( 844  ,76   )
,( 749  ,76   )
,( 744  ,100  )
go
--查询
select  a.school_id
     ,case when min(b.score) is null then -1 else min(b.score) end as score
from schools  a
left join exam b
on b.student_count <= a.capacity
group by a.school_id

67. 统计实验的数量

写一个 SQL 查询语句，以报告在给定三个实验平台中每种实验完成的次数。请注意，每一对（实验平台、实验名称）都应包含在输出中，包括平台上实验次数是零的。
结果可以以任意顺序给出。
查询的结果如下所示：

--建表
if object_id('Experiments','u') is not null drop table Experiments
go
create table Experiments(
  experiment_id    int
, platform         varchar(20)
, experiment_name  varchar(20)
)
go
insert into Experiments
values
 ( 4     ,'IOS'      ,'Programming'  )
,( 13    ,'IOS'      ,'Sports'       )
,( 14    ,'Android'  ,'Reading'      )
,( 8     ,'Web'      ,'Reading'      )
,( 12    ,'Web'      ,'Reading'      )
,( 18    ,'Web'      ,'Programming'  )
go
--查询
with T1 AS(
    select 'Android' platform
    union
    select 'IOS'
    union
    select 'Web'
), T2 AS (
         select 'Reading' as experiment_name
         union
         select 'Sports'
         union
         select 'Programming'
     )
select  a.platform,b.experiment_name
     ,isnull(count(distinct experiment_id ) ,0) as num_experiments
from T1 a
cross join T2 b
left join Experiments  c
on a.platform = c.platform
and b.experiment_name = c.experiment_name
group by  a.platform,b.experiment_name

68. 无流量的账户数

编写SQL查询以报告在 2021 购买订阅但没有任何会话的帐 户数。
查询结果格式如下例所示。

--建表
if object_id('Subscriptions','u') is not null drop table Subscriptions
go
create table Subscriptions(
 account_id  int
,start_date   date
, end_date     date
)
go
insert into Subscriptions
values
 (9    ,'2020-02-18','2021-10-30')
,(3    ,'2021-09-21','2021-11-13')
,(11   ,'2020-02-28','2020-08-18')
,(13   ,'2021-04-20','2021-09-22')
,(4    ,'2020-10-26','2021-05-08')
,(5    ,'2020-09-11','2021-01-17')
go
if object_id('Streams','u') is not null drop table Streams
go
create table Streams(
 session_id   int
,account_id   int
,stream_date  date
)
go
insert into Streams
values
 ( 14    ,9  ,'2020-05-16')
,( 16    ,3  ,'2021-10-27')
,( 18    ,11 ,'2020-04-29')
,( 17    ,13 ,'2021-08-08')
,( 19    ,4  ,'2020-12-31')
,( 13    ,5  ,'2021-01-05')
go
--查询
SELECT COUNT(*) accounts_count
FROM (SELECT DISTINCT account_id
        FROM subscriptions
        WHERE start_date <= '2021-12-31'
         AND end_date >= '2021-01-01'
          AND account_id NOT IN (SELECT account_id
                                  FROM Streams
                                  WHERE stream_date LIKE '2021%')
        ) t

69. 面试中被录取的候选人

编写解决方案，找出 至少有两年 工作经验、且面试分数之和 严格大于 15 的候选人的 ID 。
可以以 任何顺序 返回结果表。
查询结果的格式如下例所示。

--建表
if object_id('Candidates','u') is not null drop table Candidates
go
create table Candidates(
  candidate_id int
, name          varchar(20)
, years_of_exp  int
, interview_id  int
)
go
insert into Candidates
values
 (11   ,'Atticus' ,1    ,101 )
,(9    ,'Ruben'   ,6    ,104 )
,(6    ,'Aliza'   ,10   ,109 )
,(8    ,'Alfredo' ,0    ,107 )
go
if object_id('Rounds','u') is not null drop table Rounds
go
create table Rounds(
  interview_id  int
, round_id    int
, score         int
)
go
insert into Rounds
values
 (  109     ,3   ,4  )
,(  101     ,2   ,8  )
,(  109     ,4   ,1  )
,(  107     ,1   ,3  )
,(  104     ,3   ,6  )
,(  109     ,1   ,4  )
,(  104     ,4   ,7  )
,(  104     ,1   ,2  )
,(  109     ,2   ,1  )
,(  104     ,2   ,7  )
,(  107     ,2   ,3  )
,(  101     ,1   ,8  )
go
--查询
select a.candidate_id
from Candidates a
left join  Rounds  b
on a.interview_id = b.interview_id
where a.years_of_exp >=2
group by candidate_id
having sum(b.score ) > 15

70. 商店中每个成员的级别

一个商店想对其成员进行分类。有三个层次:

• “钻石”: 如果转换率 大于或等于 80.
• “黄金”: 如果转换率 大于或等于 50 且小于 80.
• “白银”: 如果转化率 小于 50.
• “青铜”: 如果该成员从未访问过该商店。
  成员的 转化率 为 (100 * 该会员的购买总数) / 该成员的总访问次数.

编写一个 SQL 来查询每个成员的 id、名称和类别。
以 任意顺序 返回结果表。
查询结果格式如下所示。

--建表
if object_id('Members','u') is not null drop table Members
go
create table Members(
  member_id    int
, name       varchar(20)
)
go
insert into Members
values
 (9    ,'Alice'  )
,(11   ,'Bob'    )
,(3    ,'Winston')
,(8    ,'Hercy'  )
,(1    ,'Narihan')
go
if object_id('Visits','u') is not null drop table Visits
go
create table Visits(
   visit_id    int
, member_id    int
, visit_date   date
)
go
insert into Visits
values
 (22  ,11   ,'2021-10-28')
,(16  ,11   ,'2021-01-12')
,(18  ,9    ,'2021-12-10')
,(19  ,3    ,'2021-10-19')
,(12  ,11   ,'2021-03-01')
,(17  ,8    ,'2021-05-07')
,(21  ,9    ,'2021-05-12')
go
if object_id('Purchases','u') is not null drop table Purchases
go
create table Purchases(
   visit_id       int
, charged_amount int
)
go
insert into Purchases
values
 ( 12   ,2000   )
,( 18   ,9000   )
,( 17   ,7000   )
go
--查询

select a.member_id ,a.name
,case when count(b.visit_id ) = 0 then 'Bronze'
    when count(c.visit_id ) *1.0  /count(b.visit_id ) <0.5 then 'Silver'
    when count(c.visit_id ) *1.0 /count(b.visit_id ) <0.8 then 'Gold'
    else 'Diamond'
end as category
from Members a
left join Visits  b
on a.member_id = b.member_id
left join Purchases c
on b.visit_id = c.visit_id
group  by a.member_id,a.name

71. 账户余额

请写出能够返回用户每次交易完成后的账户余额. 我们约定所有用户在进行交易前的账户余额都为0， 并且保证所有交易行为后的余额不为负数。
返回的结果请依次按照 账户（account_id), 日期( day ) 进行升序排序 .
查询结果的格式请参照以下测试样例.

--建表
if object_id('Transactions','u') is not null drop table Transactions
go
create table Transactions(
  account_id  int
,day         date
,type        varchar(20)
, amount       int
)
go
insert into Transactions
values
 (  1  ,'2021-11-07', 'Deposit'  ,2000 )
,(  1  ,'2021-11-09', 'Withdraw' ,1000 )
,(  1  ,'2021-11-11', 'Deposit'  ,3000 )
,(  2  ,'2021-12-07', 'Deposit'  ,7000 )
,(  2  ,'2021-12-12', 'Withdraw' ,7000 )
go
--查询
select account_id ,day
,sum(amount_chulihou) over(partition by account_id order by day) as balance
from (
select account_id,day
,case when type = 'Withdraw' then -1 * amount
else  amount  end as amount_chulihou
from Transactions ) a
order by account_id,day

72. 为订单类型为0 的客户删除类型为1 的订单

编写SQL查询以根据以下条件报告所有订单：

如果客户至少有一个类型为0的订单，则不要报告该客户的任何类型为1的订单。
否则，报告客户的所有订单。
按任意顺序返回结果表。

查询结果格式如下例所示。

--建表
if object_id('Orders','u') is not null drop table Orders
go
create table Orders(
order_id    int
,customer_id int
, order_type   int
)
go
insert into Orders
values
 (1    ,1   ,0  )
,(2    ,1   ,0  )
,(11   ,2   ,0  )
,(12   ,2   ,1  )
,(21   ,3   ,1  )
,(22   ,3   ,0  )
,(31   ,4   ,1  )
,(32   ,4   ,1  )
go
--查询
--方法1 
with T AS (
select customer_id
,sum(case when order_type = 0 then 1 else 0 end  ) as cnt
from Orders a
group by customer_id )
select * from orders
where customer_id in (select customer_id from T WHERE CNT = 0)
UNION ALL
SELECT * FROM ORDERS
WHERE CUSTOMER_ID not in (select customer_id from T WHERE CNT = 0 )
AND ORDER_TYPE <> '1'
--方法2
select order_id, customer_id, order_type from
(select *,
dense_rank() over (partition by customer_id order by order_type) rk
from Orders) a
where rk=1

73. 最繁忙的机场

编写一个 SQL 来查询 流量最大 的机场的 ID。客流量最大的机场是指从该机场起飞或抵达该机场的航班总数最多的机场。如果有多个机场流量最大，请全部查询出来。
以 任意顺序 返回结果表。
查询结果格式如下所示。

--建表
if object_id('Flights','u') is not null drop table Flights
go
create table Flights(
  departure_airport  int
, arrival_airport    int
, flights_count      int
)
go
insert into Flights
values
 ( 1     ,2        , 4     )
,( 2     ,1        , 5     )
,( 2     ,4        , 5     )
go
--查询
select airport_id
from (select airport_id
        ,rank() over(order by sum(flights_count) desc ) as rnk
        from (
                 select departure_airport as airport_id, flights_count
                 from Flights
                 union all
                 select arrival_airport, flights_count
                 from flights
             ) a
        group by airport_id ) a
where rnk = 1

74. 每辆车的乘客人数 ①

公交车和乘客到达 LeetCode 站。如果一辆公交车在时间 tbus 到站，乘客在时间 tpassenger 到站，其中 tpassenger <= tbus，该乘客之前没有赶上任何公交车，则该乘客将搭乘该公交车。
编写一个 SQL 来查询使用每辆公交车的用户数量。
返回按 bus_id 升序排序 的结果表。
查询结果格式如下所示。

--建表
if object_id('Buses','u') is not null drop table Buses
go
create table Buses(
  bus_id        int
, arrival_time int
)
go
insert into Buses
values
 ( 1  ,2   )
,( 2  ,4   )
,( 3  ,7   )
go
if object_id('Passengers','u') is not null drop table Passengers
go
create table Passengers(
passenger_id  int
,arrival_time  int
)
go
insert into Passengers
values
 (11    , 1 )
,(12    , 5 )
,(13    , 6 )
,(14    , 7 )
go
--查询
select bus_id
,count(passenger_id) -lag(count(passenger_id ) ,1,0)
over(order by a.arrival_time) as passeger_cnt
from buses a
left join Passengers b
on a.arrival_time >= b.arrival_time
group by bus_id ,a.arrival_time
order by bus_id

75. 分别排序两列

编写解决方案，使：
first_col 按照 升序 排列。
second_col 按照 降序 排列。
返回的结果格式如下。

--建表
if object_id('Data','u') is not null drop table Data
go
create table Data(
first_col    int
,second_col  int
)
go
insert into  Data
values
 ( 4  , 2   )
,( 2  , 3   )
,( 3  , 1   )
,( 1  , 4   )
go
--查询
with T as (
select *
,row_number() over(order by first_col) as first_col_rnk
,row_number() over(order by second_col desc ) as sec_col_rnk
from Data)
select a.first_col ,b.second_col
from T a
left join T b
on a.first_col_rnk = b.sec_col_rnk

76. 世界排名的变化

国家队的全球排名是按 降序排列 所有队伍的得分后所得出的排名。如果两支队伍得分相同，我们将按其名称的 字典顺序 排列以打破平衡。
每支国家队的分数应根据其相应的 points_change 进行更新。
编写解决方案来计算在分数更新后，每个队伍的全球排名的变化。
以 任意顺序 返回结果。
查询结果的格式如下例所示：

--建表
if object_id('TeamPoints','u') is not null drop table TeamPoints
go
create table TeamPoints(
 team_id      int
,name         varchar(20)
,points      int
)
go
insert into TeamPoints
values
 (3  ,'Algeria'     ,1431 )
,(1  ,'Senegal'     ,2132 )
,(2  ,'New Zealand' ,1402 )
,(4  ,'Croatia'     ,1817 )
go
if object_id ('PointsChange','u') is not null drop table PointsChange
go
create table PointsChange(
    team_id       int
,points_change  int
)
go
insert into PointsChange
values
 (3 , 399    )
,(2 , 0      )
,(4 , 13     )
,(1 , -22    )
go
--查询
select a.team_id ,a.name
 ,rank() over(order by points desc ,name asc ) -
rank() over(order by a.points + isnull(b.points_change,0) desc,name) as rank_diff
from TeamPoints  a
left join pointschange b
on a.team_id = b.team_id

77. 7天内两次购买的用户

编写解决方案，获取 最多 间隔 7 天进行两次购买的用户的 id。
返回按 user_id 排序的结果表。
结果格式如下所示。

--建表
if object_id('Purchases','u') is not null drop table Purchases
go
create table Purchases(
 purchase_id   int
, user_id        int
, purchase_date  date
)
go
insert into Purchases
values
 ( 4   , 2   ,'2022-03-13')
,( 1   , 5   ,'2022-02-11')
,( 3   , 7   ,'2022-06-19')
,( 6   , 2   ,'2022-03-20')
,( 5   , 7   ,'2022-06-19')
,( 2   , 2   ,'2022-06-08')
go
--查询
select distinct a.user_id
from (select  *
        ,dateadd(day,7,purchase_date ) as sevendaybefore
        from Purchases) a
left join Purchases  b
on b.purchase_date between a.purchase_date and sevendaybefore
and a.user_id  = b.user_id
group by a.user_id ,a.purchase_date,a.sevendaybefore
having  count(distinct b.purchase_id) >= 2
order by a.user_id
--方法2
select distinct a.user_id from Purchases a, Purchases b
where a.purchase_id <> b.purchase_id
and a.user_id = b.user_id
and abs(datediff(day,b.purchase_date,a.purchase_date)) <= 7
order by 1

78. 司机成为乘客的次数

编写解决方案，获取每个司机的 ID 和他们作为乘客的次数。
以 任意顺序 返回结果表。
结果格式如下所示。

--建表
if object_id('Rides','u') is not null drop table Rides
go
create table Rides(
ride_id       int
, driver_id     int
,passenger_id  int
)
go
insert into Rides
values
 ( 1  , 7   ,1  )
,( 2  , 7   ,2  )
,( 3  , 11  ,1  )
,( 4  , 11  ,7  )
,( 5  , 11  ,7  )
,( 6  , 11  ,3  )
go
--查询
select a.driver_id ,isnull(count(distinct ride_id ),0) as cnt
from (select distinct driver_id from rides ) a
left join     Rides b
on a.driver_id = b.passenger_id
and  b.passenger_id in (select distinct driver_id from rides )
group by a.driver_id

79. 连续两年有3个及以上订单的产品

编写解决方案，获取连续两年订购三次或三次以上的所有产品的 id。
以 任意顺序 返回结果表。
结果格式示例如下。

在这里插入代码片

80. 周末任务计数

编写一个解决方案来报告：
在周末 (周六，周日) 提交的任务的数量 weekend_cnt，以及
工作日内提交的任务数 working_cnt。
按 任意顺序 返回结果表。
返回结果格式如以下示例所示。

--建表
if object_id('Tasks','u') is not null drop table Tasks
go
create table Tasks(
     task_id   int
, assignee_id  int
, submit_date  date
)
go
insert into   Tasks
values
 (  1  ,1  ,'2022-06-13')
,(  2  ,6  ,'2022-06-14')
,(  3  ,6  ,'2022-06-15')
,(  4  ,3  ,'2022-06-18')
,(  5  ,5  ,'2022-06-19')
,(  6  ,7  ,'2022-06-19')
go
--查询
select sum(case when weekday in (1,7) then 1 else 0 end) as weekend_cnt
,sum(case when weekday not in (1,7) then 1 else 0 end ) as working_cnt
from(select *,datepart(weekday,submit_date ) as weekday
    from Tasks ) a

81. 按性别排列表格

编写一个解决方案以重新排列 Genders 表，使行按顺序在 ‘female’, ‘other’ 和 ‘male’ 之间交替。同时每种性别按照 user_id 升序进行排序。
按 上述顺序 返回结果表。
返回结果格式如以下示例所示。

--建表
if object_id('Genders','u') is not null drop table Genders
go
create table Genders(
  user_id      int
, gender       varchar(20)
)
go
insert into Genders
values
 ( 4  ,'male'   )
,( 7  ,'female' )
,( 2  ,'other'  )
,( 5  ,'male'   )
,( 3  ,'female' )
,( 8  ,'male'   )
,( 6  ,'other'  )
,( 1  ,'other'  )
,( 9  ,'female' )
go
--查询
select user_id,gender from  (
select *
    ,case when gender = 'female' then 1
when gender = 'other' then 2
else 3 end as Gender_idx
,rank() over(partition by gender order by user_id ) as rnk
from Genders) a
order by rnk,gender_idx

82. 每个城市最高气温的第一天

编写解决方案，找出每个城市中有最高温度记录的日子。如果同一城市多次记录最高气温，则返回其中最早的一天。
返回按 city_id 升序排序 的结果表。
查询结果格式示例如下。

--建表
if object_id('Weather','u') is not null drop table Weather
go
create table Weather(
  city_id     int
, day          date
, degree       int
)
go
insert into Weather
values
 ( 1 ,'2022-01-07',-12 )
,( 1 ,'2022-03-07',5   )
,( 1 ,'2022-07-07',24  )
,( 2 ,'2022-08-07',37  )
,( 2 ,'2022-08-17',37  )
,( 3 ,'2022-02-07',-7  )
,( 3 ,'2022-12-07',-6  )
go
--查询
select city_id,day    ,degree
from(select *
    ,rank() over(partition by city_id order by degree desc ,day asc ) as rnk
    from Weather ) a
where rnk = 1
order by city_id

83. 以百分比计算排名

编写一个解决方案，以百分比的形式报告每个学生在其部门的排名，其中排名的百分比使用以下公式计算:
(student_rank_in_the_department - 1) * 100 / (the_number_of_students_in_the_department - 1)。 percentage 应该 四舍五入到小数点后两位。
student_rank_in_the_department 由 mark 的降序决定，mark 最高的学生是 rank 1。如果两个学生得到相同的分数，他们也会得到相同的排名。
以 任意顺序 返回结果表。
结果格式如下所示。

--建表
if object_id('Students','u') is not null drop table Students
 go
 create table Students(
 student_id     int
,department_id  int
,mark           int
 )
 go
 insert into Students
values
 ( 2   , 2  ,650 )
,( 8   , 2  ,650 )
,( 7   , 1  ,920 )
,( 1   , 1  ,610 )
,( 3   , 1  ,530 )
go
--查询
select student_id , department_id
,case when isnull(cnt- 1,0) = 0 then 0 else round ((rnk- 1) * 100 /(cnt- 1  ) ,2 ) end as percentage
from (
select *
     ,count(student_id ) over(partition by department_id) as cnt
,rank() over(partition by department_id  order by mark desc ) as rnk
from Students ) a

84. 将表中的空值更改为前一个值

编写一个解决方案将 drink 的 null 值替换为前面最近一行不为 null 的 drink。保证表第一行的 drink 不为 null。
返回 与输入顺序相同的 结果表。
查询结果格式示例如下。

--建表
if object_id('CoffeeShop','u') is not null drop table CoffeeShop
go
create table CoffeeShop(
 id         int
, drink       varchar(20)
)
go
insert into CoffeeShop
values
 ( 9  ,'Rum and Coke'    )
,( 6  ,null             )
,( 7  ,null             )
,( 3  ,'St Germain Spritz')
,( 1  ,'Orange Margarita' )
,( 2  ,null             )
go
--查询

with c as(
    select id,drink,row_number() over(order by (select null)) as rn from coffeeshop
),d as(
    select a.id,
    iif(drink is not null,drink, (select top 1 drink from c where rn < a.rn and drink is not null order by rn desc))
    as drink   from c a
)
select * from d 

--方法2
SELECT id,MAX(drink) OVER(PARTITION BY total) AS drink
from (
    SELECT id,drink,rn,SUM(case when drink IS NULL then 0 else 1 end ) OVER(ORDER BY rn) AS total
    from (
       SELECT id,drink,ROW_NUMBER() over(order by (select null)) AS rn
       FROM CoffeeShop
    )t1
)t2
order by rn;

难度：困难

1. 部门工资前三高的所有员工

Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。

Department 表包含公司所有部门的信息。

编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：

解释：
IT部门中，Max获得了最高的工资，Randy和Joe都拿到了第二高的工资，Will的工资排第三。销售部门（Sales）只有两名员工，Henry的工资最高，Sam的工资排第二。

select b.Name as Department,a.Name as Employee,a.Salary
from (select *,dense_rank() over(partition by departmentid order by Salary desc) as rnk from Employee) a 
left join department b 
on a.departmentid = b.Id 
where a.rnk <= 3

2. 行程和用户

表：Trips

表：Users

3. 体育馆的人流量

编写一个 SQL 查询以找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。
返回按 visit_date 升序排列的结果表。

if object_id('stadium','u') is not null drop table stadium

create table stadium(
    id int identity(1,1)
    ,visit_date date
    ,people int
)

insert into stadium(visit_date, people)
values
 ('2017-01-01' , 10      )
,('2017-01-02' , 109     )
,('2017-01-03' , 150     )
,('2017-01-04' , 99      )
,('2017-01-05' , 145     )
,('2017-01-06' , 1455    )
,('2017-01-07' , 199     )
,('2017-01-09' , 188     )

select id,visit_date,people
from (
         select id, visit_date, people, count(*) over (partition by (fz)) as cnt
         from (
                  select *, id - row_number() over (order by id ) as fz
                  from stadium
                  where people >= 100
              ) a
     ) a
where cnt > 3

4. 员工薪水的中位数

写一个SQL查询，找出每个公司的工资中位数。
以 任意顺序 返回结果表。
查询结果格式如下所示。
Employee表：

--方法一：
--注意事项：排序时用row_number,会有排名相同的情况
--中位数的逻辑了解：不论个数是奇数偶数，中位数在总数除以2和总数除以2加1之间
select id ,company,salary
from (
    select *
    ,row_number() over(partition by company order by salary) as rnk
    ,count(*) over(partition by company ) as cnt
    from Employee) a
where  rnk BETWEEN cnt*1.0/2 AND cnt*1.0/2 + 1

5. 同一天的第一个电话和最后一个电话

编写一个 SQL 查询来找出那些ID们在任意一天的第一个电话和最后一个电话都是和同一个人的。这些电话不论是拨打者还是接收者都会被记录。
结果请放在一个任意次序约束的表中。
查询结果格式如下所示：

--MySQL 
with temp as (select * from calls
            union all
            select recipient_id caller_id, caller_id recipient_id, call_time from calls
    ),

temp1 as (select
            *,
            dense_rank() over (partition by date_format(call_time,"%Y-%m-%d"),caller_id order by call_time asc) rk1,
            dense_rank() over (partition by date_format(call_time,"%Y-%m-%d"),caller_id order by call_time desc) rk2
        from temp
    )

select
    distinct caller_id as user_id
from temp1
where rk1 = 1 or rk2 = 1
group by caller_id, date_format(call_time,"%Y-%m-%d")
having count(distinct recipient_id) = 1

5. 查询员工的累计薪水

Employee 表保存了一年内的薪水信息。
请你编写 SQL 语句，对于每个员工，查询他除最近一个月（即最大月）之外，剩下每个月的近三个月的累计薪水（不足三个月也要计算）。
结果请按 Id 升序，然后按 Month 降序显示。

--MySQL
--注意点：剔除最大月要以员工为单位去看，还有就是其他月份算累计只要计算近三个月的
select a.id ,a.month ,sum(b.salary)  as Salary
from Employee a 
left join Employee b 
on a.id = b.id and a.Month >= b.Month  and a.Month < b.Month + 3
where (a.Id, a.Month) NOT IN (SELECT Id, MAX(Month) FROM Employee GROUP BY Id)
group by a.id ,a.month 
order by id asc ,month desc

--MS SQL Server
WITH T AS (
select id,max(month) as month from Employee group by id
)

select a.id ,a.month ,sum(b.salary)  as Salary
from Employee a 
left join Employee b 
on a.id = b.id and a.Month >= b.Month  and a.Month < b.Month + 3
where NOT EXISTS (SELECT * FROM t b  where a.id = b.id and a.month = b.month)
group by a.id ,a.month 
order by id asc ,month desc

6. 给定数字的频率查询中位数

中位数 是将数据样本中半数较高值和半数较低值分隔开的值。
编写一个 SQL 查询，解压 Numbers 表，报告数据库中所有数字的 中位数 。结果四舍五入至 一位小数 。
查询结果如下例所示。

--中位数逻辑：按大小排序后，不论正序降序排序，中位数的排序都会大于总数的一半
select cast (sum(num)*1.0/count(num) as decimal(19,1)) as median
from
(select Num, frequency,
        sum(frequency) over(order by Num asc) as total,
        sum(frequency) over(order by Num desc) as total1
from Numbers
)as a
where total>=(select sum(frequency) from Numbers)/2
and total1>=(select sum(frequency) from Numbers)/2

7. 查询员工的累计薪水

Employee 表保存了一年内的薪水信息。
请你编写 SQL 语句，对于每个员工，查询他除最近一个月（即最大月）之外，剩下每个月的近三个月的累计薪水（不足三个月也要计算）。
结果请按 Id 升序，然后按 Month 降序显示。
示例：
输入：

--建表
if object_id('Employee','u') is not null drop table Employee
go
create table Employee  (
    Id int, Month int, Salary int
)
go
insert into Employee
values
 ( 1  , 1    , 20  )
,( 2  , 1    , 20  )
,( 1  , 2    , 30  )
,( 2  , 2    , 30  )
,( 3  , 2    , 40  )
,( 1  , 3    , 40  )
,( 3  , 3    , 60  )
,( 1  , 4    , 60  )
,( 3  , 4    , 70  )
go
--查询
with t as (
select *
,row_number() over(partition by id  order by month desc) as rnk
from Employee )
select a.id,a.month ,sum(b.salary ) as salary
from T a
left join T b
on a.id = b.id and b.month <= a.month and a.month -3 < b.month
where a.rnk <>1
group by a.id,a.month
order by a.id asc,a.month desc

8. 学生地理信息报告

一所学校有来自亚洲、欧洲和美洲的学生。
写一个查询语句实现对大洲（continent）列的 透视表 操作，使得每个学生按照姓名的字母顺序依次排列在对应的大洲下面。输出的标题应依次为美洲（America）、亚洲（Asia）和欧洲（Europe）。
测试用例的生成使得来自美国的学生人数不少于亚洲或欧洲的学生人数。

--建表
if object_id('student','u') is not null drop table student
go
create table student (
  name         varchar(20)
, continent   varchar(20)
)
go
insert into student
values
 ( 'Jane'   ,'America'   )
,( 'Pascal' ,'Europe'    )
,( 'Xi'     ,'Asia'      )
,( 'Jack'   ,'America'   )
go
--查询
with t as (select * ,row_number() over(partition by continent order by name ) as rnk
from student)
select a.name as America,b.name as Asia,c.name as Europe
from (select distinct rnk from T ) o
left join T a
on a.rnk = o.rnk and a.continent = 'America'
left join T b
on o.rnk = b.rnk and b.continent = 'Asia'
left join T c
on  o.rnk = c.rnk and c.continent = 'Europe'

9. 同一天的第一个和最后一个电话

编写一个 SQL 查询来找出那些ID们在任意一天的第一个电话和最后一个电话都是和同一个人的。这些电话不论是拨打者还是接收者都会被记录。
结果请放在一个任意次序约束的表中。

--建表
if object_id ('Calls','u') is not null drop table Calls
go
create table Calls(
  caller_id    int
, recipient_id  int
, call_time     datetime
)
go
insert into Calls
values
 (8     ,4       ,'2021-08-24 17:46:07')
,(4     ,8       ,'2021-08-24 19:57:13')
,(5     ,1       ,'2021-08-11 05:28:44')
,(8     ,3       ,'2021-08-17 04:04:15')
,(11    ,3       ,'2021-08-17 13:07:00')
,(8     ,11      ,'2021-08-17 22:22:22')
go
--查询
with T as (SELECT caller_id, recipient_id, call_time
            FROM Calls
            UNION ALL
            SELECT recipient_id , caller_id , call_time
            FROM Calls  )

SELECT DISTINCT a.caller_id user_id
FROM (SELECT caller_id, recipient_id, dense_rank() over (PARTITION BY caller_id, cast(call_time as date) order by call_time) AS rk
        FROM T) a
INNER JOIN (SELECT caller_id, recipient_id, dense_rank() over (PARTITION BY caller_id, cast(call_time as date) order by call_time DESC) AS rk
            FROM T) b
ON a.caller_id = b.caller_id AND a.recipient_id = b.recipient_id AND a.rk = 1 AND b.rk = 1

10. 职员招聘人数

一家公司想雇佣新员工。公司的工资预算是 70000 美元。公司的招聘标准是：
雇佣最多的高级员工。
在雇佣最多的高级员工后，使用剩余预算雇佣最多的初级员工。
编写一个SQL查询，查找根据上述标准雇佣的高级员工和初级员工的数量。
按 任意顺序 返回结果表。

--建表
if object_id('Candidates','u') is not null drop table Candidates
go
create table Candidates(
  employee_id  int
, experience   varchar(20)
, salary       int
)
go
insert into Candidates
values
 ( 1     ,'Junior'    ,10000)
,( 9     ,'Junior'    ,10000)
,( 2     ,'Senior'    ,20000)
,( 11    ,'Senior'    ,20000)
,( 13    ,'Senior'    ,50000)
,( 4     ,'Junior'    ,40000)
go
--查询
WITH SeniorTotal AS
(SELECT employee_id, SUM(salary) OVER (ORDER BY salary) AS totalone
FROM Candidates
WHERE experience = 'Senior'),
SeniorNumber AS
(SELECT MAX(totalone) totals
FROM SeniorTotal
WHERE totalone <= 70000),
JuniorTotal  AS
(SELECT employee_id, SUM(salary) OVER (ORDER BY salary) AS totaltwo
FROM Candidates
WHERE experience = 'Junior')
SELECT 'Senior' AS experience, COUNT(DISTINCT employee_id) AS accepted_candidates
FROM SeniorTotal
WHERE totalone <= 70000
UNION ALL
SELECT 'Junior' AS experience, COUNT(DISTINCT employee_id) AS accepted_candidates
FROM JuniorTotal, SeniorNumber
WHERE totaltwo < 70000 - isnull(totals, 0)

11. 职员招聘人数 ②

一家公司想雇佣新员工。公司的工资预算是 7 万美元。公司的招聘标准是：
继续雇佣薪水最低的高级职员，直到你不能再雇佣更多的高级职员。
用剩下的预算雇佣薪水最低的初级职员。
继续以最低的工资雇佣初级职员，直到你不能再雇佣更多的初级职员。
编写一个SQL查询，查找根据上述条件雇用职员的 ID。
按 任意顺序 返回结果表。

--建表
if object_id('Candidates','u') is not null drop table Candidates
go
create table Candidates(
  employee_id  int
, experience   varchar(20)
, salary       int
)
go
insert into Candidates
values
 (1     ,'Junior',10000 )
,(9     ,'Junior',15000 )
,(2     ,'Senior',20000 )
,(11    ,'Senior',16000 )
,(13    ,'Senior',50000 )
,(4     ,'Junior',40000 )
go
--查询
WITH SeniorTotal AS
(SELECT employee_id, SUM(salary) OVER (ORDER BY salary) AS totalone
FROM Candidates
WHERE experience = 'Senior'),
SeniorNumber AS
(SELECT MAX(totalone) totals
FROM SeniorTotal
WHERE totalone <= 70000),
JuniorTotal  AS
(SELECT employee_id, SUM(salary) OVER (ORDER BY salary) AS totaltwo
FROM Candidates
WHERE experience = 'Junior')
SELECT DISTINCT employee_id
FROM SeniorTotal
WHERE totalone <= 70000
UNION ALL
SELECT DISTINCT employee_id
FROM JuniorTotal, SeniorNumber
WHERE totaltwo < 70000 - isnull(totals, 0)

12. 找到每篇文章的主题

Leetcode 从其社交媒体网站上收集了一些帖子，并对每个帖子的主题感兴趣。每个主题可以由一个或多个关键字表示。如果某个主题的关键字存在于一个帖子的内容中 (不区分大小写)，那么这个帖子就有这个主题。
编写一个 SQL 查询，根据以下规则查找每篇文章的主题:
如果帖子没有来自任何主题的关键词，那么它的主题应该是 “Ambiguous!”。
如果该帖子至少有一个主题的关键字，其主题应该是其主题的 id 按升序排列并以逗号 ‘，’ 分隔的字符串。字符串不应该包含重复的 id。
以 任意顺序 返回结果表。

--建表
if object_id('Keywords','u') is not null drop table Keywords
go
create table Keywords (
  topic_id     int
, word         varchar(20)
)
go
insert into Keywords
values
 ( 1     ,'handball' )
,( 1     ,'football' )
,( 3     ,'WAR'      )
,( 2     ,'Vaccine'  )
go
if object_id('Posts','u') is not null drop table Posts
go
create table Posts(
    post_id     int
,content      varchar(100)
)
go
insert into Posts
values
 ( 1    ,'We call it soccer They call it football hahaha'                         )
,( 2    ,'Americans prefer basketball while Europeans love handball and football' )
,( 3    ,'stop the war and play handball'                                         )
,( 4    ,'warning I planted some flowers this morning and then got vaccinated'    )
go
--查询
with t as  (select distinct a.post_id ,a.content,b.topic_id

        from Posts a
        cross join  Keywords b
        where charindex(b.word+' ',a.content,1) <> 0
           or charindex(' ' + b.word,a.content,1) <> 0  )

select post_id
,(select isnull(stuff((select ',' + cast(topic_id as varchar(20))from T where post_id = a.post_id  for xml path('')),1,1,''),'Ambiguous!')) as topic
from Posts a

13. 生成发票

编写一个 SQL 查询来显示价格最高的发票的详细信息。如果两个或多个发票具有相同的价格，则返回 invoice_id 最小的发票的详细信息。
以 任意顺序 返回结果表。

--建表
if object_id('Products','u')
is not null drop table Products
go
create table Products (
    product_id   int
, price        int
)
go
insert into Products
values
( 1         , 100  )
,( 2         , 200  )
go
if object_id('Purchases','u') is not null drop table Purchases
go
create table Purchases(
invoice_id   int
,product_id   int
,quantity     int

)
go
insert  into Purchases
values
 ( 1     ,1    ,2   )
,( 3     ,2    ,1   )
,( 2     ,2    ,3   )
,( 2     ,1    ,4   )
,( 4     ,1    ,10  )
go
--查询
select a.Product_id,a.quantity,sum(a.quantity * p.price) as Price
from Purchases a
left join Products p
on a.product_id = p.product_id
where invoice_id = (select invoice_id 
                    from (select *,row_number() over(order by sumprice desc,invoice_id asc ) as rnk
                            from (select a.invoice_id,sum(a.quantity  * P.price ) as SumPrice
                                    from Purchases  a
                                    left join Products P
                                    on a.product_id = P.product_id
                                    group by a.invoice_id) a ) a
                    where rnk = 1 )
group by a.Product_id, a.quantity

14. 受欢迎度百分比

编写一条 SQL 查询，找出 Meta/Facebook 平台上每个用户的受欢迎度的百分比。受欢迎度百分比定义为用户拥有的朋友总数除以平台上的总用户数，然后乘以 100，并 四舍五入保留 2 位小数 。
返回按照 user1 升序 排序的结果表。

--建表
if object_id('Friends','u') is not null drop table Friends
go
create table Friends (
    user1     int
, user2       int
)
go
insert into Friends
values
( 2    , 1   )
,( 1    , 3   )
,( 4    , 1   )
,( 1    , 5   )
,( 1    , 6   )
,( 2    , 6   )
,( 7    , 2   )
,( 8    , 3   )
,( 3    , 9   )
go
--查询
with T as(
    select * from friends
    union
    select user2,user1 from friends
)
select user1
     ,CAST(round(count(distinct user2) * 1.0 / (select count(distinct user1) from T) * 100,2) AS DECIMAL(19,2)) as percentage_popularity
from T
GROUP BY USER1
order by user1

15. 购买量严格增加的客户

编写一个 SQL 查询，报告 总购买量 每年严格增加的客户 id。
客户在一年内的 总购买量 是该年订单价格的总和。如果某一年客户没有下任何订单，我们认为总购买量为 0。
对于每个客户，要考虑的第一个年是他们 第一次下单 的年份。
对于每个客户，要考虑的最后一年是他们 最后一次下单 的年份。
以 任意顺序 返回结果表。

-- 建表
if object_id ('Orders','u') is not null drop table Orders
go
create table Orders(
order_id      int
, customer_id   int
, order_date    date
, price         int
)
go
insert into Orders
values
(1    , 1      ,'2019-07-01',1100 )
,(2    , 1      ,'2019-11-01',1200 )
,(3    , 1      ,'2020-05-26',3000 )
,(4    , 1      ,'2021-08-31',3100 )
,(5    , 1      ,'2022-12-07',4700 )
,(6    , 2      ,'2015-01-01',700  )
,(7    , 2      ,'2017-11-07',1000 )
,(8    , 3      ,'2017-01-01',900  )
,(9    , 3      ,'2018-11-07',900  )
go
--查询
WITH T AS (
select customer_id,datepart(year,order_date) as year,sum(price ) as Price_SUM
,row_number() over(partition by customer_id order by datepart(year,order_date)  ) as rnk
from Orders
group by customer_id ,datepart(year,order_date)
 )

select distinct a.customer_id
from T a
left join T b
on a.customer_id = b.customer_id and a.rnk = b.rnk+1
where A.customer_id in (
SELECT a.customer_id  FROM T a
group by customer_id
having  max(year) - min(year ) + 1 = count(distinct year) )
and (a.rnk <> 1 and a.price_sum-b.Price_SUM  > 0 )

16. 合并在同一个大厅重叠的活动

编写一个 SQL 查询来合并在 同一个大厅举行 的所有重叠活动。如果两个活动 至少有一天 相同，那么它们就是重叠的。
以任意顺序返回结果表。

if object_id('HallEvents','u') is not null drop table HallEvents
go
create table HallEvents(
   hall_id     int
, start_day    date
, end_day      date
)
go
insert into HallEvents
values
 ( 2    ,'2022-12-09','2023-01-02')
,( 5    ,'2022-12-01','2022-12-02')
,( 2    ,'2023-01-12','2023-01-14')
,( 3    ,'2022-12-01','2022-12-19')
,( 4    ,'2022-12-29','2022-12-31')
,( 5    ,'2022-12-22','2023-01-18')
,( 5    ,'2022-12-04','2022-12-18')
,( 2    ,'2022-12-29','2023-01-24')
,( 2    ,'2022-12-20','2023-01-09')
,( 6    ,'2022-12-08','2022-12-31')
,( 1    ,'2022-12-14','2022-12-22')
,( 5    ,'2023-01-15','2023-01-27')
,( 1    ,'2022-12-07','2023-01-03')
,( 1    ,'2022-12-30','2023-01-27')
,( 5    ,'2022-12-01','2023-01-22')
,( 3    ,'2022-12-29','2022-12-30')
,( 3    ,'2023-01-04','2023-01-05')
,( 4    ,'2022-12-12','2022-12-17')
,(9      ,'2023-01-26','2023-01-30')
,(9      ,'2023-01-17','2023-01-28')
,(9      ,'2022-12-14','2023-01-16')
,(9      ,'2022-12-15','2023-01-02')
,(9      ,'2022-12-10','2023-01-12')
,(9      ,'2022-12-27','2023-01-06')
go
SELECT hall_id,
       MIN(start_day) AS start_day,
       MAX(end_day) AS end_day
FROM (
    SELECT *,
           SUM(range_start) OVER (PARTITION BY hall_id ORDER BY start_day) AS range_grp
    FROM (
        SELECT *,
               CASE WHEN start_day <= LAG(max_end_day_so_far) OVER (PARTITION BY hall_id ORDER BY start_day) THEN 0
                    ELSE 1 END AS range_start
        FROM (
            SELECT hall_id,
                   start_day,
                   end_day,
                   MAX(end_day) OVER (PARTITION BY hall_id ORDER BY start_day) AS max_end_day_so_far
            FROM HallEvents
        ) t
    ) t1
) t2
GROUP BY hall_id, range_grp;

17. 表的动态旋转

实现 PivotProducts 过程来重新组织 Products 表，以便每行都有一个商品的 id 及其在每个商店中的价格。如果商品不在商店出售，价格应为 null。表的列应该包含每个商店，并且它们应该按 字典顺序排序。
过程应该在重新组织表之后返回它。
以 任意顺序 返回结果表。

18. 兴趣相同的朋友

请写一段SQL查询获取到兴趣相同的朋友。用户 x 和 用户 y 是兴趣相同的朋友，需满足下述条件：
用户 x 和 y 是朋友，并且
用户 x and y 在同一天内听过相同的歌曲，且数量大于等于三首.
结果表 无需排序 。注意：返回的结果需要和源数据表的呈现方式相同 （例如， 需满足 user1_id < user2_id）。

--建表
if object_id('Listens','U') is not null drop table
go
create table Listens (
user_id     int
, song_id    int
, day        date
)
go
insert into Listens
values
 ( 1      ,10    ,'2021-03-15')
,( 1      ,11    ,'2021-03-15')
,( 1      ,12    ,'2021-03-15')
,( 2      ,10    ,'2021-03-15')
,( 2      ,11    ,'2021-03-15')
,( 2      ,12    ,'2021-03-15')
,( 3      ,10    ,'2021-03-15')
,( 3      ,11    ,'2021-03-15')
,( 3      ,12    ,'2021-03-15')
,( 4      ,10    ,'2021-03-15')
,( 4      ,11    ,'2021-03-15')
,( 4      ,13    ,'2021-03-15')
,( 5      ,10    ,'2021-03-16')
,( 5      ,11    ,'2021-03-16')
,( 5      ,12    ,'2021-03-16')
go
if object_id('Friendship','U') is not null drop table Friendship
GO
CREATE TABLE Friendship(
    user1_id    int
, user2_id      int
)
go
insert into Friendship
values
 ( 1       , 2    )
,( 2       , 4    )
,( 2       , 5    )
go
--查询
select distinct user1_id,user2_id
from Friendship f
left join Listens l1
on user1_id=l1.user_id
left join Listens l2
on user2_id=l2.user_id
where l1.song_id=l2.song_id
and l1.day=l2.day
group by user1_id,user2_id,l1.day
having count(distinct l2.song_id)>=3

19. Leetcodify 好友推荐

写出 SQL 语句，为 Leetcodify 用户推荐好友。我们将符合下列条件的用户 x 推荐给用户 y ：
用户 x 和 y 不是好友，且
用户 x 和 y 在同一天收听了相同的三首或更多不同歌曲。
注意，好友推荐是单向的，这意味着如果用户 x 和用户 y 需要互相推荐给对方，结果表需要将用户 x 推荐给用户 y 并将用户 y 推荐给用户 x。另外，结果表不得出现重复项（即，用户 y 不可多次推荐给用户 x ）。
按任意顺序返回结果表。

SELECT DISTINCT t.user1_id AS user_id,t.user2_id AS recommended_id
  FROM
(SELECT a.user_id AS user1_id
       ,b.user_id AS user2_id
       ,a.song_id
       ,a.day
       ,COUNT(a.song_id) OVER (PARTITION BY a.day,a.user_id,b.user_id) AS cnt
  FROM (SELECT DISTINCT * FROM Listens) a
 INNER JOIN (SELECT DISTINCT * FROM Listens) b
    ON a.user_id <> b.user_id
   AND a.song_id = b.song_id
   AND a.day = b.day) t
  LEFT JOIN Friendship t1
    ON t.user1_id = t1.user1_id AND t.user2_id = t1.user2_id
  LEFT JOIN Friendship t2
    ON t.user1_id = t2.user2_id AND t.user2_id = t2.user1_id
 WHERE t.cnt >= 3 AND t1.user1_id IS NULL AND t2.user1_id IS NULL

20. 航班机票状态

乘客提前预订航班机票。如果乘客预订了一张航班机票，并且航班上还有空座位，则乘客的机票将 得到确认 。然而，如果航班已经满员，乘客将被列入 等候名单 。
编写解决方案来确定每个乘客航班机票的当前状态。
按 passenger_id 升序排序 返回结果表。

--建表
if object_id ('Flights','u') is not null drop table Flights
go
create table Flights(
  flight_id    int
, capacity     int
)
go
insert into Flights
values
 ( 1      ,2    )
,( 2      ,2    )
,( 3      ,1    )
go
if object_id('Passengers','u') is not null drop table Passengers
go
create table Passengers(
 passenger_id  int
,flight_id     int
,booking_time  datetime
)
go
insert into Passengers
values
 (101  , 1  ,'2023-07-10 16:30:00')
,(102  , 1  ,'2023-07-10 17:45:00')
,(103  , 1  ,'2023-07-10 12:00:00')
,(104  , 2  ,'2023-07-05 13:23:00')
,(105  , 2  ,'2023-07-05 09:00:00')
,(106  , 3  ,'2023-07-08 11:10:00')
,(107  , 3  ,'2023-07-08 09:10:00')
go
--查询
select a.passenger_id
     , case when row_number() over(partition by a.flight_id order by booking_time) <= b.capacity then 'Confirmed'
         else 'Waitlist' end as Status
from passengers a
left join Flights b
on a.flight_id = b.flight_id
order by a.passenger_id

21. 受欢迎度百分比

编写一条 SQL 查询，找出 Meta/Facebook 平台上每个用户的受欢迎度的百分比。受欢迎度百分比定义为用户拥有的朋友总数除以平台上的总用户数，然后乘以 100，并 四舍五入保留 2 位小数 。
返回按照 user1 升序 排序的结果表。

if object_id('Friends','u') is not null drop table Friends
go
create table Friends(
 user1     int
,user2      int
)
go
insert into Friends
values
 ( 2   , 1   )
,( 1   , 3   )
,( 4   , 1   )
,( 1   , 5   )
,( 1   , 6   )
,( 2   , 6   )
,( 7   , 2   )
,( 8   , 3   )
,( 3   , 9   )
go
--查询
with t as (select user1 ,user2 from friends
union select user2 ,user1 from friends)
select user1 ,cast(count(distinct user2) * 100.0 /(SELECT count(distinct user1) FROM T) as decimal(19,2)) as percentage_popularity
from T a
group by user1

22. 用户购买平台

编写解决方案找出每天 仅 使用手机端用户、仅 使用桌面端用户和 同时 使用桌面端和手机端的用户人数和总支出金额。
以 任意顺序 返回结果表。

--建表
if object_id('Spending','u') is not null drop table Spending
go
create table Spending(
 user_id      int
,spend_date   date
,platform     varchar(20)
,amount       int
)
go
insert into Spending
values
 (1    ,'2019-07-01', 'mobile'   ,100  )
,(1    ,'2019-07-01', 'desktop'  ,100  )
,(2    ,'2019-07-01', 'mobile'   ,100  )
,(2    ,'2019-07-02', 'mobile'   ,100  )
,(3    ,'2019-07-01', 'desktop'  ,100  )
,(3    ,'2019-07-02', 'desktop'  ,100  )
go
--查询

 WITH T AS(
select spend_date, platform, count( DISTINCT user_id) as total_users,
sum(amount) as total_amount
from (select user_id, spend_date,amount,
    case when count(platform) over(partition by user_id, spend_date) =2 then 'both'
    else platform end as platform
    from spending ) A
GROUP  by spend_date,platform
)

 SELECT B.spend_date, A.platform, ISNULL(T.total_amount,0) AS total_amount,ISNULL(T.total_users,0) AS total_users
 FROM (select 'mobile' as platform
         union
         select 'desktop' as platform
         union
         select 'both' as platform
         ) A
 CROSS JOIN (select  distinct spend_date from spending ) B
LEFT JOIN T T
ON B.spend_date=T.spend_date AND A.platform=T.platform
order by B.spend_date,LEN(A.platform) DESC

23. 锦标赛优胜者

每组的获胜者是在组内累积得分最高的选手。如果平局，player_id 最小 的选手获胜。
编写解决方案来查找每组中的获胜者。
返回的结果表单 没有顺序要求 。

--建表
if object_id('Players','u') is not null drop table Players
go
create table Players (
 player_id    int
,group_id     int
)
go
insert into Players
values
 ( 15    ,1   )
,( 25    ,1   )
,( 30    ,1   )
,( 45    ,1   )
,( 10    ,2   )
,( 35    ,2   )
,( 50    ,2   )
,( 20    ,3   )
,( 40    ,3   )
go
if object_id('Matches','u') is not null drop table Matches
go
create table Matches (
  match_id      int
, first_player  int
, second_player int
, first_score   int
, second_score  int
)
go
insert into Matches
values
 ( 1  ,15    ,45   ,3   ,0   )
,( 2  ,30    ,25   ,1   ,2   )
,( 3  ,30    ,15   ,2   ,0   )
,( 4  ,40    ,20   ,5   ,2   )
,( 5  ,35    ,50   ,1   ,1   )
go
--查询
select group_id,player_id from (
select* ,row_number() over(partition by group_id order by score desc ,player_id asc) as rnk
from (
select b.group_id, a.player_id,sum(score) as score  from (
select match_id,first_player as player_id,first_score as Score from Matches
union
select match_id,second_player,second_score from Matches ) a
left join Players  b
on a.player_id = b.player_id
group by b.group_id,a.player_id)a  )a
where rnk = 1

24. 周内每天的销售情况

你是企业主，想要获得分类商品和周内每天的销售报告。
编写解决方案，报告 周内每天 每个商品类别下订购了多少单位。
返回结果表单 按商品类别排序 。

--建表
if object_id('Orders','u') is not null drop table Orders
go
create table Orders (
 order_id     int
,customer_id  int
,order_date   date
,item_id      varchar
,quantity     int
)
go
insert into Orders
values
  (1    ,1   ,'2020-06-01',  1  ,10   )
, (2    ,1   ,'2020-06-08',  2  ,10   )
, (3    ,2   ,'2020-06-02',  1  ,5    )
, (4    ,3   ,'2020-06-03',  3  ,5    )
, (5    ,4   ,'2020-06-04',  4  ,1    )
, (6    ,4   ,'2020-06-05',  5  ,5    )
, (7    ,5   ,'2020-06-05',  1  ,10   )
, (8    ,5   ,'2020-06-14',  4  ,5    )
, (9    ,5   ,'2020-06-21',  3  ,5    )
go
if object_id('Items','u') is not null drop table Items
go
create table Items (
 item_id          varchar(20)
,item_name        varchar(20)
,item_category    varchar(20)
)
go
insert into Items
values
 ( 1   ,'LC Alg. Book'   ,'Book'      )
,( 2   ,'LC DB. Book'    ,'Book'      )
,( 3   ,'LC SmarthPhone' ,'Phone'     )
,( 4   ,'LC Phone 2020'  ,'Phone'     )
,( 5   ,'LC SmartGlass'  ,'Glasses'   )
,( 6   ,'LC T-Shirt XL'  ,'T-Shirt'   )
go
--查询
select category,isnull(Monday,0) as Monday,isnull(Tuesday,0) as Tuesday,isnull(Wednesday,0) as Wednesday
     ,isnull(Thursday,0) as Thursday
     ,isnull(Friday,0) as Friday
    ,isnull(Saturday,0) as Saturday
    ,isnull(Sunday,0) as Sunday
from (
select a.item_category as Category,datename(weekday,order_date) as Weekday,sum(quantity)as quantity
from Items a
left join Orders b
on a.item_id = b.item_id
group by a.item_category,datename(weekday,order_date) )a
pivot(sum(quantity) for Weekday in ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]))p
order by Category

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

相关企业：亚马逊/奥多比 Adobe

找出各个部门员工的平均薪资与公司平均薪资之间的比较结果（更高 / 更低 / 相同）。
以 任意顺序 返回结果表。
结果格式如下所示。

--建表
if object_id('Salary','u') is not null drop table Salary
go
create table Salary(
id           int
,employee_id  int
 ,amount       int
 ,pay_date     date
)
go
insert into Salary
values
 ( 1  , 1    ,9000  ,'2017/03/31')
,( 2  , 2    ,6000  ,'2017/03/31')
,( 3  , 3    ,10000 ,'2017/03/31')
,( 4  , 1    ,7000  ,'2017/02/28')
,( 5  , 2    ,6000  ,'2017/02/28')
,( 6  , 3    ,8000  ,'2017/02/28')
go
if object_id('Employee','u') is not null drop table Employee
go
create table Employee (
  employee_id    int
, department_id  int
)
go
insert into Employee
values
 (1  ,1   )
,(2  ,2   )
,(3  ,2   )
go
--查询

select pay_month,department_id
     ,case when AVG_DPT_Salary = AVG_CPY_Salary then 'same'
            when AVG_DPT_Salary > AVG_CPY_Salary then 'higher'
else 'lower' end as comparison
from (
select  distinct format(pay_date,'yyyy-MM') as pay_month,B.department_id
,avg(amount      ) over(partition by format(pay_date,'yyyy-MM'),B.department_id) AS AVG_DPT_Salary
,avg(amount      ) over (partition by format(pay_date,'yyyy-MM') )  AS AVG_CPY_Salary

from salary a
left join employee b
on a.employee_id = b.employee_id  ) a

26. 游戏玩法分析 ⑤

相关企业：字节跳动/shopee/亚马逊/快手

玩家的 安装日期 定义为该玩家的第一个登录日。
我们将日期 x 的 第一天留存率 定义为：假定安装日期为 X 的玩家的数量为 N ，其中在 X 之后的一天重新登录的玩家数量为 M，M/N 就是第一天留存率，四舍五入到小数点后两位。
编写解决方案，报告所有安装日期、当天安装游戏的玩家数量和玩家的 第一天留存率。
以 任意顺序 返回结果表。

--建表
if object_id('Activity','u') is not null drop table Activity
go
create table Activity(
 player_id     int
,device_id     int
,event_date    date
,games_played  int
)
go
insert into Activity
values
 ( 1  , 2  ,'2016-03-01', 5  )
,( 1  , 2  ,'2016-03-02', 6  )
,( 2  , 3  ,'2017-06-25', 1  )
,( 3  , 1  ,'2016-03-01', 0  )
,( 3  , 4  ,'2016-07-03', 5  )
go
--查询
with t as (
select player_id,min(event_date) as install_dt
from Activity
group by player_id )
select a.install_dt ,count(a.player_id) as installs
     ,cast(sum(case when b.event_date is not null then 1 else 0 end) * 1.0/count(a.player_id) as decimal(19,2)) as Day1_retention
from t a
left join activity b
on a.player_id = b.player_id and dateadd(day,1,a.install_dt )= b.event_date
group by a.install_dt

27. 报告系统状态的连续日期

系统 每天 运行一个任务。每个任务都独立于先前的任务。任务的状态可以是失败或是成功。
编写解决方案找出 2019-01-01 到 2019-12-31 期间任务连续同状态 period_state 的起止日期（start_date 和 end_date）。即如果任务失败了，就是失败状态的起止日期，如果任务成功了，就是成功状态的起止日期。
最后结果按照起始日期 start_date 排序

--建表
if object_id('Failed','u') is not null drop table Failed
go
create table Failed(
    fail_date date
)
go
insert into Failed
values
('2018-12-28')
,('2018-12-29')
,('2019-01-04')
,('2019-01-05')
go
if object_id('Succeeded','u') is not null drop table Succeeded
go
create table Succeeded(
   success_date  date
)
go
insert into Succeeded
values
('2018-12-30')
,('2018-12-31')
,('2019-01-01')
,('2019-01-02')
,('2019-01-03')
,('2019-01-06')
go
--查询
select period_state ,min(eventdate) as start_date
     ,max(eventdate) as end_date from (
select *
     ,row_number() over(order by eventdate)
    -row_number() over(partition by period_state  order by eventdate) as rnk2
from (
select success_date as eventdate,'succeeded' as period_state  from Succeeded
union
select *,'failed'  from Failed ) a
where left([eventdate],4) = 2019) a
group by rnk2,period_state
order by min(eventdate)

28. 每次访问的交易次数

银行想要得到银行客户在一次访问时的交易次数和相应的在一次访问时该交易次数的客户数量的图表
编写解决方案找出多少客户访问了银行但没有进行任何交易，多少客户访问了银行进行了一次交易等等
结果包含两列：
transactions_count： 客户在一次访问中的交易次数
visits_count： 在 transactions_count 交易次数下相应的一次访问时的客户数量
transactions_count 的值从 0 到所有用户一次访问中的 max(transactions_count)
结果按 transactions_count 排序

--建表
if object_id('Visits','u') is not null drop table Visits
go
create table Visits(
  user_id        int
, visit_date     date
)
go
insert into  Visits
values
 ( 1      ,'2020-01-01')
,( 2      ,'2020-01-02')
,( 12     ,'2020-01-01')
,( 19     ,'2020-01-03')
,( 1      ,'2020-01-02')
,( 2      ,'2020-01-03')
,( 1      ,'2020-01-04')
,( 7      ,'2020-01-11')
,( 9      ,'2020-01-25')
,( 8      ,'2020-01-28')
go
if object_id('Transactions','u') is not null drop table Transactions
go
create table Transactions (
 user_id           int
, transaction_date  date
, amount            int
)
go
insert into Transactions
values
( 1   ,'2020-01-02',120 )
,( 2   ,'2020-01-03',22  )
,( 7   ,'2020-01-11',232 )
,( 1   ,'2020-01-04',7   )
,( 9   ,'2020-01-25',33  )
,( 9   ,'2020-01-25',66  )
,( 8   ,'2020-01-28',1   )
,( 9   ,'2020-01-25',99  )
go
--查询
with T AS (
select transactions_count ,count(*) as visits_count
from(select a.*,isnull(B.transactions_count,0) as transactions_count
    from visits  a
    left join (select user_id ,transaction_date,count(*) as transactions_count
                from transactions
                group by user_id ,transaction_date) b
    on a.user_id = b.user_id and a.visit_date = b.transaction_date) a
    group by transactions_count  )
select number as transactions_count,isnull(b.visits_count ,0) as visits_count
from master..spt_values a
left join T b
on a.number = b.transactions_count
where type = 'p' and number <= (select max(transactions_count)
    from t )
order by a.number

29. 获取最近第二次的活动

编写解决方案展示每一位用户 最近第二次 的活动
如果用户仅有一次活动，返回该活动
一个用户不能同时进行超过一项活动，以 任意 顺序返回结果
下面是返回结果格式的例子。

--建表
if object_id('UserActivity','u') is not null drop table UserActivity
go
create table UserActivity(
  username      varchar(20)
, activity       varchar(20)
, startDate     Date
, endDate       Date
)
go
insert into UserActivity
values
('Alice'   , 'Travel'     ,'2020-02-12','2020-02-20')
,('Alice'   , 'Dancing'    ,'2020-02-21','2020-02-23')
,('Alice'   , 'Travel'     ,'2020-02-24','2020-02-28')
,('Bob'     , 'Travel'     ,'2020-02-11','2020-02-18')
go
--查询
select username,activity , startDate,endDate
from (select *
     ,row_number() over(partition by username order by enddate desc ) as rnk
     , count(*) over(partition by username  ) as cnt
     from UserActivity ) a
where rnk  = 2 or cnt = 1

30. 按年度列出销售总额

编写解决方案，找出每个产品每年的总销售额，并包含 product_id , product_name , report_year 以及 total_amount 。
返回结果并按 product_id 和 report_year 排序。
返回结果格式如下例所示。

--建表
if object_id('Product','u') is not null drop table Product
go
create table Product(
 product_id     int
,product_name   varchar(20)
)
go
insert into Product
values
(1    ,'LC Phone'   )
,(2    ,'LC T-Shirt' )
,(3    ,'LC Keychain')
go
if object_id ('Sales','u') is not null drop table Sales
go
create table Sales(
  product_id          int
, period_start        date
, period_end          date
, average_daily_sales int
)
go
insert into Sales
values
(1  ,'2019-01-25', '2019-02-28', 100 )
,(2  ,'2018-12-01', '2020-01-01', 10  )
,(3  ,'2019-12-01', '2020-01-31', 1   )
go
--查询
select product_id,product_name ,report_year,(datediff(day,period_start,period_end) + 1 ) * average_daily_sales as total_amount
from (
         select a.Product_id
              , a.Product_name
              , b.report_year
              , case
                    when datepart(year, c.period_start) = b.report_year then c.period_start
                    else cast(b.report_year as varchar(4)) + '-01-01' end as Period_start
              , case
                    when datepart(year, c.period_end) = b.report_year then c.period_end
                    else cast(b.report_year as varchar(4)) + '-12-31' end as period_end
              ,c.average_daily_sales
         from Product a
                  left join Sales c
                            on a.product_id = c.product_id
                  left join (
             select number as report_year
             from master..spt_values
             where type = 'p'
               and number between (select min(datepart(year, period_start)) from sales)
                 and (select max(datepart(year, period_end)) from sales)) b
                            on b.report_year between datepart(year, c.period_start) and datepart(year, c.period_end)
     ) a
order by product_id,report_year

31. 查找成绩处于中游的学生

成绩处于中游的学生是指至少参加了一次测验, 且得分既不是最高分也不是最低分的学生。
编写解决方案，找出在 所有 测验中都处于中游的学生 (student_id, student_name)。不要返回从来没有参加过测验的学生。
返回结果表按照 student_id 排序。
返回结果格式如下。

--建表
if object_id('Student','u') is not null drop table Student
go
create table Student(
 student_id        int
,student_name      varchar(20)
)
go
insert into Student
values
(1     ,'Daniel'    )
,(2     ,'Jade'      )
,(3     ,'Stella'    )
,(4     ,'Jonathan'  )
,(5     ,'Will'      )
go
if object_id('Exam','u') is not null drop table Exam
go
create table Exam(
    exam_id      int
, student_id    int
, score         int
)
go
insert into   Exam
values
(10   , 1  , 70   )
,(10   , 2  , 80   )
,(10   , 3  , 90   )
,(20   , 1  , 80   )
,(30   , 1  , 70   )
,(30   , 3  , 80   )
,(30   , 4  , 90   )
,(40   , 1  , 60   )
,(40   , 2  , 70   )
,(40   , 4  , 80   )
go
--查询
select a.student_id,a.student_name
from student a
where a.student_id not in (
    select distinct  student_id
    from (select *
            ,rank() over(partition by exam_id  order by score asc ) as ascrnk
            ,rank() over(partition by exam_id order by score desc ) as rnk

            from Exam ) b
where (b.rnk = 1 or b.ascrnk = 1) )
and student_id  in (select student_id from exam)
order by student_id

32. 寻找没有被执行的任务对

编写解决方案报告没有被执行的（主任务，子任务）对，即没有被执行的（task_id, subtask_id）。
以 任何顺序 返回即可。

--建表
if object_id('Tasks','u') is not null drop table Tasks
go
create table Tasks(
 task_id        int
, subtasks_count  int
)
go
insert into Tasks
values
( 1    , 3  )
,( 2    , 2  )
,( 3    , 4  )
go
if object_id('Executed','u') is not null drop table Executed
go
create table Executed (
 task_id        int
,subtask_id     int
)
go
insert into   Executed
values
( 1     , 2    )
,( 3     , 1    )
,( 3     , 2    )
,( 3     , 3    )
,( 3     , 4    )
go
--查询
select a.task_id,c.number as subtask_id
from tasks a
left join (select number from master..spt_values where type = 'p'
    and number >= 1 ) c
on c.number between 1 and a.subtasks_count
where not exists (select * from executed b
    where a.task_id = b.task_id and
          c.number = b.subtask_id
    )

33. 页面推荐 ②

您正在为一个社交媒体网站实施一个页面推荐系统。如果页面被user_id的 至少一个朋友喜欢 ，而 不被user_id喜欢 ，你的系统将 推荐 一个页面到user_id。
编写一个解决方案来查找针对每个用户的所有可能的 页面建议 。每个建议应该在结果表中显示为一行，包含以下列:
user_id: 系统向其提出建议的用户的ID。
page_id: 推荐为 user_id 的页面ID。.
friends_likes: user_id 对应 page_id 的好友数。
以 任意顺序 返回结果表。
返回结果格式示例如下。

在这里插入代码片

34. 在连续天数上进行了最多交易次数的顾客

编写一个解决方案，找到连续天数上进行了最多交易的所有 customer_id 。
返回所有具有最大连续交易次数的 customer_id 。结果表按 customer_id 的 升序 排序。
结果的格式如下所示。

--建表
if object_id('Transactions','u') is not null drop table Transactions
go
create table Transactions(
  transaction_id    int
, customer_id       int
, transaction_date  date
, amount            int
)
go
insert into Transactions
values
 ( 1     ,101  ,'2023-05-01',    100 )
,( 2     ,101  ,'2023-05-02',    150 )
,( 3     ,101  ,'2023-05-03',    200 )
,( 4     ,102  ,'2023-05-01',    50  )
,( 5     ,102  ,'2023-05-03',    100 )
,( 6     ,102  ,'2023-05-04',    200 )
,( 7     ,105  ,'2023-05-01',    100 )
,( 8     ,105  ,'2023-05-02',    150 )
,( 9     ,105  ,'2023-05-03',    200 )
go
--查询
with  T as (
select customer_id ,count(*) AS CNT
from (select * ,datepart(day,transaction_date) - rnk  as diff
        from (select customer_id ,transaction_date,dense_rank() over(partition by customer_id order by transaction_date) as rnk
                from Transactions ) a ) a
group by customer_id ,diff
)
select customer_id
from T
where cnt = (select max(cnt) from T )
order by customer_id

35. 连续递增交易

编写一个 SQL 查询，找出至少连续三天 amount 递增的客户。并包括 customer_id 、连续交易期的起始日期和结束日期。一个客户可以有多个连续的交易。
返回结果并按照 customer_id 升序 排列。
查询结果的格式如下所示。

--建表
if object_id('Transactions','u') is not null drop table Transactions
go
create table Transactions(
  transaction_id    int
, customer_id       int
, transaction_date  date
, amount            int
)
go
insert into Transactions
values
 ( 1   ,101   ,'2023-05-01'  ,100 )
,( 2   ,101   ,'2023-05-02'  ,150 )
,( 3   ,101   ,'2023-05-03'  ,200 )
,( 4   ,102   ,'2023-05-01'  ,50  )
,( 5   ,102   ,'2023-05-03'  ,100 )
,( 6   ,102   ,'2023-05-04'  ,200 )
,( 7   ,105   ,'2023-05-01'  ,100 )
,( 8   ,105   ,'2023-05-02'  ,150 )
,( 9   ,105   ,'2023-05-03'  ,200 )
,( 10  ,105   ,'2023-05-04'  ,300 )
,( 11  ,105   ,'2023-05-12'  ,250 )
,( 12  ,105   ,'2023-05-13'  ,260 )
,( 13  ,105   ,'2023-05-14'  ,270 )
go
--查询
with t as (
select * ,count(*) over(partition by customer_id,diff ) as cnt
from (select * ,datepart(day,transaction_date ) - rnk as diff
      from (select a.*
             ,dense_rank() over(partition by a.customer_id order by a.transaction_date ) as rnk
            from Transactions a )a ) a
)

select customer_id ,min(transaction_date) as consecutive_start ,max(transaction_date) as consecutive_end
from(select a.* ,b.amount - isnull(a.amount,0) as diffamount
        from T a
        left join T b
        on a.customer_id = b.customer_id and a.transaction_id = b.transaction_id - 1
        and a.cnt = b.cnt ) a
where cnt > = 3 and (diffamount >0 or diffamount is null)
group by customer_id,cnt
order by customer_id
--方法2
with tmp as
(select t1.*, case when t2.customer_id is NULL then 1 else 0 end group_start
from transactions t1
left join transactions t2 on t1.customer_id=t2.customer_id
and t1.amount > t2.amount
and DATEDIFF(DAY,t2.transaction_date,t1.transaction_date)=1)
, tmp2 as
(select *,sum(group_start) over (order by customer_id,transaction_date) grp
from tmp)
select customer_id,
min(transaction_date) consecutive_start,
max(transaction_date) consecutive_end
from tmp2
group by grp,customer_id
having count(1)>=3
order by customer_id

36. 最多连胜的次数

选手的 连胜数 是指连续获胜的次数，且没有被平局或输球中断。
编写解决方案来计算每个参赛选手最多的连胜数。
结果可以以 任何顺序 返回。
结果格式如下例所示：

--建表
if object_id('Matches','u') is not null drop table Matches
go
create table Matches(
 player_id    int
,match_day  date
, result     varchar(20)
)
go
insert into Matches
values
 (1 ,'2022-01-17', 'Win'  )
,(1 ,'2022-01-18', 'Win'  )
,(1 ,'2022-01-25', 'Win'  )
,(1 ,'2022-01-31', 'Draw' )
,(1 ,'2022-02-08', 'Win'  )
,(2 ,'2022-02-06', 'Lose' )
,(2 ,'2022-02-08', 'Lose' )
,(3 ,'2022-03-30', 'Win'  )
go
--查询
select distinct a.player_id ,isnull(b.longest_streak,0) as  longest_streak
from Matches a
left join (select player_id,max(cnt) as longest_streak 
           from (select player_id,count(*) cnt  
                from (select * ,rnk-rnk2 as diff  
                       from (select * ,row_number() over(partition by player_id order by match_day) as rnk
                                ,row_number() over(partition by player_id,result order by match_day ) as rnk2
                                from Matches a ) a
                        where result = 'win' )a
                group by player_id ,diff)a
group by player_id ) b
on a.player_id = b.player_id

37. 每辆车的乘客人数 ②

公交车和乘客到达 LeetCode 站。如果一辆公交车在 tbus 时间点到达车站，乘客在 tpassenger 到达车站，其中 tpassenger <= tbus，而该乘客没有赶上任何公交车，则该乘客将搭乘该公交车。此外，每辆公交车都有一个容量。如果在公交车到站的那一刻，等待的乘客超过了它的载客量 capacity，只有 capacity 个乘客才会搭乘该公交车。
编写解决方案，报告使用每条总线的用户数量。
返回按 bus_id 升序排序 的结果表。
结果格式如下所示。

--建表
if object_id('Buses','u') is not null drop table Buses
go
create table Buses(
  bus_id       int
, arrival_time  int
, capacity      int
)
go
insert into  Buses
values
( 1   ,2    ,1    )
,( 2   ,4    ,10   )
,( 3   ,7    ,2    )
go
if object_id('Passengers','u') is not null drop table Passengers
go
create table Passengers(
  passenger_id  int
, arrival_time  int
)
go
insert into Passengers
values
( 11  ,1    )
,( 12  ,1    )
,( 13  ,5    )
,( 14  ,6    )
,( 15  ,7    )
go
--查询
alter table Passengers add flag int
go
alter table Passengers add bus_id int
go

update Passengers set flag = 0
update Passengers set bus_id = null
--select * from Passengers
declare @bus_id int,@arrival_time int ,@capacity  int,@i int
declare C cursor for
select  bus_id, arrival_time,capacity from Buses order by arrival_time
open C
fetch next from C into @bus_id,@arrival_time,@capacity
while @@FETCH_STATUS=0
begin
 select @bus_id,@arrival_time,@capacity
	select * from
	(select passenger_id,arrival_time,ROW_NUMBER()over(order by arrival_time)Rnk
	from Passengers
	where arrival_time <= @arrival_time and flag = 0)a
	where Rnk <= @capacity

	update a
	set bus_id = @bus_id,flag = 1
	from Passengers a
	inner join (select * from (select passenger_id,arrival_time,ROW_NUMBER()over(order by arrival_time)Rnk
	                            from Passengers
	                            where arrival_time <= @arrival_time and flag = 0)a
	            where Rnk<=@capacity)b
	on a.passenger_id = b.passenger_id

fetch next from C into @bus_id,@arrival_time,@capacity
end
close C
deallocate C

select a.bus_id,count(b.passenger_id) as passengers_cnt from Buses a
left join (select * from Passengers)b
on a.bus_id = b.bus_id
group by a.bus_id

38. 建立方程

你有一个非常强大的程序，可以解决世界上任何单变量的方程。传递给程序的方程必须格式化如下:

• 左边 (LHS) 应该包含所有的术语。
• 右边 (RHS) 应该是零。
• LHS 的每一项应遵循 “X^” 的格式，其中:
  是 “+” 或者 “-”。
  是 factor 的 绝对值。
  是 power 的值。
• 如果幂是 1, 不要加上 “^”.
  例如, 如果 power = 1 并且 factor = 3, 将有 “+3X”。
• 如果幂是 0, 不要加上 “X” 和 “^”.
  例如, 如果 power = 0 并且 factor = -3, 将有 “-3”。
• LHS 中的幂应该按 降序排序。
  编写一个解决方案来构建方程。

结果格式如下所示。

39. 动态取消表的旋转

重要提示: 这个问题针对的是那些对 SQL 有丰富经验的人。如果你是初学者，我们建议你现在跳过它。
实现 UnpivotProducts 过程来重新组织 Products 表，使每一行都有一个产品的 id、销售该商品的商店名称以及该商品在该商店中的价格。如果某个商品在某个商店中不可用，则不要在结果表中包含该 product_id 和 store 组合的行。结果应该有三列:product_id、store 和 price。
过程应该在重新组织表之后返回它。
以 任意顺序 返回结果表。
查询结果格式如下所示。

--建表
if object_id('Products','u') is not null drop table Products
go
create table Products(
product_id   int
,LC_Store int
,Nozama int
,Shop int
,Souq   int
)
go
insert into Products
values
 ( 1  , 100   ,null ,110  , null)
,( 2  , null  ,200  ,null , 190 )
,( 3  , null  ,null ,1000 , 1900)
go

--查询
CREATE PROCEDURE UnpivotProducts AS
BEGIN
declare @SQL VARCHAR(MAX),@store_name varchar(200)
select @store_name  = STUFF( (SELECT ',' +column_name
                    from information_schema.columns
                where table_name = 'Products'
                and column_name <>'Product_id'
                FOR XML PATH(''))
        ,1,1,'')

set @sql = '
select product_id,store ,price
from Products
unpivot ( price for  store in ('+@store_name+'))p'
print(@sql)
exec (@sql)
END