# Dashboard Report - Consolidated Pageviews with Browser Detection

**URL:** <https://meta.discourse.org/t/dashboard-report-consolidated-pageviews-with-browser-detection/312968>\
**Category:** Data & reporting\
**Tags:** sql-query, dashboard-reports, dashboard-sql\
**Created:** [June 20, 2024, 11:22pm UTC](https://meta.discourse.org/t/dashboard-report-consolidated-pageviews-with-browser-detection/312968 "2024-06-20T23:22:48Z")\
**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:** [June 20, 2024, 11:22pm UTC](https://meta.discourse.org/t/dashboard-report-consolidated-pageviews-with-browser-detection/312968/1 "2024-06-20T23:22:48Z")

</div>

This is an SQL version of the Dashboard Report for Consolidated Pageviews with Browser Detection.

This [Data Explorer](https://meta.discourse.org/t/32566?silent=true) report provides a view of daily site pageviews with browser detection over a specified date range. The pageviews are categorized into four types:

- Logged In Browser
- Anonymous Browser
- Known Crawler
- Other pageviews

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

SELECT
  ar.date::date AS "date",
  SUM(CASE WHEN ar.req_type = 15 THEN count ELSE 0 END) AS "Logged In Browser",
  SUM(CASE WHEN ar.req_type = 13 THEN count ELSE 0 END) AS "Anonymous Browser",
  SUM(CASE WHEN ar.req_type = 6 THEN count ELSE 0 END) AS "Known Crawler",
  SUM(
    CASE WHEN ar.req_type = 8 THEN count
        WHEN ar.req_type = 7 THEN count
        WHEN ar.req_type = 13 THEN -count
        WHEN ar.req_type = 15 THEN -count
        ELSE 0
    END
  ) AS "Other pageviews"
FROM
  application_requests ar
WHERE
  ar.date::date >= :start_date
  AND ar.date <= :end_date
GROUP BY
  ar.date
ORDER BY
  ar.date ASC

```

### SQL Query Explanation

- **Parameters** :
  - `:start_date` - The start date for the report.
  - `:end_date` - The end date for the report.
  - Both date parameters accept the date format of `YYYY-MM-DD` .

- **SELECT Clause** :
  - `ar.date::date AS "date"`: Converts the `date` field to a date type and labels it as “date”.
  - `SUM(CASE WHEN ar.req_type = 15 THEN count ELSE 0 END) AS "Logged In Browser"`: Sums up the counts where `req_type` is 15, labeling the result as “Logged In Browser”.
  - `SUM(CASE WHEN ar.req_type = 13 THEN count ELSE 0 END) AS "Anonymous Browser"`: Sums up the counts where `req_type` is 13, labeling the result as “Anonymous Browser”.
  - `SUM(CASE WHEN ar.req_type = 6 THEN count ELSE 0 END) AS "Known Crawler"`: Sums up the counts where `req_type` is 6, labeling the result as “Known Crawler”.
  - `SUM(CASE WHEN ar.req_type = 8 THEN count WHEN ar.req_type = 7 THEN count WHEN ar.req_type = 13 THEN -count WHEN ar.req_type = 15 THEN -count ELSE 0 END) AS "Other pageviews"`: Sums up the counts for other types of requests, adjusting the counts for `req_type` 13 and 15 by subtracting them, and labels the result as “Other pageviews”.

- **FROM Clause** :
  - `FROM application_requests ar`: Specifies the `application_requests` table as the source of the data, aliasing it as `ar`.

- **WHERE Clause** :
  - `ar.date::date >= :start_date AND ar.date <= :end_date`: Filters the records to include only those where the `date` falls within the specified `:start_date` and `:end_date` range.

- **GROUP BY Clause** :
  - `GROUP BY ar.date`: Groups the results by the `date` field to aggregate the counts for each day.

- **ORDER BY Clause** :
  - `ORDER BY ar.date ASC`: Orders the results in ascending order by the `date` field.

### Results

The results of this query will provide a daily breakdown of pageviews categorized by the type of browser or crawler. Each row in the result set will contain:

- `date`: The specific date.
- `Logged In Browser`: The total count of pageviews from logged-in users.
- `Anonymous Browser`: The total count of pageviews from anonymous users.
- `Known Crawler`: The total count of pageviews from known crawlers.
- `Other pageviews`: The total count of other types of pageviews.

### Example Results

| date | Logged In Browser | Anonymous Browser | Known Crawler | Other pageviews |
| --- | --- | --- | --- | --- |
| 2024-05-02 | 1238 | 1085 | 2727 | 3983 |
| 2024-05-03 | 1039 | 1068 | 5718 | 3958 |
| 2024-05-04 | 572 | 628 | 3847 | 3761 |
| 2024-05-05 | 447 | 551 | 2093 | 3773 |

---

_[View the full topic](https://meta.discourse.org/t/dashboard-report-consolidated-pageviews-with-browser-detection/312968)._