CODEWITHSUNDEEP
mysqlsql
Jill Clover
Jill Clover

Reputation: 2328

Find third purchase without window function

I came across this question:

Given a revenue transaction table that contains user_id, timestamp, and revenue, how would you find the third purchase of every user?

I know how to use window function to solve it, but I don't know how to solve it without window function. For example, correlated subquery.

If we want to find n th purchase, does your method work?

Upvotes: 0

Views: 308

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270523

In MySQL, I would use variables:

select t.*
from (select t.*,
             (@rn := if(@u = user_id, @rn + 1,
                        if(@u := user_id, 1, 1)
                       )
             ) as rn
      from (select t.*
            from transactions t
            order by user_id, timestamp
           ) t cross join
           (select @rn := 0, @u := -1) params
     ) t
where rn = 3;

That said, the old-fashioned SQL method is a correlated subquery:

select t.*
from transactions t
where 3 = (select count(*)
           from transactions t2
           where t2.user_id = t.user_id and t2.timestamp <= t.timestamp
          );

Upvotes: 6

Related Questions