locked
SQL Query to get exception users RRS feed

  • Question

  • User954998566 posted

    Hi, 

    I need some ideas\help with this query

    I have a table as following : dbo.Sales

    with the following data:

    UserID             UserName                ProductID                 PuchaseDate

    1                       John Smith                   982                            1/1/2015

    2                       Miriam Ehris                 955                            5/5/2016

    3                       Ilya Conor                     386                            2/5/2014

    1                       John Satella                  985                            2/2/2015

    1                       John Satella                  983                            3/5/2013

    3                        Ilya Conor                    387                            9/3/2014

    Some items are rare and I want to detect users who buy more than one during a 2 year period.

    • 1st combination of Items rare : 981, 982, 983, 984, 985

    For Example :

    • John Satella had 2 items (982 (on 1/1/2015) and 985 on (2/2/2015) within 2 years)
    • John Satella had 2 items (985 (on 2/2/2015) and 983 on (3/5/2013) within 2 years)

    ------------

    • 2nd combination of Items rare : 385, 386, 387

    For Example :

    • Ilya Conor had 2 items (386 (on 2/5/2014) and 387 (on 9/3/2014) within 2 years)

    What SQL Query can you suggest to return these users in the general case ?

    Thanks

    Tuesday, June 14, 2016 9:37 PM

Answers

  • User941753370 posted

    Hi,

    Try:

    with CTE_RN as
    (
        select
            UserID,
            UserName,
            PurchaseDate,
            ROW_NUMBER() OVER(PARTITION BY UserID ORDER BY PurchaseDate) as RN
        from Sales
        where
            ProductID in (385, 386, 387)
    )
    
    select
        f.UserID,
        f.UserName,
        f.PurchaseDate as PurchaseDate1,
        s.PurchaseDate as PurchaseDate2
    from CTE_RN as f
    inner join CTE_RN as s
        on s.UserID = f.UserID and
           s.RN = f.RN + 1
    where
        s.PurchaseDate <= DATEADD(YEAR, 2, f.PurchaseDate)

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 15, 2016 12:01 AM