Here is the question:count how many people who buy the same item at second time and how many people who did not buy the second item at second time (compare to buying_order = 1)

We have a table:

To be more clear, if id 1 buy item 1 on 19990101, that is the first purchased item so the buying_order is 1. Our concern is know how many people buy a specific item at their first time, and how many people re-purchase that specific item at the second time.

Here are the logic I have tried, but not work

SELECT a.id FROM (SELECT id FROM table WHERE buying_order = 1) AS a 
LEFT JOIN table AS b ON a.id = b.id 
WHERE b.buying_order=2 and a.item = b.item

Expected result:

For item 1, there are two first purchase at order 1, and there is only one customer buy item 1 at order 2.

NOTE: The order can be higher than 2, such that order = 3,4,..., but we only care the people who buy or not buy the same item at their second purchase.

CodePudding user response：

Its not a hugely clear question. I would also raises questions why you even have the buying_order column, as it really doesn't seem to add value, you already have a row per user and item and also a date!

You could simply do this to count the orders, again ignoring the buying_order column altogether

CREATE TABLE #MyTable (
    UserId INT NOT NULL, 
    ItemId INT NOT NULL, 
    [Date] DATE NOT NULL, 
    BuyingOrder INT NOT NULL
);

INSERT INTO #MyTable(UserId, ItemId, [Date], BuyingOrder)
VALUES
(1, 1, '19990101', 1),
(1, 1, '19990102', 2),
(2, 1, '19990102', 1),
(2, 2, '19990101', 2),
(2, 3, '19990103', 1);

GO

SELECT UserId, ItemId, COUNT(*) NumberOfTimesBought
FROM #MyTable
GROUP BY UserId, ItemId

I was also thinking you could use the ROW_NUMBER solution as follows, which will just give you the items that have been bought more than once:

WITH T AS (
    SELECT 
    UserId, 
    ItemId,
    ROW_NUMBER() OVER(PARTITION BY UserId, ItemId ORDER BY [Date] DESC) AS RowNumber
    FROM
    #MyTable
)

SELECT UserId, ItemId
FROM T
WHERE RowNumber > 1