I'm looking to find the first 2 purchases for every customer.

I have a table that looks something like this:


With columns:


There are many millions of rows in this table and I'm trying to look for an efficient way to grab the first two earliest records for each customer, so that I get a result set that looks something like this:


I'm at a loss on how to do this efficiently.

  • 2
    order_id can't be in your result set... either it's customer_email, order_id1, order_id2 or customer_email, purchase_date1, purchase_date2. – Colin 't Hart Jun 28 '18 at 9:40
  • @Colin'tHart - to be more specific, data that's consistent for all rows for a customer, or that ultimately comes from a customer table, can be shown once for the top two orders; information that can be different from one order to another should be shown explicitly for the two orders. order_id will be different for the first two orders, so you (logically) should show the order_id for each order. Now, purchase_date could actually be the same for the top two orders, but it could be different, so you should show both purchase dates as well. ... – RDFozz Jun 28 '18 at 16:51
  • ... For that matter, we're assuming that customer_email uniquely identifies a specific customer; perhaps a customer may specify a different email for two orders, but still be recognized as the same customer. TL;DR: customer_email,order_id1,purchase_date,order_id2,purchase_date2 might well be valid, as well - you aren't limited to just one field from the unique elements of order – RDFozz Jun 28 '18 at 16:54

Assuming a separate customers table, and a compound index on orders(customer_email, purchase_date), something like this will do it:

    select order_id
    from orders
    where customer_email = c.customer_email
    order by purchase_date
    limit 2
  ) as order_ids
from customers c;

Using a window function might be an option:

select * from (
  select customer_email, purchase_date, order_id, 
         row_number() over (partition by customer_email order by purchase_date) rn
  from orders
) t where rn < 3
  • Is Postgres smart enough not to calculate row_numbers for all orders, and to use an index to do so? At least with correlated subquery with limit, I have gotten it to only "walk the index" for the first rows for each customer. See dba.stackexchange.com/questions/51895/… – Colin 't Hart Jun 28 '18 at 12:43

Not the answer you're looking for? Browse other questions tagged or ask your own question.