Dashboard Report - Top Users by Likes Received from a User with a Lower Trust Level

Data & reporting
, ,
1

This is an SQL version of the Dashboard Report for Top Users by Likes Received from a User with a Lower Trust Level.

This dashboard report aims to identify which users have received the most likes from members of lower trust levels within a specified timeframe. The report focuses on the interaction between users of different trust levels and highlights accounts that stand out in community engagement by being liked by those with lower permissions or experience on the forum.

--[params]
--date :start_date = 2024-01-01
--date :end_date = 2025-01-01

WITH user_liked_tl_lower AS (
    SELECT
        users.id AS user_id,
        users.username,
        users.trust_level,
        COUNT(*) AS likes,
        rank() OVER (
            PARTITION BY users.trust_level
            ORDER BY COUNT(*) DESC
        ) AS rank
    FROM users
    INNER JOIN posts p ON p.user_id = users.id
    INNER JOIN user_actions ua ON ua.target_post_id = p.id AND ua.action_type = 1
    INNER JOIN users u_liked ON ua.user_id = u_liked.id AND u_liked.trust_level < users.trust_level
    WHERE ua.created_at::date BETWEEN :start_date AND :end_date
    GROUP BY users.id
    ORDER BY trust_level DESC, likes DESC
)

SELECT * FROM user_liked_tl_lower
WHERE rank <= 10

SQL Query Explanation

  • Date Parameters:
    • The query accepts two parameters, :start_date and :end_date, which define the date range for the report. Both date parameters accept the date format of YYYY-MM-DD.
  • Common Table Expression (CTE):
    • The CTE user_liked_tl_lower is defined at the beginning for reuse within the main query.
    • It calculates the total number of likes each user has received on their posts from users with lower trust levels within the given timeframe defined by :start_date and :end_date.
  • Inner Joins:
    • Joins are performed between the users table and the posts and user_actions tables to filter out the actions that are ‘likes’ (indicated by action_type = 1).
    • An additional join to the users table (u_liked) is made to ensure that the liker has a trust level lower than the post’s author.
  • Ranking:
    • Results are partitioned by the user’s trust level and ordered by the count of likes in descending order.
    • A ranking is assigned based on the number of likes, with ties receiving the same rank and the next rank skipping numbers accordingly (this is standard ranking, not dense ranking).
  • Filtering results in CTE:
    • Only the likes that were given within the specified time period are counted.
  • Final Selection:
    • The main query selects all columns from the CTE where the rank is 10 or lower.
    • This limits the results to the top 10 users in each trust level category that have received the most likes from users of lower trust levels.

Example Results

user username trust_level likes rank
user1 user1 4 323 1
user2 user2 4 164 2
user3 user3 4 143 3
… … … …
user11 user11 3 45 1
user12 user12 3 34 2
… … … …
3 Likes