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