SQL subquery to find overlapping customers

I’m working on writing a SQL query that would allow me to find customers who bought a certain variety of a product during specific sale dates and then subsequently bought a different product within a different date range. I want to use a subquery in the from clause to search for the second group of customers within the first group of customers but I am having some issues

My main question is- inside of the subquery I have joined several different tables. I need to refer to 2 of those joined tables in the final where clause of the query. Do I need to rejoin those tables outside of the subquery? I tried exchanging o.”OrderDate” and ol.”SKU” in the last line with subq.”order date” and subq.”sku” since that is my table alias, but didn’t have any luck with that.

Am I right to be using a subquery for this?

SELECT DISTINCT subq."FirstName", subq."LastName", subq."Email"
FROM (
    SELECT DISTINCT o."FirstName", o."LastName", o."Email"
    FROM flight_export_order o
    JOIN flight_export_orderline ol
        ON o."OrderDisplayID" = ol."OrderDisplayID"
    JOIN flight_export_product p
        ON ol."SKU" = p."SKU"
    JOIN full_consultant_info fci
        ON o."ConsultantDisplayID" = fci."ConsultantDisplayID"
    WHERE p."DisplayName" LIKE '%varietal%'
        AND o."OrderDate" BETWEEN '06/26/2020' AND '07/03/2020'
) AS subq
WHERE o."OrderDate" BETWEEN '09/22/2020' AND '10/05/2020' AND ol."SKU" = 'Red312';

Answer

I think you actually want the intersection of 2 queries…

SELECT DISTINCT o."FirstName", o."LastName", o."Email"
FROM flight_export_order o
JOIN flight_export_orderline ol
ON o."OrderDisplayID" = ol."OrderDisplayID"
JOIN flight_export_product p
ON ol."SKU" = p."SKU"
JOIN full_consultant_info fci
ON o."ConsultantDisplayID" = fci."ConsultantDisplayID"
WHERE p."DisplayName" LIKE '%varietal%'
AND o."OrderDate" BETWEEN '06/26/2020' AND '07/03/2020'
INTERSECT
SELECT DISTINCT o."FirstName", o."LastName", o."Email"
FROM flight_export_order o
JOIN flight_export_orderline ol
ON o."OrderDisplayID" = ol."OrderDisplayID"
JOIN flight_export_product p
ON ol."SKU" = p."SKU"
JOIN full_consultant_info fci
ON o."ConsultantDisplayID" = fci."ConsultantDisplayID"
WHERE o."OrderDate" BETWEEN '09/22/2020' AND '10/05/2020' 
AND ol."SKU" = 'Red312';

Learn more about set operators like UNION, UNION ALL, INTERSECT and EXCEPT