# Dashboard Report - Top Users by Likes Received

**URL:** <https://meta.discourse.org/t/dashboard-report-top-users-by-likes-received/293714>\
**Category:** Data & reporting\
**Tags:** sql-query, dashboard-reports, dashboard-sql\
**Created:** [February 1, 2024, 12:21am UTC](https://meta.discourse.org/t/dashboard-report-top-users-by-likes-received/293714 "2024-02-01T00:21:49Z")\
**Posts on this page:** 1\
**Showing post:** 1

<div class="post-metadata">

**Author:** ![SaraDev](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/saradev/32/335139_2.png) [@SaraDev](https://meta.discourse.org/u/SaraDev)\
**Post date:** [February 1, 2024, 12:21am UTC](https://meta.discourse.org/t/dashboard-report-top-users-by-likes-received/293714/1 "2024-02-01T00:21:49Z")

</div>

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.

```sql
--[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 |
| … | … | … |

---

_[View the full topic](https://meta.discourse.org/t/dashboard-report-top-users-by-likes-received/293714)._