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 firsttime)

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:

id item date buying_order
1 1 19990101 1
1 1 19990102 2
2 1 19990102 1
2 2 19990101 2
2 3 19990103 1

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:

item first_purchase_customer second_purchase second_buy_other_item_count
1 2 1 1

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.

1 answer

  • answered 2022-05-07 06:15 Andrew

    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
    

    enter image description here

    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
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum