Dashboard Report - Top Users by Likes Received

data & reporting
, ,
1

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

This dashboard report shows the top users on a site by the number of likes received across all posts. The report helps admins identify highly engaged users by counting likes received within a certain period, aiding in recognizing active members, assessing content quality, monitoring user engagement trends, and making informed community management decisions such as selecting moderators.

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

SELECT
ua.user_id AS user_id,
u.username as username,
COUNT(*) likes_received
FROM user_actions ua
INNER JOIN users u on ua.user_id = u.id
WHERE ua.created_at::date BETWEEN :start_date AND :end_date
AND ua.action_type = 2
GROUP BY ua.user_id, u.username, u.uploaded_avatar_id
ORDER BY likes_received DESC

SQL Query Explanation

The query works as follows:

  • 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 of Data: The query selects user IDs and usernames from the user_actions table (ua) and the users table (u), which are joined together using SQL’s INNER JOIN command on the condition that ua.user_id matches u.id.
  • Filtering Data: The WHERE clause is used to filter actions that fall within the specified date range and only considers actions of the type was_liked, which is represented by action_type = 2.
  • Aggregation: The COUNT(*) function in combination with the GROUP BY clause groups the results by user_id, username, and uploaded_avatar_id, and counts the number of likes each user has received.
  • Ordering Results: The ORDER BY clause then orders the aggregated data in a descending manner based on likes_received, so that the user with the most likes received is at the top of the report.

The query produces a list of users along with the count of likes they’ve received in the given date range, sorted from the highest to the lowest.

Example Results

user username likes_received
user1 user1 748
user2 user2 324
user3 user3 308
… … …
2 Likes