0

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

I have a table that looks something like this:

orders

With columns:

order_id
customer_email
grand_total
purchase_date

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:

order_id
customer_email
purchase_date1
purchase_date2

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

john
  • 153
  • 6
  • 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 09: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

2 Answers2

2

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

select
  customer_email,
  array(
    select order_id
    from orders
    where customer_email = c.customer_email
    order by purchase_date
    limit 2
  ) as order_ids
from customers c;
Colin 't Hart
  • 9,323
  • 15
  • 35
  • 43
0

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
mustaccio
  • 25,896
  • 22
  • 57
  • 72
  • 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 https://dba.stackexchange.com/questions/51895/efficient-query-to-get-greatest-value-per-group-from-big-table – Colin 't Hart Jun 28 '18 at 12:43