query.sql

SELECT
    TRUNCATE(p1.reason_weight, -1) AS weight,
    COUNT(DISTINCT p2.id) / @total AS probability
FROM (
    SELECT 
        posts.id,
        SUM(reasons.weight) AS reason_weight
    FROM posts
    INNER JOIN posts_reasons ON posts.id = posts_reasons.post_id
    INNER JOIN reasons ON posts_reasons.reason_id = reasons.id
    GROUP BY posts.id
) AS p1
INNER JOIN (
    SELECT
        posts.id,
        SUM(reasons.weight) AS reason_weight
    FROM posts
    INNER JOIN posts_reasons ON posts.id = posts_reasons.post_id
    INNER JOIN reasons ON posts_reasons.reason_id = reasons.id
    GROUP BY posts.id
) AS p2 ON TRUNCATE(p2.reason_weight, -1) <= TRUNCATE(p1.reason_weight, -1)
GROUP BY TRUNCATE(p1.reason_weight, -1)
ORDER BY TRUNCATE(p1.reason_weight, -1) ASC;