devbox
空白诗007
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Leetcode题库（数据库合集）_ 难度：困难_leetcode题库数据集

作者：空白诗007 | 2024-07-08 23:48:34

赞

踩

leetcode题库数据集

目录

难度：困难

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


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

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






  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35

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



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

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


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

6. 查询员工的累计薪水

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




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

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

中位数 是将数据样本中半数较高值和半数较低值分隔开的值。
编写一个 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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

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'


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27

9. Hopper 公司查询 ①

在这里插入图片描述
在这里插入图片描述
编写解决方案以报告 2020 年每个月的以下统计信息：

  • 截至某月底，当前在Hopper公司工作的驾驶员数量（active_drivers）。
  • 该月接受的乘车次数（accepted_rides）。
    返回按month 升序排列的结果表，其中month 是月份的数字（一月是1，二月是2，依此类推）。
    返回结果格式如下例所示。
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
--建表
if object_id('Drivers','u') is not null drop table Drivers
go
create table Drivers(
 driver_id    int
, join_date    date
)
go
insert into Drivers
values
 ( 10  ,'2019-12-10' )
,( 8   ,'2020-1-13'  )
,( 5   ,'2020-2-16'  )
,( 7   ,'2020-3-8'   )
,( 4   ,'2020-5-17'  )
,( 1   ,'2020-10-24' )
,( 6   ,'2021-1-5'   )
go
if object_id('Rides','u') is not null drop table Rides
go
create table Rides(
 ride_id       int
, user_id       int
, requested_at date
)
go
insert into Rides
values
 ( 6       ,75    ,'2019-12-9'  )
,( 1       ,54    ,'2020-2-9'   )
,( 10      ,63    ,'2020-3-4'   )
,( 19      ,39    ,'2020-4-6'   )
,( 3       ,41    ,'2020-6-3'   )
,( 13      ,52    ,'2020-6-22'  )
,( 7       ,69    ,'2020-7-16'  )
,( 17      ,70    ,'2020-8-25'  )
,( 20      ,81    ,'2020-11-2'  )
,( 5       ,57    ,'2020-11-9'  )
,( 2       ,42    ,'2020-12-9'  )
,( 11      ,68    ,'2021-1-11'  )
,( 15      ,32    ,'2021-1-17'  )
,( 12      ,11    ,'2021-1-19'  )
,( 14      ,18    ,'2021-1-27'  )
go
if object_id('AcceptedRides','u') is not null drop table AcceptedRides
go
create table AcceptedRides(
      ride_id       int
, driver_id     int
, ride_distance int
, ride_duration int
)
go
insert into AcceptedRides
values
 ( 10   ,10  ,63     , 38    )
,( 13   ,10  ,73     , 96    )
,( 7    ,8   ,100    , 28    )
,( 17   ,7   ,119    , 68    )
,( 20   ,1   ,121    , 92    )
,( 5    ,7   ,42     , 101   )
,( 2    ,4   ,6      , 38    )
,( 11   ,8   ,37     , 43    )
,( 15   ,8   ,108    , 82    )
,( 12   ,8   ,38     , 34    )
,( 14   ,1   ,90     , 74    )
go
--查询
select number as Month
,isnull(count(distinct c.driver_id ),0) as active_drivers
,isnull(count(distinct b.ride_id ),0) as accepted_rides
from master..spt_values o
left join  Rides a
on datepart(MM,a.requested_at) = o.number  and datepart(year,requested_at) = 2020
left join Drivers c
on ( datepart(MM,c.join_date  ) <= o.number and datepart(year,join_date) <= 2020) OR YEAR(join_date ) <2020
left join AcceptedRides b
on a.ride_id = b.ride_id
where type = 'p'
and number between  1 and 12
group by o.number
order by o.number

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82

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)





  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42

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)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31

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 )

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39

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;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55

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


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52

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

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

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40

21. 用户购买平台

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

--建表
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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42

22. 锦标赛优胜者

在这里插入图片描述
在这里插入图片描述
每组的获胜者是在组内累积得分最高的选手。如果平局，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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50

23. 周内每天的销售情况

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

--建表
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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54

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

相关企业：亚马逊/奥多比 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


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47

25. 游戏玩法分析 ⑤

相关企业：字节跳动/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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29

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

在这里插入图片描述
系统 每天 运行一个任务。每个任务都独立于先前的任务。任务的状态可以是失败或是成功。
编写解决方案找出 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)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42

27. 每次访问的交易次数

在这里插入图片描述
银行想要得到银行客户在一次访问时的交易次数和相应的在一次访问时该交易次数的客户数量的图表
编写解决方案找出多少客户访问了银行但没有进行任何交易，多少客户访问了银行进行了一次交易等等
结果包含两列：
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


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58

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

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

