Repeat Purchases on Multiple Days – SQL Question Asked by Stitch Fix

  • Post last modified:September 11, 2022
  • Reading time:2 mins read
Photo by Caspar Camille Rubin on Unsplash

Introduction

  • In this article we will solve Repeat Purchases on Multiple Days SQL question which is asked by Stitch Fix as per DataLemur website.

If you don’t know DatLemur then please do visit , it’s one of the best website to practice SQL questions and improve your SQL skills.


Question

  • We have been given purchases table which contains purchases made by different user on different days.
  • We have to output number of users who made purchase of same product on two or more days.

Solution

  • We will first partition our purchase table by user_id and product id, this partition will be order by date in ascending order. we will assign rank within each partition.
  • In the output we can see that most of the user_id and product_id combination has rank of 1.
  • We should be interested in the rank 2 because that would help us to get the user who purchased same product on two different days.
  • for user 333, we can see rank is 1 for all the records because purchase date is same for all of them.
  • but let’s consider user_id 536, this user bought same product at two different days hence the rank is different.
  • Now all we have to do is to just filter out cases where rank is greater than equal to 2 and count such users.

Final Solution

  • Here is our final solution.
select count(distinct user_id) from(
select user_id, product_id , 
rank() over(partition by user_id,product_id order by date(purchase_date) asc) as rnk 
from purchases
 ) x
 where rnk >=2

Submission

  • Our solution is accepted by the platform.

Conclusion

  • In this article we solved SQL question asked by Stitch Fix. In This question we used sql windowing operation ranking to get the output.

Leave a Reply