SQL for Customers Who Bought This Item Also Bought

[benknight]

Hello.

We need to develop a feature very similar to Amazon's "Customers Who Bought This Item Also Bought" feature.

I'm looking for suggestions on best ways to handle this, via real-time using the actual table or a periodic process that updates a different table (e.g. nightly).

Any suggestions on how to design one or more tables would be greatly appreciated!

Thanks in advance.

 

[r937]

if a customer can purchase more than one product, and a product can be purchased by more than one customer, then the relationship table consisting of customer_id and product_id is all you need

r937.com | rudy.ca

 

[benknight]

Thanks, r937. However, what I really need help with is the actual query.

For starters, we have "members" who "read books," and we would like these members to know what other members are reading. So, we have a table called "books" which contains info about the books (unique key column: id). Then, we have a table we are considering creating called "books_read" which would contain 3 simple columns: id, user_account_id, book_id (foreign key to books.id table).

Given the above scenario, would this SQL be the most efficient way to implement this feature?

select book_id, count(*)
from books_read
where user_account_id in (select distinct user_account_id from books_read where user_account_id != 1 and book_id = 26)
and book_id != 26
group by book_id
order by 2 desc;


I'm just worried about the sub-query returning thousands of user_account_ids!

Thanks again!

 

[r937]

books_read should not have its own id

what's up with user_account_id 1?

r937.com | rudy.ca

 

[benknight]

Thanks again.

books_read.id is a "surrogate" key required by our ORM package. id "1" is simply the id of the current user that is not logged in (which in this case is my test id) -- I do this since we are trying to show members what "other" members are reading, I added that check in there but it isn't entirely necessary. Also, FYI, 26 is the current book, so in my above test query, I get something like this back:

book_id count(*)
33 2
45 2
98 2
22 1
102 1
53 1

NOTE: The reason I sort by count(*) DESC is to pick the top 5 books or so.

 

[r937]

try this --

select theother.book_id 
     , books.SongName
     , count(*) as how_many_other_times
  from books_read as this
inner
  join books_read as that
    on that.book_id = this.book_id  
   and that.user_account_id <> this.user_account_id
inner
  join books_read as theother
    on that.user_account_id = theother.user_account_id
   and that.book_id <> theother.book_id
inner
  join books 
    on books.id = theother.book_id     
 where this.book_id = 26
   and this.user_account_id = 1
group
    by theother.book_id 
     , books.SongName
order
    by how_many_other_times desc

r937.com | rudy.ca

 

[benknight]

Wow, r937!

Not only does my query generate invalid results but your query is accurate and runs lightning fast! I created 5000 test records and your query ran as follows:

108 rows in set (0.06 sec)

Here is what my (invalid) version did :-(

999 rows in set (5.65 sec)

I must say, in 15+ years of working with RDBMS/SQL, I thought had seen every type of query but you definitely topped it

Thank you, thank you, thank you

 

[r937]

apologies for SongName which should have been BookName

i adapted a previous query that i had written for music downloads ("customers who downloaded this song also downloaded...")

r937.com | rudy.ca

 

[benknight]

No worries at all... we have a column called "title", so that was an easy swap

I already tested the code thoroughly, checked it into subversion and deployed the new app.

Thanks again. Have a great weekend!