--建表
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

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

29. 按年度列出销售总额

在这里插入图片描述
编写解决方案，找出每个产品每年的总销售额，并包含 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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54

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

在这里插入图片描述
成绩处于中游的学生是指至少参加了一次测验, 且得分既不是最高分也不是最低分的学生。
编写解决方案，找出在 所有 测验中都处于中游的学生 (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


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51

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

在这里插入图片描述
编写解决方案报告没有被执行的（主任务，子任务）对，即没有被执行的（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
    )

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39

32. 页面推荐 ②

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

在这里插入代码片

  • 1

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

在这里插入图片描述
编写一个解决方案，找到连续天数上进行了最多交易的所有 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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34

34. 连续递增交易

在这里插入图片描述
编写一个 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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61

35. 最多连胜的次数

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

--建表
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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33

36. 每辆车的乘客人数 ②

在这里插入图片描述
公交车和乘客到达 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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72

37. 建立方程

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

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

结果格式如下所示。
在这里插入图片描述

38. 动态取消表的旋转

在这里插入图片描述
重要提示: 这个问题针对的是那些对 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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36

39. 市场分析 ②

在这里插入图片描述
编写一个解决方案，查找每个用户的加入日期和他们作为买家在 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  ,'2019-01-01', 'Lenovo'   )
,( 2  ,'2019-02-09', 'Samsung'  )
,( 3  ,'2019-01-19', 'LG'       )
,( 4  ,'2019-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   ,'2019-08-02', 2  ,1  ,3 )
,( 3   ,'2019-08-03', 3  ,2  ,3 )
,( 4   ,'2019-08-04', 1  ,4  ,2 )
,( 5   ,'2019-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 distinct a.user_id as seller_id
     , case when isnull(a.favorite_brand,'') <> isnull(c.Item_brand,'') then 'no' else 'yes' end as [2nd_item_fav_brand]
from Users  a
left join (select a.*,b.Item_brand
        ,row_number() over(partition by seller_id order by order_date) as rnk
        from Orders  a
        left join Items b
        on a.item_id = b.item_id
        ) c
on a.user_id = c.seller_id and c.rnk = 2


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62

40. Hopper 公司查询 ②

在这里插入图片描述
在这里插入图片描述
编写解决方案以报告 2020 年每个月的工作驱动因素 百分比（working_percentage），其中：
在这里插入图片描述
注意：如果一个月内可用驾驶员的数量为零，我们认为 working_percentage 为 0。
返回按 month 升序 排列的结果表，其中 month 是月份的编号（一月是 1，二月是 2，等等）。将 working_percentage 四舍五入至 小数点后两位。
结果格式如下例所示。
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

--建表
if object_id('Drivers','u') is not null drop table Drivers
go
create table Drivers(
 driver_id    int
, join_date    date
)
go
insert into Drivers
values
 ( 10  ,'2019-12-10' )
,( 8   ,'2020-1-13'  )
,( 5   ,'2020-2-16'  )
,( 7   ,'2020-3-8'   )
,( 4   ,'2020-5-17'  )
,( 1   ,'2020-10-24' )
,( 6   ,'2021-1-5'   )
go
if object_id('Rides','u') is not null drop table Rides
go
create table Rides(
 ride_id       int
, user_id       int
, requested_at date
)
go
insert into Rides
values
 ( 6       ,75    ,'2019-12-9'  )
,( 1       ,54    ,'2020-2-9'   )
,( 10      ,63    ,'2020-3-4'   )
,( 19      ,39    ,'2020-4-6'   )
,( 3       ,41    ,'2020-6-3'   )
,( 13      ,52    ,'2020-6-22'  )
,( 7       ,69    ,'2020-7-16'  )
,( 17      ,70    ,'2020-8-25'  )
,( 20      ,81    ,'2020-11-2'  )
,( 5       ,57    ,'2020-11-9'  )
,( 2       ,42    ,'2020-12-9'  )
,( 11      ,68    ,'2021-1-11'  )
,( 15      ,32    ,'2021-1-17'  )
,( 12      ,11    ,'2021-1-19'  )
,( 14      ,18    ,'2021-1-27'  )
go
if object_id('AcceptedRides','u') is not null drop table AcceptedRides
go
create table AcceptedRides(
      ride_id       int
, driver_id     int
, ride_distance int
, ride_duration int
)
go
insert into AcceptedRides
values
 ( 10   ,10  ,63     , 38    )
,( 13   ,10  ,73     , 96    )
,( 7    ,8   ,100    , 28    )
,( 17   ,7   ,119    , 68    )
,( 20   ,1   ,121    , 92    )
,( 5    ,7   ,42     , 101   )
,( 2    ,4   ,6      , 38    )
,( 11   ,8   ,37     , 43    )
,( 15   ,8   ,108    , 82    )
,( 12   ,8   ,38     , 34    )
,( 14   ,1   ,90     , 74    )
go
--查询
select number as month
,case when isnull(count(distinct c.driver_id ),0) = 0 then 0
    else cast(isnull(count(distinct b.driver_id ),0) * 1.0  /isnull(count(distinct c.driver_id ),0) * 100 as decimal(19,2)) end as working_percentage
from master..spt_values o
left join  Rides a
on datepart(MM,a.requested_at) = o.number  and datepart(year,requested_at) = 2020
left join Drivers c
on ( datepart(MM,c.join_date  ) <= o.number and datepart(year,join_date) <= 2020) OR YEAR(join_date ) <2020
left join AcceptedRides b
on a.ride_id = b.ride_id
where type = 'p'
and number between  1 and 12
group by o.number
order by o.number


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83

41. Hopper 公司查询 ③

在这里插入图片描述
在这里插入图片描述
编写一个解决方案，计算出从 2020 年 1 月至 3 月 至 2020 年 10 月至 12 月 的每三个月窗口的 average_ride_distance 和 average_ride_duration 。并将 average_ride_distance 和 average_ride_duration 四舍五入至 小数点后两位 。
通过将三个月的总 ride_distance 相加并除以 3 来计算 average_ride_distance 。average_ride_duration 的计算方法与此类似。
返回按 month 升序排列的结果表，其中 month 是起始月份的编号（一月为 1，二月为 2 …）。

查询结果格式如下示例所示。
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

--建表
if object_id('Drivers','u') is not null drop table Drivers
go
create table Drivers(
 driver_id    int
, join_date    date
)
go
insert into Drivers
values
 ( 10  ,'2019-12-10' )
,( 8   ,'2020-1-13'  )
,( 5   ,'2020-2-16'  )
,( 7   ,'2020-3-8'   )
,( 4   ,'2020-5-17'  )
,( 1   ,'2020-10-24' )
,( 6   ,'2021-1-5'   )
go
if object_id('Rides','u') is not null drop table Rides
go
create table Rides(
 ride_id       int
, user_id       int
, requested_at date
)
go
insert into Rides
values
 ( 6       ,75    ,'2019-12-9'  )
,( 1       ,54    ,'2020-2-9'   )
,( 10      ,63    ,'2020-3-4'   )
,( 19      ,39    ,'2020-4-6'   )
,( 3       ,41    ,'2020-6-3'   )
,( 13      ,52    ,'2020-6-22'  )
,( 7       ,69    ,'2020-7-16'  )
,( 17      ,70    ,'2020-8-25'  )
,( 20      ,81    ,'2020-11-2'  )
,( 5       ,57    ,'2020-11-9'  )
,( 2       ,42    ,'2020-12-9'  )
,( 11      ,68    ,'2021-1-11'  )
,( 15      ,32    ,'2021-1-17'  )
,( 12      ,11    ,'2021-1-19'  )
,( 14      ,18    ,'2021-1-27'  )
go
if object_id('AcceptedRides','u') is not null drop table AcceptedRides
go
create table AcceptedRides(
      ride_id       int
, driver_id     int
, ride_distance int
, ride_duration int
)
go
insert into AcceptedRides
values
 ( 10   ,10  ,63     , 38    )
,( 13   ,10  ,73     , 96    )
,( 7    ,8   ,100    , 28    )
,( 17   ,7   ,119    , 68    )
,( 20   ,1   ,121    , 92    )
,( 5    ,7   ,42     , 101   )
,( 2    ,4   ,6      , 38    )
,( 11   ,8   ,37     , 43    )
,( 15   ,8   ,108    , 82    )
,( 12   ,8   ,38     , 34    )
,( 14   ,1   ,90     , 74    )
go
--查询
with T AS (
select number as month
from master..spt_values o
where type = 'p' and number between  1 and 10
)
select month
,cast(isnull(sum(ride_distance ) ,0) * 1.0  /3 as decimal(19,2)) as average_ride_distance
,cast(isnull(sum(ride_duration  ) ,0)* 1.0  /3  as decimal(19,2)) as average_ride_duration
from T o
left join  Rides a
on datepart(MM,a.requested_at) - 2 <= o.month
and datepart(MM,a.requested_at) >= o.month
and datepart(year,requested_at) = 2020
left join AcceptedRides b
on a.ride_id = b.ride_id
group by month
order by o.month


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/空白诗007/article/detail/800751
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号