同一ORDERS表的SQL JOIN/子查询求助：特定商品复购用户查询

Hey there! Let's tackle this problem together. The goal is to find customers who've bought a specific product and have placed at least one order after their very first one. Here's a robust SQL approach that should work with your ORDERS table:

WITH CustomerFirstOrder AS (
    -- Step 1: Calculate each customer's first order date
    SELECT 
        "Customer Name",
        MIN("Order Date") AS first_order_date
    FROM ORDERS
    GROUP BY "Customer Name"
),
CustomersWithSpecificItem AS (
    -- Step 2: Get unique customers who purchased your target product (replace with actual product name)
    SELECT DISTINCT "Customer Name"
    FROM ORDERS
    WHERE "Line Item" = 'TARGET_PRODUCT'
)
-- Step 3: Filter for customers who have orders after their first purchase
SELECT DISTINCT 
    cwsi."Customer Name"
    -- Add other customer fields (like ID, email) here if they exist in your table
FROM CustomersWithSpecificItem cwsi
JOIN CustomerFirstOrder cfo ON cwsi."Customer Name" = cfo."Customer Name"
WHERE EXISTS (
    SELECT 1
    FROM ORDERS o
    WHERE o."Customer Name" = cwsi."Customer Name"
      AND o."Order Date" > cfo.first_order_date
);

Let's break down how this works: #

• CustomerFirstOrder CTE: This uses MIN("Order Date") grouped by customer name to grab the earliest purchase date for every customer—this tells us when they first shopped with you.
• CustomersWithSpecificItem CTE: This pulls a unique list of customers who've bought your target product. The DISTINCT ensures we don't count the same customer multiple times if they purchased the item repeatedly.
• Final Query: We join the two CTEs to focus only on customers who meet both criteria. The EXISTS clause checks if the customer has any order that happened after their first one—confirming they're a repeat buyer.

Quick adjustments for your use case: #

• Swap 'TARGET_PRODUCT' with the exact name of the specific item you're looking for.
• If your table has unique customer identifiers (like a Customer ID), use that instead of Customer Name to avoid matches with customers who share the same name.
• Add any additional customer fields you want to return to the final SELECT statement (just ensure they're compatible with the grouping or joins if needed).

内容的提问来源于stack exchange，提问作者Scott