SQL查询查找首笔订单是在周日的客户

Question

我有两个数据表

表1:DK_Table

DK_ID   DK_Name Dk_location Cust_Id Amount  Event   Time _stamp
1   A   KOR01   111123  752 Order_Placed    8-14-21 21:05
3   C   WHF01   111123  688 Order_Placed    8-15-21 22:20
1   A   KOR01   111123  752 Order_Accepted  8-14-21 21:05
1   A   KOR01   111123  752 Order_Prepared  8-14-21 21:09
1   A   KOR01   111123  752 Order_Picked    8-14-21 21:25

表2:Delivery_Agent_Table

DEX_Id  Cust_Id DK_Id   Event   Time Stamp
AAEE01  111123  1   Delivery_Assigned   8-14-21 21:07
AAEE01  111123  1   Arrived_DK Location 8-14-21 21:20
AAEE01  111123  1   Arrived_Destination 8-14-21 21:40

面试官让我从以上两张表格中写出以下问题的查询。

问题:编写一个SQL查询，计算每个DK在周日第一次点餐的用户数量

Answer 1

所以，您的示例数据并不足以完全显示解决方案，但是使用我们已有的数据:

declare @DK_Table table(
DK_ID int,
DK_Name varchar(1),
Dk_location varchar(5),
Cust_Id int,
Amount int,
[Event] varchar(15),
[Time _stamp] datetime
)
insert into @DK_Table values
(1,'A','KOR01',111123,752,'Order_Placed',  '2021-08-14 21:05:00.000'),
(3,'C','WHF01',111123,688,'Order_Placed',  '2021-08-15 22:20:00.000'),
(1,'A','KOR01',111123,752,'Order_Accepted','2021-08-14 21:05:00.000'),
(1,'A','KOR01',111123,752,'Order_Prepared','2021-08-14 21:09:00.000'),
(1,'A','KOR01',111123,752,'Order_Picked',  '2021-08-14 21:25:00.000')
select count(distinct Cust_Id)
from
(
select 
*,
ROW_NUMBER() OVER (PARTITION BY Cust_Id ORDER BY [Time _stamp]) as OrderNumber
from @DK_Table
where [Event] = 'Order_Placed'
and datepart(weekday,[Time _stamp]) = 1 --Presuming your DATEFIRST is set to the default, so Sunday is 1
) a
where a.OrderNumber = 1

Delivery_Agent_Table似乎与您试图回答的问题无关，假设"订购食物的用户"的定义;为DK_Table中Event为Order_Placed时的记录

select子句中的distinct不应该是严格必要的，因为每个Cust_Id应该只有一个第一阶，但我把它放在那里只是作为一个安全网，以防实际数据有所不同。