亚麻DS一道SQL

面试了一道SQL题，楼主写完，面试官提醒说要考虑有的人多次成为Prime member，感觉题目一下子变复杂了，时间有限面当时就说了下思路，现在回头想把题目写全。
Transaction Table: transaction_id, customer_id, product_id, payment_date, amount



Prime Table: customer_id, prime_status (prime-free, prime-paid, prime-student) , prime_start_date, prime_end_date

Note: if a

您好！
本帖隐藏的内容需要积分高于 188 才可浏览
您当前积分为 0。
使用VIP即刻解锁阅读权限或查看其他获取积分的方式

游客，您好！
本帖隐藏的内容需要积分高于 188 才可浏览
您当前积分为 0。
VIP即刻解锁阅读权限 或 查看其他获取积分的方式
Unlock interview details and practice with AI
Curated Interview Questions from Top Companies

spend
FROM Transaction t
LEFT JOIN unique_customer_id u
ON t.customer_id = u.customer_id
WHERE year(t.payment_date) = 2018
GROUP BY u.prime_status

select prime_status
,sum(amount)
from prime A
join transaction B
on A.customer=B.customer
and B.payment_date between A.prime_start_date and B.prime_end_date
and year(B.payment_date)=2018
group by 1

我觉得你的思路是对的，需要考虑同一个customer_id在2018年有不同的prime status。
简化了一下query，不知道对不对。

1. select p.prime_status, sum(ifnull(amount,0)) as total_spend
   
2. from prime as p
   
3. left join transaction as t
   
4. on p.customer_id= t.customer_id
   
5. and year(t.payment_date) = '2018'
   
6. and t.payment_date between p.prime_start_date and p.prime_end_date
   
7. group by 1

复制代码

楼主好(用英文讲一下自己的理解顺便练习一下, 感谢理解)

I think the follow-up is considering that there might be customers who are prime in some time frames and not in others. In this case, the transaction they had when they are not prime members should not be aggregated.
So I come up with a solution that we could first set the whole time span for the query as from '2018-01-01' to '2018-12-31' and query the customers who have ever been prime members in the time frame and their prime time frames.
Then join the tables on customer_id and filter out the transactions placed when the customers are actually prime, then aggregate the amount by prime status.

Here is the code:
with prime_2018 as (select customer_id, prime_status,
       case when prime_start_date <= '2018-01-01' then '2018-01-01' else prime_start_date end as prime_start_date,
       case when Prime_end_date >='2018-12-31' then '2018-12-31' else prime_end_date end as prime_end_date
from prime p
join transaction t on p.customer_id = t.customer_id)

. Χselect prime_status, sum(amount) as total_amount
from prime_2018
where payment_date >= prime_start_date and payment_date <= prime_end_date
group by prime_status

please feel free to bring up different ideas and correct it if I'm not right.:)

lz，有个问题没弄明白，如果算spend，那有一个人2017 付钱然后2018结束，这个人算在2018还是2017呢？你的window function选的是那些2018-01-01之后过期的用户，similarly，那2019过期的但是2018付钱的是要算在哪里？我觉得就按照payment date计算然后join prime type table group by prime type。

Lz, 我没懂，你为什么要用window function呢？我是这么写的
With cte as(select t.customer_id, payment_date, amount, prime_status from transaction t
Left join prime p
T.customer_id=p.customer_id
Where prime_status is not null)

Select prime_status, sum(amount)
From cte
Where year(patment_date)='2018'
Group by 1

小垂耳喵 发表于 2022-1-2 16:19
Lz, 我没懂，你为什么要用window function呢？我是这么写的
With cte as(select t.customer_id, payment_d ...

嗯 你说得对，不需要window function, 我改了一下：

1. SELECT p.prime_status, sum(ifnull(t.amount,0)) as total_spend
   
2. FROM Transaction t
   
3. LEFT JOIN Prime p
   
4. ON t.customer_id = p.customer_id
   
5. WHERE p.prime_status IS NOT NULL AND year(t.payment_date) = 2018 AND t.payment_date BETWEEN p.prime_start_date AND p.prime_end_date
   
6. GROUP BY p.prime_status
   

复制代码

小亩_b0e57d5 发表于 2022-1-2 20:26
我觉得你的思路是对的，需要考虑同一个customer_id在2018年有不同的prime status。
简化了一下query，不知 ...

谢谢，我现在想的query和你一样。。

Notsage 发表于 2022-1-2 18:56
楼主好(用英文讲一下自己的理解顺便练习一下, 感谢理解)

I think the follow-up is considering that th ...

谢谢，我又想了一下觉得应该这样写：

1. SELECT p.prime_status, SUM(IFNULL(t.amount, 0)) AS total_spend
   
2. FROM Transaction t
   
3. LEFT JOIN Prime p
   
4. ON t.customer_id = p.customer_id
   
5. WHERE p.prime_status IS NOT NULL AND year(t.payment_date) = 2018 AND t.payment_date BETWEEN p.prime_start_date AND p.prime_end_date
   
6. GROUP BY p.prime_status
   

复制代码

匿名者 发表于 2022-1-2 13:08
lz，有个问题没弄明白，如果算spend，那有一个人2017 付钱然后2018结束，这个人算在2018还是2017呢？你的wi ...

嗯，window function我自己把自己绕进去了，你可以看楼下我更新过的query

ztan71mu3fendi 发表于 2022-1-2 20:51
谢谢，我又想了一下觉得应该这样写：

楼主你说了 Note: if a customer is not in the Prime table, then non-prime.

既然用了left join， where 里面 prime_status is not null does not necessary.