Dashboard Report - Consolidated Pageviews

data & reporting
, ,
1

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

This SQL report provides a daily count of pageviews from logged in users, anonymous users, and crawlers on a Discourse site.

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

SELECT
    ar.date::date AS "date",
    SUM(ar.count) AS "Total Views",
    SUM(CASE WHEN ar.req_type=6 THEN ar.count ELSE 0 END) AS "Crawler Views",
    SUM(CASE WHEN ar.req_type=7 THEN ar.count ELSE 0 END) AS "Logged in User Views",
    SUM(CASE WHEN ar.req_type=8 THEN ar.count ELSE 0 END) AS "Anonymous User Views"
FROM application_requests ar
WHERE req_type IN (6,7,8)
    AND ar.date::date BETWEEN :start_date AND :end_date
GROUP BY date
ORDER BY date

SQL Query Explanation

The query operates on the application_requests table, which logs various types of requests made to the forum. It focuses on three specific types of requests identified by req_type values:

  • 6: Crawler Views
  • 7: Logged in User Views
  • 8: Anonymous User Views

Here’s a step-by-step explanation of what the query does:

  • Parameters: The query accepts two parameters, :start_date and :end_date, allowing the user to specify the date range for the report. Both date parameters accept the date format of YYYY-MM-DD .
  • Selection: The query selects the date of the request (ar.date) and casts it to a date type. It also calculates the total views and the breakdown of views by type (crawler, logged-in user, and anonymous user) for each date within the specified range.
  • Summation: It sums up the count column for each type of view. The CASE statements are used to conditionally include counts in the sum based on the req_type.
  • Filtering: The WHERE clause filters the records to include only those with req_type values of 6, 7, or 8 and with dates falling between the specified :start_date and :end_date.
  • Grouping: The query groups the results by date, ensuring that the counts for each type of view are aggregated per day.
  • Ordering: The results are ordered by date to provide a chronological view of the traffic.

Example Results

date Total Views Crawler Views Logged in User Views Anonymous User Views
2023-11-18 5608 3052 565 1928
2023-11-19 9207 6564 470 2273
2023-11-20 6271 2631 1016 2924
3 Likes