Find first-time purchases with certain category I'm trying to find the first purchase of a customer who buys a computer category table only within all time purchase to segment them using SQL. My trial is. The JOIN part worked by someone else, so I just copy them and add the WHERE condition.

select order_table.guest_email as "Email",MIN(order_table.date_purchased as "First Purchase", main_category_name as "Category"
FROM (SELECT *,
                date_trunc('day', date_purchase) AS day_date
          FROM order_order 

        ) AS order_table

    JOIN order_line 
      ON order_table.id = order_line.order_id
    JOIN catalogue_productcategory 
      ON order_line.product_id = catalogue_productcategory.product_id
    JOIN (SELECT catalogue_category.id, catalogue_category.name, main_category.name AS main_category_name FROM catalogue_category 
          JOIN catalogue_category as main_category 
            ON main_category.path=substring(catalogue_category.path, 1, 4)) 
          AS category_table 
      ON category_table.id = catalogue_productcategory.category_id
    WHERE main_category_name = 'Computer'


The Min used to find the first purchase, and Where at the bottom used to filter 'Computer' category only. However, my work is wrong, the query returns the first 'Computer' which the customer purchase. The query should skip customers who did not purchase "Computer" on their first purchase. Are there any suggestion to this?
My educated guess:

SELECT *
FROM (
   SELECT DISTINCT ON (o.guest_email)
          o.guest_email AS "Email", o.date_purchased AS "First Purchase", mc.name AS "Category"
   FROM   order_order               o
   JOIN   order_line                l  ON l.order_id = o.id 
   JOIN   catalogue_productcategory p  ON p.product_id = l.product_id
   JOIN   catalogue_category        c  ON c.id = p.category_id
   JOIN   catalogue_category        mc ON mc.path = left(c.path, 4)
   ORDER  BY o.guest_email, o.date_purchased
   ) sub
WHERE  "Category" = 'Computer';


Or, if all those joins never exclude rows (referential integrity enforced with FK constraints), you can exclude irrelevant rows early:

SELECT o.guest_email AS "Email", o.date_purchased AS "First Purchase", mc.name AS "Category"
FROM  (
   SELECT DISTINCT ON (guest_email)
          id, guest_email, date_purchased  -- more columns?
   FROM   order_order
   ORDER  BY guest_email, date_purchased
   ) o
JOIN   order_line                l  ON l.order_id = o.id 
JOIN   catalogue_productcategory p  ON p.product_id = l.product_id
JOIN   catalogue_category        c  ON c.id = p.category_id
JOIN   catalogue_category        mc ON mc.path = left(c.path, 4)
WHERE  mc.name = 'Computer';


I largely rewrote the query and removed some cruft.

The subquery retrieves the first order per email.
The outer query filters first-timers who bought a 'Computer'.

About DISTINCT ON:


Select first row in each GROUP BY group?


Depending on your complete situation, there may be faster query techniques:


Optimize GROUP BY query to retrieve latest record per user