question

Anduve11 avatar image
Anduve11 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
sql-servertsqljoinssubquery
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Prabu avatar image
Prabu answered
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'
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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?
0 Likes 0 ·
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
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.