Skip to main content

Total Views by Content

This page provides a SQL query example for retrieving total view counts for content items within a specific platform and date range.

Overview​

For advanced analytics needs, you may need to query the database directly. This example shows how to retrieve the total number of views for each content item within a platform, sorted by popularity.

Prerequisites​

To use this SQL query, you'll need:

  1. Database access credentials (name, username, password, and hostname)
  2. Your Platform ID (can be found in the Platform table)
  3. The desired date range for analysis

SQL Query​

SELECT
    Contents.Title AS 'Content Title',
    COUNT(Analytics.id) AS 'Total Views'
FROM
    Contents
INNER JOIN
    Analytics ON Contents.id = Analytics.ContentId
WHERE
    Contents.PlatformId = 100 AND
    Analytics.type = 'view' AND
    Contents.createdAt BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY
    Contents.Title
ORDER BY
    'Total Views' DESC;

Query Explanation​

This query:

  1. Selects the content title and counts the number of analytics records (views)
  2. Joins the Contents and Analytics tables on the content ID
  3. Filters for a specific platform (replace 100 with your platform ID)
  4. Filters for analytics records of type 'view'
  5. Filters for content created within a specific date range (adjust as needed)
  6. Groups the results by content title
  7. Orders the results by view count in descending order (most popular first)

Notes​

  • This query provides a direct way to analyze content performance
  • Modify the date range to focus on specific time periods
  • The query can be adapted for other analytics needs by changing the selected columns, filters, or joins
  • For very large datasets, consider adding additional filters to improve query performance
  • This query requires direct database access and is typically used by platform administrators or for generating reports

Security Considerations​

  • Database credentials should be kept secure
  • Access to raw analytics data should be restricted to authorized personnel
  • Consider creating read-only database users for analytics purposes
  • For production environments, it's recommended to use a database replica to avoid impacting application performance