CODEWITHSUNDEEP
sqlsql-server
Mohamed abdelrahman
Mohamed abdelrahman

Reputation: 25

Find related bought products (in a SQL Server database)

I have to tables, Bills and BillDetails.

The Bill table contains the following columns:

billId, salesManId, purchaseBranch

The BillDetails table contains these columns:

billDetailId , billId, productId,  purchaseBranch, Quantity

What I want is report to find top 2 related products

For example

  1. People who bought iPhone X also bought

    • Nomad Rugged Cable
    • Kenu Stance Tripod

  2. People who bought Samsung s5282 also bought:

    • Bluetooth Keyboards
    • MP3 Player

I know it is somewhat called recommendation system but I can not figure out how to implement it

Upvotes: 0

Views: 111

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

If the purchases are on the same bill, you can use a self-join and aggregation:

select bd1.productid, bd2.productid, count(*)
from billdetails bd1 join
     billdetails bd2
     on bd1.billid = bd2.billid
group by bd1.productid, bd2.productid
order by bd1.productid, count(*) desc;

For each product, this gives all related products, ordered by the frequency of appearance together on a bill.

Upvotes: 1

Related Questions