Dashboard Report - Top Users by Likes Received From a Variety of People

Data & reporting
, ,
1

This is an SQL version of the Dashboard Report for Top Users by Likes Received From a Variety of People.

This dashboard report shows the top users who have received likes from a wide range of users of a site, over a specified date range. The query is designed to find out which users received the most likes on their posts, with each like count representing the number of unique users who liked their posts.

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

SELECT
    p.user_id,
    u.username,
    COUNT(DISTINCT ua.user_id) AS likes
FROM
    user_actions ua
    INNER JOIN posts p ON p.id = ua.target_post_id
    INNER JOIN users u ON p.user_id = u.id
WHERE
    ua.created_at::date BETWEEN :start_date AND :end_date
    AND ua.action_type = 1
    AND p.user_id > 0
GROUP BY
    p.user_id,
    u.username,
ORDER BY
    likes DESC

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.
  • Selection: The main columns selected for the output are:
    • p.user_id: The user ID of the poster.
    • u.username: Corresponding user’s name.
    • likes: A count of the distinct user IDs (ua.user_id) that have ‘liked’ the posts, ensuring unique likes per user are calculated.
  • Joins: There are two inner joins:
    • user_actions ua with posts p: Matches the actions to their corresponding posts.
    • posts p with users u: Connects posts to their authors, facilitating retrieval of user information.
  • Filtering:
    • The WHERE clause applies several filters:
      • ua.created_at::date BETWEEN :start_date AND :end_date limits the actions considered to those within the parameters.
      • ua.action_type = 1 isolates ‘like’ actions from other types of user actions.
      • p.user_id > 0 excludes any likes associated with non-registered users (like system or anonymous users).
  • Grouping:
    • The result set is grouped by p.user_id and u.username. This ensures that likes are aggregated per user and corresponding username.
  • Ordering:
    • Results are ordered by the count of likes (likes DESC), presenting the most appreciated users first.

Example Results

user username likes
user1 user1 100
user2 user2 90
user3 user3 80
… … …
3 Likes