TV Show Suggestion Algorithm and SQL

Question

There are users that can be referenced by an id as seen in the table below:

users:
user_id

I have attempted at a solution for keeping track of show views

tvshow_hits:
user_id|show_id

There is also a table with details in it:

tvshows:
show_id|name

But that is not required I'm open to change. I feel it isn't necessary to keep track of individual views more if they have watched it, since unlike music tv shows and movies are only watched once, and if they are watched again they don't carry any "weight".

So now I'll work backwards: The resulting page should look like this:

People Who Watch Also Watch

South Park 40%
Family Guy 20%
Something 10%

So, what I need is a query and (if needed) PHP code to

Select the shows (in order of P DESC), the amount of people who watch it compared to the other shows (that have been watched by users who have watched ) in percentage form AS P WHERE the show(s) have been watched by users who have watched LIMIT x

I hope that makes sense that is my best way of explaining it, feel free to think of another way to do what I'm trying to do. All suggestions and help welcome.

Answer 1

Not tested but something like this:

SELECT name, Count(1) AS no_users
    FROM
        tvshowhitdetails 
    WHERE
        userid IN (
            SELECT userid
               FROM
                   tvshow_hits
               WHERE
                   showid = @showid
              )
        AND
        showid <> @showid
GROUP BY
    name
ORDER BY
    no_users DESC

will give you the name of a show (tvshowhitdetails here is a view which joins your show_hits and show details table) and the number of people who have watched it. You can then get the total number of users in another query to work out your percentage.

Update
Your tvshowhitdetails view looks like this:

CREATE VIEW tvshowhitdetails
AS
SELECT     tvshow_hits.UserId, tvshow_hits.ShowId, tvshows.Name
     FROM         tvshow_hits LEFT OUTER JOIN
                  tvshows ON tvshow_hits.ShowId = tvshows.ShowId

Answer 2

Starting from the show, you can look up other users who watched that show. Then you can look up the shows that those other users watched. Group it by the other show, and calculate the percentage by dividing by the total number of watchers for the selected show.

Here's an example query:

select
    other_shows.showid as ShowId
,   COUNT(other_shows.userid) / (
        select count(*) from tvshows_hits where showid = @showid 
        ) as Percentage
from tvshows_hits other_users
inner join tvshows_hits other_shows
    on other_shows.userid = other_users.userid
    and other_shows.showid <> @showid
where other_users.showid = @showid
and other_users.userid <> @userid
group by other_shows.showid