"loyal" customers are considered loyal if they have purchased at least 5 times. I am trying to build an SQL query which returns only "loyal" customers along with the day on which they become "loyal" customers (the day of their 5th transaction).

Ideally the desired output would be as follows

I tried creating a new table as follows:

SELECT user_id, purchase_ts
    FROM Customers
    WHERE user_id IN (
        SELECT user_id
        FROM Customers
        GROUP BY user_id
        HAVING COUNT (user_id) >=5
)

But I am having trouble, any suggestions?

CodePudding user response：

On MySQL 8 you could use:

with cte as
            ( select   *, 
                       row_number() over (partition by user_id order by purchase_ts asc) row_num
             from accounts
            ) 
select user_id,purchase_ts
from cte
where row_num >=5;

Result:

user_id       purchase_ts
f594fsae       2021-07-25
f632fsae       2021-07-25

Demo

CodePudding user response：

Since MySQL introduced the support of subquery a long time ago, we have been using its techniques in some MySQL-version-nonspecific scenarios. In this case, we can use a correlated subquery to get exactly the fifth purchase_ts by using the LIMIT [OFFSET] clause. The WHERE clause is used to exclude those purchase_id which doesn't have a fifth purchase_ts.

select distinct user_Id, 
(select purchase_ts from purchase where user_id=p.user_id order by purchase_ts limit 4,1) as loyal_time 
from purchase p 
where (select purchase_ts from purchase where user_id=p.user_id order by purchase_ts limit 4,1) is not null;