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 ofYYYY-MM-DD
. - Selection of Data: The query selects user IDs and usernames from the
user_actions
table (ua
) and theusers
table (u
), which are joined together using SQL’sINNER JOIN
command on the condition thatua.user_id
matchesu.id
. - Filtering Data: The
WHERE
clause is used to filter actions that fall within the specified date range and only considers actions of the typewas_liked
, which is represented byaction_type = 2
. - Aggregation: The
COUNT(*)
function in combination with theGROUP BY
clause groups the results byuser_id
,username
, anduploaded_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 onlikes_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 |
… | … | … |