TheUndecider
asked on
Query to Show Users who have Previously Purchased the Same Item Before
Hello,
We like to have a SQL Server query where we could see if someone purchasing an Item has it purchased (or a combo which the item was part of) before. It should not matter if they've purchased it more than once. As long as the product has been purchased, it should show up in the query.
For example, if we have these Product and Combos in the Products table:
WidgetA
WidgetB
WidgetC
ComboA = WidgetA + WidgetC
ComboB = WidgetB + WidgetC
And these two users (U229 and U223) are purchasing WidgetC right now, but one of them purchased WidgetC a year ago and the other one purchased ComboB (which WidgetC is part of) they should up in the list. On the other hand, user U221 should not be part of this query because this is the first time he's purchased WidgetA (not pictured in the following Orders record because it should not exist)
Orders Table
OrderID UserID Product PreviousDatePurchased
112 U229 WidgetC 8/1/15
454 U223 ComboB 4/29/16
This is how we'd like this query to show up the results:
UserID CurrentProduct PreviousProduct DatePurchased
U229 WidgetC WidgetC 8/1/15
U223 WidgetC ComboB 4/28/16
One of the reasons I'm having issues with this query is that we have several products and combos they can be part of and that makes it more complicated. The good thing is that I only have to worry about the users who purchased anything in the past week so I can limit by that. However, these orders could have any products.
Any help will be appreciated it.
Thanks.
We like to have a SQL Server query where we could see if someone purchasing an Item has it purchased (or a combo which the item was part of) before. It should not matter if they've purchased it more than once. As long as the product has been purchased, it should show up in the query.
For example, if we have these Product and Combos in the Products table:
WidgetA
WidgetB
WidgetC
ComboA = WidgetA + WidgetC
ComboB = WidgetB + WidgetC
And these two users (U229 and U223) are purchasing WidgetC right now, but one of them purchased WidgetC a year ago and the other one purchased ComboB (which WidgetC is part of) they should up in the list. On the other hand, user U221 should not be part of this query because this is the first time he's purchased WidgetA (not pictured in the following Orders record because it should not exist)
Orders Table
OrderID UserID Product PreviousDatePurchased
112 U229 WidgetC 8/1/15
454 U223 ComboB 4/29/16
This is how we'd like this query to show up the results:
UserID CurrentProduct PreviousProduct DatePurchased
U229 WidgetC WidgetC 8/1/15
U223 WidgetC ComboB 4/28/16
One of the reasons I'm having issues with this query is that we have several products and combos they can be part of and that makes it more complicated. The good thing is that I only have to worry about the users who purchased anything in the past week so I can limit by that. However, these orders could have any products.
Any help will be appreciated it.
Thanks.
ASKER
Hi Dustin,
Thanks for the feedback. We do have Product IDs (int) but you can use either in your response. For the Combos, there is a separate table where we can set Combo components, which also has their own ComboID (int).
Thanks.
Thanks for the feedback. We do have Product IDs (int) but you can use either in your response. For the Combos, there is a separate table where we can set Combo components, which also has their own ComboID (int).
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot Dustin, I will use this information to solve this issue.
Do you write the product as a varchar or is there a productID that you use?