-- For each post P1, generate a list of other posts which are recommended by the same users
-- who recommended the post P1 Ordered by frequecy.

-- Result would be something similar to

-- p1, p2, frequency, rank
-- p1, p3, frequency, rank

SELECT postId1, postId2, frequency, rank
INTO similarly_voted_posts
FROM
  (SELECT p_a.post_id postId1, p_b.post_id postId2, count(1) frequency,
          rank() OVER (PARTITION BY postId1 ORDER BY frequency DESC, postId2) rank
  FROM user_post_relations p_a
  JOIN user_post_relations p_b ON (
       p_a.user_id = p_b.user_id AND
       p_a.post_id != p_b.post_id AND
       p_a.voted_at > 0 AND
       p_b.voted_at > 0)
  GROUP BY postId1, postId2
  ORDER BY postId1, frequency DESC, postId2
  ) AS temp
WHERE rank <= 10 AND
frequency > 50