Design Leaderboards

[gabortodor]

We need to decide how to implement the leaderboard system. Two options:

• Query leaderboard results dynamically from existing tables when requested.
• Maintain a dedicated table that stores leaderboard stats.

[gabortodor]

Some ideas if we are going with the query option:

-- Reading sessions
SELECT
    user_id,
    session_durations
FROM (
    SELECT
        u.user_id,
        COALESCE(SUM(urs.duration), 0) AS session_durations
    FROM (
        SELECT :user_id AS user_id
        UNION
        SELECT
            CASE
                WHEN user_id =  :user_id THEN friend_id
                ELSE user_id
            END
        FROM friend
        WHERE user_id =  :user_id OR friend_id =  :user_id
    ) u
    LEFT JOIN user_reading_session urs
        ON urs.user_id = u.user_id
    GROUP BY u.user_id
) leaderboard
ORDER BY session_durations DESC;

-- Exercise sessions
SELECT
    u.user_id,
    COALESCE(SUM(urs.duration), 0) AS session_durations
FROM (
    SELECT  :user_id AS user_id
    UNION
    SELECT
        CASE
            WHEN user_id =  :user_id THEN friend_id
            ELSE user_id
        END
    FROM friend
    WHERE user_id =  :user_id OR friend_id =  :user_id
) u
LEFT JOIN user_exercise_session urs
    ON urs.user_id = u.user_id
GROUP BY u.user_id
ORDER BY session_durations DESC;

-- Listening sessions
SELECT
    u.user_id,
    COALESCE(SUM(urs.duration), 0) AS session_durations
FROM (
    SELECT  :user_id AS user_id
    UNION
    SELECT
        CASE
            WHEN user_id =  :user_id THEN friend_id
            ELSE user_id
        END
    FROM friend
    WHERE user_id =  :user_id OR friend_id =  :user_id
) u
LEFT JOIN user_listening_session urs
    ON urs.user_id = u.user_id
GROUP BY u.user_id
ORDER BY session_durations DESC;