CODEWITHSUNDEEP
mysqlsql
CluelessGerman
CluelessGerman

Reputation: 23

Joining tables, if percentage is above certain value

My question is similar to this one: Compare rows and get percentage

However, little different. I adapted my question to the other post.

I got 2 tables.

First table:

user_id | post_id
1         1
1         2
1         3
2         12
2         15

And second table:

post_id | rating
1         1
1         2
1         3 
2         1
2         5
3         1
3         1
3         4
12        4
15        1

So now I would like to count the rating for each post, in the second table. If the rating has more than, lets say, 50% positive ratings than I want to get the post_id and going it to the post_id from table one and add 1 to the user_id.

At the end it would return the user_id with the number of positive posts.

The result for above table would be:

user_id | helpfulPosts
1         2
2         1

The post with post_id 1 and 3 have positive rating, because more than 50% have ratings of 1-3. The post with id = 2 is not positive, because the rating is exactly 50%.

How would I achieve this?

For clarification: It's a mysql rdbm and a positive post, is one where the number of rating_ids with 1, 2 and 3 are more than half of the overall rating. Basically the same thing, from the other thread I posted above.

Forgot one thing: There is also the possibility that one post_id in posts table exist, but there is no rating for it, in the ratings_table. Those posts count as helpful as well. The case with null as rating, was a misunderstanding on my side.

Upvotes: 2

Views: 109

Answers (3)

Zane Bien
Zane Bien

Reputation: 23125

Try this solution:

SELECT
    a.user_id,
    COUNT(1) AS helpfulPosts
FROM
    posts a
LEFT JOIN
    (
        SELECT 
            post_id, 
            COUNT(CASE WHEN rating IN (1,2,3) OR rating IS NULL THEN 1 END) / COUNT(1) AS percent_positive
        FROM ratings
        GROUP BY post_id
    ) b ON a.post_id = b.post_id
WHERE
    b.post_id IS NULL OR
    b.percent_positive > 0.5
GROUP BY
    a.user_id

SQL-Fiddle Demo

^ Notice I added posts to user_id 1 that has no ratings, and those are counted towards the user's helpfulPosts.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270643

To solve this, you need to first figure out which posts are helpful. Using your logic, this is just calculating the average rating, when a rating is present.

select u.user_id, count(*) as HelpfulPosts
from UserPosts u join
     (select post_id,
             sum(case when rating in (1, 2, 3) then 1.0 else 0.0 end) / count(rating) as HelpfulRating 
      from PostRating pr
      group by post_id
     ) r
     on r.post_id = u.post_id
where r.HelpfulRating > 0.5
group by user_id

The next step is to join this back to the user posts table, grouping by user id, to count the number of helpful posts.

By the way, I don't see how "3" is considered helpful. Do you mean 15 instead? The above query ignores NULL rating. If NULL should be considered as helpful, then use:

             sum(case when coalesce(rating, 1) in (1, 2, 3) then 1.0 else 0.0 end) / count(*) as HelpfulRating

instead of the version in the query.

Upvotes: 1

shawnt00
shawnt00

Reputation: 17935

select up.user_id, count(up.post_id) as helpfulPosts
from userposts as up
where up.post_id in (
    select pr.post_id
    from postratings as pr
    group by pr.post_id
    having
        sum(case when pr.rating between 4 and 5 then 0 else 1 end) > 
        sum(case when pr.rating between 4 and 5 then 1 else 0 end)
)
group by up.user_id

Upvotes: 1

Related Questions