SELECT vip.user_id id
FROM (SELECT oo.user_id, IF(SUM(IF(oo.Rev >= 40, 1, 0)) >= 3, 1, 0) SSVIP_Seg,
IF(SUM(IF(oo.Rev < 40 AND oo.Rev >= 15, 1, 0)) >= 3, 1, 0) SVIP_Seg
FROM (SELECT kk.id, kk.user_id, kk.Rev, kk.created_at, kk.rn, IF(@prev1 <> kk.user_id, @rn1:=0, @rn1), @prev1:=kk.user_id, @rn1:=@rn1+1 AS rn1
FROM (SELECT pp.id, pp.user_id, pp.Rev, pp.created_at, pp.rn
FROM (SELECT up.id, up.user_id, created_at, ROUND(amountpayout_foreign_exchange_rate) Rev, IF(@prev <> user_id, @rn:=0, @rn), @prev:=user_id, @rn:=@rn+1 AS rn
FROM (SELECT id, user_id, amount, payout_foreign_exchange_rate, created_at
FROM XXXYYY.user_purchase
WHERE status = 'completed'
AND created_at >= NOW() -INTERVAL 6086400 SECOND
AND user_id IS NOT NULL
AND user_id > 0
ORDER BY user_id, id) up, (SELECT @rn:=0) rn, (SELECT @prev:='') prev) pp
JOIN (SELECT pp.user_id, MAX(pp.rn) Max_rn
FROM (SELECT up.id, up.user_id, IF(@prev <> user_id, @rn:=0, @rn), @prev:=user_id, @rn:=@rn+1 AS rn
FROM (SELECT id, user_id
FROM XXXYYY.user_purchase
WHERE status = 'completed'
AND created_at >= NOW() -INTERVAL 6086400 SECOND
AND user_id IS NOT NULL
AND user_id > 0
ORDER BY user_id, id) up, (SELECT @rn:=0) rn, (SELECT @prev:='') prev) pp
GROUP BY pp.user_id) jk
ON pp.user_id = jk.user_id AND pp.rn >= jk.Max_rn - 6 AND jk.Max_rn - 6 >= 1 / <2>WHERE pp.user_id = 240XX24/) kk, (SELECT @rn1:=0) rn1, (SELECT @prev1:='') prev1
WHERE user_id IN (/250XX24,/ 240XX24)
/ <1> Here you cant limit only 1 user, ???????????????????????? */
) oo
GROUP BY oo.user_id) vip
WHERE vip.SVIP_Seg = 0 AND vip.SSVIP_Seg = 0;
If you are doing the limit in <1> with one user, the order will be miss sorted as below:
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
id, user_id, Rev, created_at, rn, IF(@prev1 <> kk.user_id, @rn1:=0, @rn1), @prev1:=kk.user_id,rn1
29225 | 240XX24 | XX.0 | 2022-11-08 13:43:32.0 | 2.0 | 0 | 2405724 | 1.0
29321 | 240XX24 | XX.0 | 2022-11-10 20:40:49.0 | 3.0 | 1 | 2405724 | 2.0
29930 | 240XX24 | XX.0 | 2022-11-24 05:03:30.0 | 4.0 | 2 | 2405724 | 3.0
30408 | 240XX24 | XX.0 | 2022-12-16 04:02:46.0 | 5.0 | 3 | 2405724 | 4.0
30460 | 240XX24 | XX.0 | 2022-12-18 06:29:23.0 | 6.0 | 4 | 2405724 | 5.0
30483 | 240XX24 | XX.0 | 2022-12-18 23:49:47.0 | 7.0 | 5 | 2405724 | 6.0
29176 | 240XX24 | XX.0 | 2022-11-07 02:38:24.0 | 1.0 | 6 | 2405724 | 7.0
^ ^
But you can do the limitation in <2>, I want to know why such thing happened, would you mind explain the machinasm behind it, I was read somewhere that Mariadb team also created MySQL many years ago.
johnhhu2020
commented
1 year ago 
RESET QUERY CACHE;
SELECT vip.user_id id FROM (SELECT oo.user_id, IF(SUM(IF(oo.Rev >= 40, 1, 0)) >= 3, 1, 0) SSVIP_Seg,
IF(SUM(IF(oo.Rev < 40 AND oo.Rev >= 15, 1, 0)) >= 3, 1, 0) SVIP_Seg FROM (SELECT kk.id, kk.user_id, kk.Rev, kk.created_at, kk.rn, IF(@prev1 <> kk.user_id, @rn1:=0, @rn1), @prev1:=kk.user_id, @rn1:=@rn1+1 AS rn1 FROM (SELECT pp.id, pp.user_id, pp.Rev, pp.created_at, pp.rn FROM (SELECT up.id, up.user_id, created_at, ROUND(amountpayout_foreign_exchange_rate) Rev, IF(@prev <> user_id, @rn:=0, @rn), @prev:=user_id, @rn:=@rn+1 AS rn FROM (SELECT id, user_id, amount, payout_foreign_exchange_rate, created_at FROM XXXYYY.user_purchase WHERE status = 'completed' AND created_at >= NOW() -INTERVAL 6086400 SECOND AND user_id IS NOT NULL AND user_id > 0 ORDER BY user_id, id) up, (SELECT @rn:=0) rn, (SELECT @prev:='') prev) pp JOIN (SELECT pp.user_id, MAX(pp.rn) Max_rn FROM (SELECT up.id, up.user_id, IF(@prev <> user_id, @rn:=0, @rn), @prev:=user_id, @rn:=@rn+1 AS rn FROM (SELECT id, user_id FROM XXXYYY.user_purchase WHERE status = 'completed' AND created_at >= NOW() -INTERVAL 6086400 SECOND AND user_id IS NOT NULL AND user_id > 0 ORDER BY user_id, id) up, (SELECT @rn:=0) rn, (SELECT @prev:='') prev) pp GROUP BY pp.user_id) jk ON pp.user_id = jk.user_id AND pp.rn >= jk.Max_rn - 6 AND jk.Max_rn - 6 >= 1 / <2>WHERE pp.user_id = 240XX24/) kk, (SELECT @rn1:=0) rn1, (SELECT @prev1:='') prev1 WHERE user_id IN (/250XX24,/ 240XX24) / <1> Here you cant limit only 1 user, ???????????????????????? */ ) oo GROUP BY oo.user_id) vip WHERE vip.SVIP_Seg = 0 AND vip.SSVIP_Seg = 0;
If you are doing the limit in <1> with one user, the order will be miss sorted as below: +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ id, user_id, Rev, created_at, rn, IF(@prev1 <> kk.user_id, @rn1:=0, @rn1), @prev1:=kk.user_id,rn1 29225 | 240XX24 | XX.0 | 2022-11-08 13:43:32.0 | 2.0 | 0 | 2405724 | 1.0 29321 | 240XX24 | XX.0 | 2022-11-10 20:40:49.0 | 3.0 | 1 | 2405724 | 2.0 29930 | 240XX24 | XX.0 | 2022-11-24 05:03:30.0 | 4.0 | 2 | 2405724 | 3.0 30408 | 240XX24 | XX.0 | 2022-12-16 04:02:46.0 | 5.0 | 3 | 2405724 | 4.0 30460 | 240XX24 | XX.0 | 2022-12-18 06:29:23.0 | 6.0 | 4 | 2405724 | 5.0 30483 | 240XX24 | XX.0 | 2022-12-18 23:49:47.0 | 7.0 | 5 | 2405724 | 6.0 29176 | 240XX24 | XX.0 | 2022-11-07 02:38:24.0 | 1.0 | 6 | 2405724 | 7.0 ^ ^ But you can do the limitation in <2>, I want to know why such thing happened, would you mind explain the machinasm behind it, I was read somewhere that Mariadb team also created MySQL many years ago.