CODEWITHSUNDEEP
sql-server-2008t-sql
Konrad Z.
Konrad Z.

Reputation: 1652

Getting TOP N pairs of products, that customers buy commonly

I faced such a problem of returning top N pairs of products, that customers buy in common. Let say I have following query:

SELECT c.CustomerKey, ProductAlternateKey
FROM 
    [AdventureWorksDW2008R2].[dbo].[FactInternetSales] FIS JOIN
    dbo.DimCustomer C ON C.CustomerKey = FIS.CustomerKey JOIN
    dbo.DimProduct P ON P.ProductKey = FIS.ProductKey

GROUP BY 
    C.CustomerKey, P.ProductAlternateKey

And it returns following results:

CustomerKey  | ProductAlternateKey| 
-------------+-----------+
0001         | A                  |
0001         | B                  |
0002         | A                  | 
0002         | B                  |
0002         | C                  |
0003         | B                  |
0003         | C                  |
0004         | A                  |
0004         | B                  |
0005         | A                  |
0005         | C                  |

From this query I'd like to find top 2 pairs of products that customers buy, so in my example -> (A,B) 3 occurrences and (B,C) -> 2 occurrences

Upvotes: 1

Views: 2849

Answers (2)

Jason Carter
Jason Carter

Reputation: 925

Give this a test:

declare @Orders table ( OrderID int identity, PersonID int,  Description varchar(32), ProductId int, Amount int);

insert into @Orders values (1, 'Shirt', 1, 20),(1, 'Shoes', 2, 50),(2, 'Shirt', 1, 22),(2, 'Shoes', 2, 20),(3, 'Shirt', 1, 20),(3, 'Shoes', 2, 50),(3, 'Hat',   3, 20),(4, 'Shirt', 1, 20),(5, 'Shirt', 1, 20),(5, 'Pants', 4, 30),(6, 'Shirt', 1, 20),(6, 'RunningShoes', 5, 70),(6, 'Hat', 3, 20),(7, 'Shirt', 1, 22),(7, 'Shoes', 2, 40),(7, 'Coat', 6, 80);

select
     top 2       
     I.Description,
     M.Description
from @Orders as I
inner join @Orders as C on C.ProductId = I.ProductId   and C.PersonId <> I.PersonId
inner join @Orders as M on C.PersonId = M.PersonId and C.ProductId <> M.ProductId
where 
    I.productid = 3
    and I.ProductId<>M.ProductId
group by 
    I.Description
    ,M.Description
order
    by count(2) desc

Upvotes: 1

djangojazz
djangojazz

Reputation: 13272

Use a RANK or DENSERANK function. MSDN on Rank function: http://msdn.microsoft.com/en-us/library/ms176102.aspx

Example usage with self extracting SQL:

declare @Person Table ( personID int identity, person varchar(8));

insert into @Person values ('Brett'),('Sean'),('Chad'),('Michael'),('Ray'),('Erik'),('Queyn');

declare @Orders table ( OrderID int identity, PersonID int, Desciption varchar(32), Amount int);

insert into @Orders values (1, 'Shirt', 20),(1, 'Shoes', 50),(2, 'Shirt', 22),(2, 'Shoes', 20),(3, 'Shirt', 20),(3, 'Shoes', 50),(3, 'Hat', 20),(4, 'Shirt', 20),(5, 'Shirt', 20),(5, 'Pants', 30),
(6, 'Shirt', 20),(6, 'RunningShoes', 70),(7, 'Shirt', 22),(7, 'Shoes', 40),(7, 'Coat', 80);

with a as 
    (
    Select
        person
    ,   o.Desciption
    ,   o.Amount
    ,   rank() over(partition by p.personId order by Amount) as Ranking
    ,   Dense_rank() over(partition by p.personId order by Amount) as DenseRanking
    from @Person p
        join @Orders o on p.personID = o.PersonID
    )
select *
from a 
where Ranking <= 2  -- determine top 2, 3, etc.... whatever you want.
order by person, amount

Upvotes: 1

Related Questions