asked
How to designate New Customers
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][1] [1]: /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'
Thanks for your help. I think this is really close but I'm getting the customerkey multiple times with different ranks because of how often they purchase lessons. I put in a Case statement to say if Purchased_Order = 1 then 'New Customer' Else 'Returning Customer' but the problem is a customer can have a Purchased order of 1 and 20, so they show up as New and Returning. Do you know how I could filter it down to show just a distinct customer or if customerkey has multiple Purchased_orders look at the smallest value, or something like that?
Hi, i am not 100% sure about your requirement, but i assume that you wanted to find out the customer latest event(Ski and Ride School) attended is whether the new customer or old customer. Below code will do that..! ;With CTE as ( Select *, Rank() over (Partition by Customerkey order by Purchasedate ) as Purchased_Order from Sales ) Select Customerkey, eventkey, max(purchasedate) as purchasedate, ProductlobSummary, Year eventamount, Case When max(Purchased_Order) = 1 then 'New_Customer' Else 'Return_Customer' End as Purchased_Order From CTE where ProductlobSummary = 'Ski and Ride School' group by Customerkey, eventkey, ProductlobSummary, Year, eventamount
18 People are following this question.
