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 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,455
  • 15
  • 36
  • 44
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
  • 28,207
  • 24
  • 60
  • 76