My question is trying to find out if a customer purchases a ski lesson for the first time is that their first time at the resort or have they been here before and purchased something other than a lesson (lift ticket, food, lodging, etc). The exact question I was asked to find out is "When someone goes to the Ski and Ride School are they new to the resort or have they been here before". I'm using a sales and customer table. The customer table is very basic, with customerkey, firstname, lastname, address, etc. A sample of the sales table is below and it has the following columns: Customerkey | Eventkey | PurchaseDate | ProductLOBSummary | Year | Amount. ![alt text] : /storage/temp/4171-screen-shot-2017-07-05-at-14628-pm.png I'm want to find out if a customer attends the ski school (productlobsummary = 'Ski and Ride School') have they made any purchases in other productlobsummaries before going to the ski school or is the ski school their first purchase at the resort. I tried doing a query where i filtered on productlobsummary='Ski and Ride School' and then looked where the purchasedate was greater than where productlobsummary <> 'Ski and ride School', which would return someone who is New to the Resort. I couldn't make that work and didn't know if i should do a subquery and if so where to put it. I'm new to SQL and I'm lost. Any help or advice would be greatly appreciated. Thanks
Hi, Below query will provide the list of customer who purchased 'Ski and Ride School' and rank of the purchase_order. in the below query result set if the purchase Order is 1 then customer purchasing first time in the resort or else he/she is not. ;With CTE as ( Select *, Rank() over (Partition by Customerkey order by Purchasedate ) as Purchased_Order from Sales ) Select * From CTE where ProductlobSummary = 'Ski and Ride School'