# Dashboard Report - Top Referred Topics

**URL:** https://meta.discourse.org/t/dashboard-report-top-referred-topics/288433
**Category:** Data & reporting
**Tags:** sql-query, dashboard-reports, dashboard-sql
**Created:** [December 12, 2023, 9:39pm UTC](https://meta.discourse.org/t/dashboard-report-top-referred-topics/288433 "2023-12-12T21:39:57Z")
**Posts on this page:** 1
**Page:** 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: [December 12, 2023, 9:39pm UTC](https://meta.discourse.org/t/dashboard-report-top-referred-topics/288433/1 "2023-12-12T21:39:57Z")

</div>

This is an SQL version of the Dashboard Report for Top Referred Topics

This report will display topics that have received the most clicks from external sources.

```sql
--[params]
-- date :start_date
-- date :end_date

SELECT
    p.topic_id,
  COUNT(*) AS external_click_count
FROM incoming_links il
INNER JOIN posts p ON p.id = il.post_id
WHERE il.created_at::date BETWEEN :start_date AND :end_date
    AND p.deleted_at ISNULL
GROUP BY p.topic_id
ORDER BY external_click_count DESC

```

### How It Works

- The query selects the `topic_id` from the `posts` table and counts the number of incoming links associated with each post.
- It filters the incoming links based on the `created_at` date to fall between `:start_date` and `:end_date`.
- Only non-deleted posts are considered in the count (`p.deleted_at IS NULL`).
- Results are grouped by `topic_id` to aggregate the count of external clicks per topic.
- The list is ordered in descending order of `external_click_count` to show the topics with the most external clicks at the top.

### Example Results

| topic | external\_click\_count |
| --- | --- |
| topic 1 | 1234 |
| topic 2 | 456 |
| topic 3 | 78 |
| … | … |