SQL--筛选出购买某一种产品前两次时间差小于3个月的人

最新推荐文章于 2024-09-23 20:03:06 发布

Stephanie_0903

最新推荐文章于 2024-09-23 20:03:06 发布

阅读量1k

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL

本文链接：https://blog.youkuaiyun.com/Stephanie_0903/article/details/88597750

该博客主要介绍了如何使用SQL查询那些购买特定产品（12号产品）且两次购买时间间隔少于87天的消费者信息。在这些基础上，进一步筛选出有额外购买备注以及年龄低于12岁并同样满足购买间隔条件的客户。

1.Select all people from [summary] table, purchase #12 product, the time interval (1st time purchase and 2nd time purchase ) less than 87 days. 筛选出购买12号产品，第一次和第二次购买的时间相差小于87天的人。

SELECT A.id FROM 
(SELECT id,type,sequence,date,scr,updated_date FROM summary WHERE type=12 AND sequence='D1') A,
(SELECT id,type,sequence,date,scr,updated_date FROM summary WHERE type=12 AND sequence='D2') B 
WHERE A.id=B.id 
AND DATEDIFF(day,A.date,B.date) BETWEEN 1 AND 86
AND A.scr IS NOT NULL 
AND B.scr IS NOT NULL
ORDER BY B.updated_date DESC

2.Base on the 1st senario, select all people who have addtional remarks for their purchase 2 times less than 87 days. 在第1条基础上，查看是否有人给了特殊说明。

SELECT TOP 10 A.id FROM 
(SELECT id,type,sequence,date,scr,updated_date FROM summary WHERE type=12 AND sequence='D1') A,
(SELECT S.id,S.type,S.sequence,S.date,S.scr,S.updated_date 
FROM summary

最低0.47元/天解锁文章

新学期VIP享超值加赠