获取最近7天内有交易，并且30天内只有一笔交易的用户

Question

我有一个如下表，今天是2021-05-25。

UserId	事务日期
1	2021-05-21
1	2021-05-12
4	2021-05-25
1	2021-04-03
3	2021-05-15
3	2021-04-02
4	2021-03-25

您可以将聚合与having:一起使用

select userid
from t
where transactiondate >= curdate() - interval 30 day
group by userid
having count(*) = 1 and
max(transactiondate) >= curdate() - interval 7 day;

如果你想要交易细节，它会更复杂一点：

select t.*
from transactions t
where t.transactiondate >= curdate() - interval 7 day and
not exists (select 1
from transactions t2
where t2.userid = t.userid and
t2.transactiondate >= curdate() - interval 30 day and
t2.transactiondate <> t.transactiondate
);