gistfile1.sql

WITH subject_points AS (
	SELECT 
	subject.id subject_id,
	subject.name subject_name,
	achievement.user_id user_id,
	SUM(achievement.points) points
	FROM subjects_subject_topics subject_topic
	LEFT JOIN subjects_subject subject
	ON subject.id = subject_topic.subject_id
	LEFT JOIN subjects_topic topic
	ON topic.topic_id = subject_topic.topic_id
	LEFT JOIN achievements_achievement achievement
	ON achievement.topic_id = topic.topic_id
	WHERE points > 0
	GROUP BY subject.id, achievement.user_id
	ORDER BY points DESC
),

subject_points_rank AS (
	SELECT
	*,
	rank() OVER (PARTITION BY subject_id ORDER BY points DESC) rank,
        percent_rank() OVER (PARTITION BY subject_id ORDER BY points DESC) rank_percentage,
        COUNT(user_id) OVER (PARTITION BY subject_id) total
	FROM subject_points
)

SELECT * FROM subject_points_rank WHERE subject_id = 19 AND user_id = 75