亚麻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 customer is not in the Prime

您好！
本帖隐藏的内容需要积分高于 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

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

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

我觉得你的思路是对的，需要考虑同一个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

复制代码

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

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

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

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

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

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

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

小亩_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

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

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