Leetcode题库(数据库合集)_ 难度:中等

目录

难度:中等

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;

85. 产品销售分析 ③

在这里插入图片描述
编写解决方案,选出每个售出过的产品 第一年 销售的 产品 id、年份、数量 和 价格。
结果表中的条目可以按 任意顺序 排列。
结果格式如下例所示:
在这里插入图片描述

--建表
if object_id('Sales','u') is not null drop table Sales
go
create table Sales(
  sale_id      int
, product_id   int
, year         int
, quantity     int
, price        int
)
go
insert into Sales
values
 (1   ,100   , 2008,10  ,5000)
,(2   ,100   , 2009,12  ,5000)
,(7   ,200   , 2011,15  ,9000)
go
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
 ( 100     ,'Nokia'    )
,( 200     ,'Apple'    )
,( 300     ,'Samsung'  )
go
--查询
select product_id,year  first_year,quantity, price
from (select *
,rank() over(partition by product_id order by year ) as rnk
from Sales
) a
where rnk = 1

86. 产品销售分析 ④

在这里插入图片描述
编写解决方案,为每个用户获取其消费最多的产品 id。如果同一用户在两个或多个产品上花费了最多的钱,请获取所有花费了最多的钱的产品。
以 任意顺序 返回结果表。
查询结果格式如下所示。
在这里插入图片描述

--建表
if object_id('Sales','u') is not null drop table Sales
go
create table Sales(
  sale_id     int
, product_id  int
, user_id     int
, quantity    int
)
go
insert into Sales
values
 ( 1  ,1   ,101  ,10)
,( 2  ,3   ,101  ,7 )
,( 3  ,1   ,102  ,9 )
,( 4  ,2   ,102  ,6 )
,( 5  ,3   ,102  ,10)
,( 6  ,1   ,102  ,6 )
go
if object_id('Product','u') is not null drop table Product
go
create table Product(
product_id   int
,price        int
)
go
insert into Product
values
 ( 1    ,10  )
,( 2    ,25  )
,( 3    ,15  )
go
--查询
select user_id  ,product_id  from (
select a.user_id,a.product_id,sum(quantity * price ) as sales
,rank() over(partition by a.user_id order by sum(quantity * price )  desc) as rnk
from Sales a
left join Product b
on a.product_id = b.product_id
group by a.user_id,a.product_id ) a
where rnk = 1
  • 19
    点赞
  • 22
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值