This article helps admins and data teams understand how to calculate the Key Performance Indicators (KPIs) displayed in the Content dashboard using queries from the CC Data Lake.
Overview
The Content Dashboard KPIs – Query Reference provides the SQL logic behind each KPI widget in the Community Content dashboard. These queries are designed for use with the CC Data Lake, allowing community administrators, analysts, and developers to understand the KPIs.
For more information on the Content dashboard, refer to the Content Dashboard article.
KPI Query and Reference
The following section outlines each Key Performance Indicator (KPI) available in the Content dashboard, along with the corresponding SQL query used to calculate it in the CC Data Lake.
Query to Generate Community Engagement Metrics
The following SQL query generates five key metrics related to user activity in the community forum. It fetches data from topics and replies, filters out deleted or unpublished items, and calculates statistics about post creation and response behavior.
All five metrics are derived from a single query using structured steps (Common Table Expressions) for efficiency and clarity.
| KPIs | Description |
|---|---|
| Post Created | The number of topics and replies posted in the community within the chosen timeframe. |
| Topics Created | The number of topics posted in the community within the chosen timeframe. |
| Replies Created | The number of replies posted within the chosen timeframe, regardless of when the topic was created. |
| Single User Topics | The number of topics in the selected time there only one user-contributed content. Replies from the topic starter do not count. |
| Average Response Time | The average amount of time between the creation of a topic and its first reply. Replies from topic starters do not count. |
WITH _topic_cache AS (
SELECT topic_public_id, author_id, published_at AS created_at, content_type, topic_private_id, title,
category_id
FROM "community-id".topic
WHERE content_type IN ('productUpdate', 'article')
AND published_at IS NOT NULL
AND trashed_at IS NULL
UNION ALL
SELECT topic_public_id, author_id, created_at, content_type, topic_private_id, title, category_id
FROM "community-id".topic
WHERE content_type IN ('question', 'discussion', 'idea')
AND trashed_at IS NULL
AND created_at IS NOT NULL
),
_topic_reply_cache AS (
SELECT
replied_at AS created_at,
topic_reply.topic_public_id,
topic_reply.topic_private_id,
topic_reply.reply_public_id,
topic_reply.reply_private_id,
topic_reply.author_id,
topic_reply.author_id AS user_id
FROM "community-id".topic_reply
JOIN _topic_cache ON topic_reply.topic_public_id = _topic_cache.topic_public_id AND trashed_at IS NULL
),
reply_stats AS (
SELECT
replies.topic_public_id,
MIN(replies.created_at) AS min_response_timestamp,
MIN(replies.created_at) FILTER (WHERE replies.author_id != topics.author_id) AS min_response_timestamp_by_other_users,
array_agg(DISTINCT replies.author_id) FILTER (WHERE replies.author_id IS NOT NULL) AS authors
FROM _topic_reply_cache AS replies
JOIN _topic_cache AS topics ON topics.topic_public_id = replies.topic_public_id
GROUP BY replies.topic_public_id
),
post_created_stats AS (
SELECT
'post_created-topic-' || topic.topic_public_id || '-' || COALESCE(topic.author_id, 'anonymous') AS event_id,
cast(date(topic.created_at) AS date) AS activity_date,
'post_created' AS event_name,
'topic' AS entity_type,
created_at AS activity_timestamp,
topic.author_id AS author_id,
topic.author_id AS user_id,
topic.topic_public_id AS topic_public_id,
topic.topic_private_id AS topic_private_id,
NULL AS reply_public_id,
NULL AS reply_private_id,
reply_stats.min_response_timestamp_by_other_users AS first_response_timestamp,
COALESCE(CARDINALITY(COALESCE(reply_stats.authors, ARRAY[])) = 0 OR reply_stats.authors = ARRAY[topic.author_id], FALSE) AS single_user_topic
FROM _topic_cache AS topic
LEFT JOIN reply_stats ON topic.topic_public_id = reply_stats.topic_public_id
UNION ALL
SELECT
'post_created-reply-' || replies.topic_public_id || '-' || replies.reply_public_id || '-' || COALESCE(replies.author_id, 'anonymous') AS event_id,
cast(date(replies.created_at) AS date) AS activity_date,
'post_created' AS event_name,
'reply' AS entity_type,
replies.created_at AS activity_timestamp,
replies.author_id AS author_id,
replies.author_id AS user_id,
replies.topic_public_id AS topic_public_id,
replies.topic_private_id AS topic_private_id,
replies.reply_public_id AS reply_public_id,
replies.reply_private_id AS reply_private_id,
NULL AS first_response_timestamp,
NULL AS single_user_topic
FROM _topic_reply_cache AS replies
)
SELECT
COUNT(1) as posts_created,
COUNT(CASE WHEN entity_type = 'reply' THEN 1 ELSE NULL END) AS replies_created,
COUNT(CASE WHEN entity_type = 'topic' THEN 1 ELSE NULL END) AS topics_created,
COUNT(CASE WHEN single_user_topic THEN 1 ELSE NULL END) as single_user_topic,
AVG(TO_UNIXTIME(first_response_timestamp) - TO_UNIXTIME(activity_timestamp)) as average_response_time
from post_created_stats
where activity_date BETWEEN DATE(''YYYY-MM-DD') and DATE('YYYY-MM-DD')Likes Received
The number of likes received on topics or replies during the selected range, regardless of the original creation dates of those topics or replies.
WITH _likes as (
SELECT
'like-topic-' || topic_public_id || '-' || liked_by AS event_id,
cast(liked_at AS date) AS activity_date,
'like' AS event_name,
'topic' AS entity_type,
liked_at AS activity_timestamp,
author_id AS author_id,
liked_by AS user_id,
topic_public_id AS topic_public_id,
topic_private_id AS topic_private_id,
NULL AS reply_public_id,
NULL AS reply_private_id
FROM "community-id".likes
WHERE reply_public_id IS NULL
UNION ALL
SELECT
'like-reply-' || '-' || topic_public_id || '-' || reply_public_id || '-' || liked_by AS event_id,
cast(liked_at AS date) AS activity_date,
'like' AS event_name,
'reply' AS entity_type,
liked_at AS activity_timestamp,
author_id AS author_id,
liked_by AS user_id,
topic_public_id AS topic_public_id,
topic_private_id AS topic_private_id,
reply_public_id AS reply_public_id,
reply_private_id AS reply_private_id
FROM "community-id".likes
WHERE reply_public_id IS NOT NULL
)
SELECT COUNT(1) AS likes
from _likes
where activity_date BETWEEN DATE('YYYY-MM-DD') and DATE('YYYY-MM-DD')Positive Content Helpfulness Votes
The count of positive votes for content helpfulness assigned to topics within the chosen timeframe, irrespective of when the topic was initially created.
WITH _topic_cache AS (
SELECT topic_public_id, author_id, published_at AS created_at, content_type, topic_private_id, title, category_id
FROM "community-id".topic
WHERE content_type IN ('productUpdate', 'article')
AND published_at IS NOT NULL
AND trashed_at IS NULL
UNION ALL
SELECT topic_public_id, author_id, created_at, content_type, topic_private_id, title, category_id
FROM "community-id".topic
WHERE content_type IN ('question', 'discussion', 'idea')
AND trashed_at IS NULL
AND created_at IS NOT NULL
),
helpfulness as (
SELECT
'helpfulness-helpful-' || topic.topic_public_id || '-'
|| COALESCE(helpfulness_topic_helpful.user_id, 'guest')
|| '-'
|| format('%.0f', 1000 * to_unixtime(from_iso8601_timestamp(occurred_at)))
|| '-'
|| CAST(ROW_NUMBER() OVER (PARTITION BY topic.topic_public_id, helpfulness_topic_helpful.user_id ORDER BY from_iso8601_timestamp(occurred_at)) AS VARCHAR)
AS event_id,
DATE(from_iso8601_timestamp(occurred_at)) AS activity_date,
'helpfulness_helpful' AS event_name,
'topic' AS entity_type,
from_iso8601_timestamp(occurred_at) AS activity_timestamp,
topic.author_id AS author_id,
helpfulness_topic_helpful.user_id AS user_id,
topic.topic_public_id AS topic_public_id,
topic.topic_private_id AS topic_private_id
FROM
"community-id".helpfulness_topic_helpful
JOIN _topic_cache AS topic ON topic.topic_public_id = helpfulness_topic_helpful.topic_public_id
)
SELECT COUNT(1) AS positive_content_helpfulness_votes
from helpfulness
where activity_date BETWEEN DATE('YYYY-MM-DD') and DATE('YYYY-MM-DD')Popular Topics
The most popular topics, sorted by the number of views, replies, likes, or content helpfulness score. Users can choose to sort the list based on their preferred criteria, allowing for easy identification of trending topics aligned with user interests and engagement levels.
WITH _user as (
SELECT
"community-id".user.user_id,
"community-id".user_role.role_id
FROM
"community-id".user
join "community-id".user_role on "user".user_id = "community-id".user_role.user_id
join "community-id".role on "community-id".user_role.role_id = "community-id".role.role_id
and primary_role = 1
where
role.name not in (
'roles.requires-approval', 'roles.not-activated',
'roles.super-admin', 'roles.banned',
'roles.guest', 'roles.deleted'
)
),
_topic_cache AS (
SELECT
topic_public_id,
author_id,
published_at AS created_at,
content_type,
topic_private_id,
title,
category_id
FROM
"community-id".topic
WHERE
content_type IN ('productUpdate', 'article')
AND published_at IS NOT NULL
AND trashed_at IS NULL
UNION ALL
SELECT
topic_public_id,
author_id,
created_at,
content_type,
topic_private_id,
title,
category_id
FROM
"community-id".topic
WHERE
content_type IN ('question', 'discussion', 'idea')
AND trashed_at IS NULL
AND created_at IS NOT NULL
),
_topic_reply_cache AS (
SELECT
replied_at AS created_at,
topic_reply.topic_public_id,
topic_reply.topic_private_id,
topic_reply.reply_public_id,
topic_reply.reply_private_id,
topic_reply.author_id,
topic_reply.author_id AS user_id,
_topic_cache.title,
_topic_cache.content_type,
_topic_cache.category_id
FROM
"community-id".topic_reply
JOIN _topic_cache ON topic_reply.topic_public_id = _topic_cache.topic_public_id
AND topic_reply.trashed_at IS NULL
),
post_created as (
SELECT
'post_created-topic-' || topic.topic_public_id || '-' || COALESCE(topic.author_id, 'anonymous') AS event_id,
cast(
to_iso8601(
CAST(topic.created_at AS DATE)
) AS varchar
) AS activity_date,
'post_created' AS event_name,
'topic' AS entity_type,
topic.title,
topic.content_type,
topic.category_id,
CAST(
to_iso8601(
topic.created_at AT TIME ZONE 'UTC'
) AS varchar
) AS activity_timestamp,
CAST(topic.author_id AS varchar) AS author_id,
CAST(topic.author_id AS varchar) AS user_id,
CAST(topic.topic_public_id AS varchar) AS topic_public_id,
CAST(
topic.topic_private_id AS varchar
) AS topic_private_id,
CAST(NULL AS varchar) AS reply_public_id,
CAST(NULL AS varchar) AS reply_private_id
FROM
_topic_cache AS topic
UNION ALL
SELECT
'post_created-reply-' || replies.topic_public_id || '-' || replies.reply_public_id || '-' || COALESCE(replies.author_id, 'anonymous') AS event_id,
cast(
to_iso8601(
CAST(replies.created_at AS DATE)
) AS varchar
) AS activity_date,
'post_created' AS event_name,
'reply' AS entity_type,
replies.title,
replies.content_type,
replies.category_id,
CAST(
to_iso8601(
replies.created_at AT TIME ZONE 'UTC'
) AS varchar
) AS activity_timestamp,
CAST(replies.author_id AS varchar) AS author_id,
CAST(replies.author_id AS varchar) AS user_id,
CAST(
replies.topic_public_id AS varchar
) AS topic_public_id,
CAST(
replies.topic_private_id AS varchar
) AS topic_private_id,
CAST(
replies.reply_public_id AS varchar
) AS reply_public_id,
CAST(
replies.reply_private_id AS varchar
) AS reply_private_id
FROM
_topic_reply_cache AS replies
),
helpfulness as (
SELECT
'helpfulness-helpful-' || topic.topic_public_id || '-' || COALESCE(
helpfulness_topic_helpful.user_id,
'guest'
) || '-' || format(
'%.0f',
1000 * to_unixtime(
from_iso8601_timestamp(occurred_at)
)
) || '-' || CAST(
ROW_NUMBER() OVER (
PARTITION BY topic.topic_public_id,
helpfulness_topic_helpful.user_id
ORDER BY
from_iso8601_timestamp(occurred_at)
) AS VARCHAR
) AS event_id,
cast(
to_iso8601(
cast(
from_iso8601_timestamp(occurred_at) as date
)
) AS varchar
) AS activity_date,
'helpfulness_helpful' AS event_name,
'topic' AS entity_type,
topic.title,
topic.content_type,
topic.category_id,
cast(
to_iso8601(
from_iso8601_timestamp(occurred_at)
) AS varchar
) AS activity_timestamp,
CAST(topic.author_id AS varchar) AS author_id,
CAST(topic.author_id AS varchar) AS user_id,
CAST(topic.topic_public_id AS varchar) AS topic_public_id,
CAST(
topic.topic_private_id AS varchar
) AS topic_private_id,
CAST(NULL AS varchar) AS reply_public_id,
CAST(NULL AS varchar) AS reply_private_id
FROM
"community-id".helpfulness_topic_helpful
JOIN _topic_cache AS topic ON topic.topic_public_id = helpfulness_topic_helpful.topic_public_id
UNION ALL
SELECT
'helpfulness-neutral-' || topic.topic_public_id || '-' || COALESCE(
helpfulness_topic_neutral.user_id,
'guest'
) || '-' || format(
'%.0f',
1000 * to_unixtime(
from_iso8601_timestamp(occurred_at)
)
) || '-' || CAST(
ROW_NUMBER() OVER (
PARTITION BY topic.topic_public_id,
helpfulness_topic_neutral.user_id
ORDER BY
from_iso8601_timestamp(occurred_at)
) AS VARCHAR
) AS event_id,
cast(
to_iso8601(
cast(
from_iso8601_timestamp(occurred_at) as date
)
) AS varchar
) AS activity_date,
'helpfulness_neutral' AS event_name,
'topic' AS entity_type,
topic.title,
topic.content_type,
topic.category_id,
cast(
to_iso8601(
from_iso8601_timestamp(occurred_at)
) AS varchar
) AS activity_timestamp,
CAST(topic.author_id AS varchar) AS author_id,
CAST(topic.author_id AS varchar) AS user_id,
CAST(topic.topic_public_id AS varchar) AS topic_public_id,
CAST(
topic.topic_private_id AS varchar
) AS topic_private_id,
CAST(NULL AS varchar) AS reply_public_id,
CAST(NULL AS varchar) AS reply_private_id
FROM
"community-id".helpfulness_topic_neutral
JOIN _topic_cache AS topic ON topic.topic_public_id = helpfulness_topic_neutral.topic_public_id
UNION ALL
SELECT
'helpfulness-not_helpful-' || topic.topic_public_id || '-' || COALESCE(
helpfulness_topic_not_helpful.user_id,
'guest'
) || '-' || format(
'%.0f',
1000 * to_unixtime(
from_iso8601_timestamp(occurred_at)
)
) || '-' || CAST(
ROW_NUMBER() OVER (
PARTITION BY topic.topic_public_id,
helpfulness_topic_not_helpful.user_id
ORDER BY
from_iso8601_timestamp(occurred_at)
) AS VARCHAR
) AS event_id,
cast(
to_iso8601(
CAST(
from_iso8601_timestamp(occurred_at) AS DATE
)
) AS varchar
) AS activity_date,
'helpfulness_not_helpful' AS event_name,
'topic' AS entity_type,
topic.title,
topic.content_type,
topic.category_id,
cast(
to_iso8601(
from_iso8601_timestamp(occurred_at)
) AS varchar
) AS activity_timestamp,
CAST(topic.author_id AS varchar) AS author_id,
CAST(topic.author_id AS varchar) AS user_id,
CAST(topic.topic_public_id AS varchar) AS topic_public_id,
CAST(
topic.topic_private_id AS varchar
) AS topic_private_id,
CAST(NULL AS varchar) AS reply_public_id,
CAST(NULL AS varchar) AS reply_private_id
FROM
"community-id".helpfulness_topic_not_helpful
JOIN _topic_cache AS topic ON topic.topic_public_id = helpfulness_topic_not_helpful.topic_public_id
),
_pageview_cache AS (
SELECT
occurred_at,
topic_public_id,
reply_public_id,
user_id,
visitor_id,
ROW_NUMBER() OVER (
PARTITION BY visitor_id
ORDER BY
from_iso8601_timestamp(occurred_at) NULLS LAST,
user_id,
topic_public_id,
reply_public_id
) AS row_num
FROM
"community-id".pageview
),
visitors as (
SELECT
'view-topic-' || cast(topic.topic_public_id AS varchar) || '-' || cast(
COALESCE(
pageview.user_id,
CONCAT(
'guest',
COALESCE('-' || pageview.visitor_id, '')
)
) AS varchar
) || '-' || format(
'%.0f',
1000 * to_unixtime(
from_iso8601_timestamp(occurred_at)
)
)|| '-' || CAST(row_num as varchar) AS event_id,
cast(
to_iso8601(
CAST(
from_iso8601_timestamp(occurred_at) AS DATE
)
) AS varchar
) AS activity_date,
'view' AS event_name,
'topic' AS entity_type,
topic.title,
topic.content_type,
topic.category_id,
CAST(
to_iso8601(
from_iso8601_timestamp(occurred_at)
) AS varchar
) AS activity_timestamp,
CAST(topic.author_id AS varchar) AS author_id,
CAST(pageview.user_id AS varchar) AS user_id,
CAST(topic.topic_public_id AS varchar) AS topic_public_id,
CAST(
topic.topic_private_id AS varchar
) AS topic_private_id,
CAST(NULL AS varchar) AS reply_public_id,
CAST(NULL AS varchar) AS reply_private_id
FROM
_pageview_cache AS pageview
JOIN _topic_cache AS topic ON pageview.topic_public_id = topic.topic_public_id
WHERE
pageview.topic_public_id IS NOT NULL
UNION ALL
SELECT
'view-reply-' || CAST(
replies.topic_public_id AS varchar
) || '-' || CAST(
replies.reply_public_id AS varchar
) || '-' || cast(
COALESCE(
pageview.user_id,
CONCAT(
'guest',
COALESCE('-' || pageview.visitor_id, '')
)
) AS varchar
) || '-' || format(
'%.0f',
1000 * to_unixtime(
from_iso8601_timestamp(occurred_at)
)
)|| '-' || CAST(row_num as varchar) AS event_id,
cast(
to_iso8601(
CAST(
from_iso8601_timestamp(occurred_at) AS DATE
)
) AS varchar
) AS activity_date,
'view' AS event_name,
'reply' AS entity_type,
replies.title,
replies.content_type,
replies.category_id,
CAST(
to_iso8601(
from_iso8601_timestamp(occurred_at)
) AS varchar
) AS activity_timestamp,
CAST(replies.author_id AS varchar) AS author_id,
CAST(pageview.user_id AS varchar) AS user_id,
CAST(
replies.topic_public_id AS varchar
) AS topic_public_id,
CAST(
replies.topic_private_id AS varchar
) AS topic_private_id,
CAST(
replies.reply_public_id AS varchar
) AS reply_public_id,
CAST(
replies.reply_private_id AS varchar
) AS reply_private_id
FROM
_pageview_cache AS pageview
JOIN _topic_reply_cache AS replies ON pageview.reply_public_id = replies.reply_public_id
WHERE
pageview.reply_public_id IS NOT NULL
),
likes_received as (
SELECT
'like-topic-' || CAST(likes.topic_public_id as varchar) || '-' || CAST(likes.liked_by as varchar) AS event_id,
CAST(
to_iso8601(
CAST(likes.liked_at AS DATE)
) AS varchar
) AS activity_date,
'like' AS event_name,
'topic' AS entity_type,
topic.title,
topic.content_type,
topic.category_id,
CAST(
to_iso8601(likes.liked_at AT TIME ZONE 'UTC') AS varchar
) AS activity_timestamp,
CAST(likes.author_id AS varchar) AS user_id,
CAST(likes.author_id AS varchar) AS user_id,
CAST(likes.topic_public_id AS varchar) AS topic_public_id,
CAST(
likes.topic_private_id AS varchar
) AS topic_private_id,
CAST(NULL AS varchar) AS reply_public_id,
CAST(NULL AS varchar) AS reply_private_id
FROM
"community-id".likes
JOIN _topic_cache AS topic ON likes.topic_public_id = topic.topic_public_id
WHERE
likes.reply_public_id IS NULL
UNION ALL
SELECT
'like-reply-' || '-' || CAST(likes.topic_public_id as varchar) || '-' || CAST(likes.reply_public_id as varchar) || '-' || CAST(likes.liked_by as varchar) AS event_id,
CAST(
to_iso8601(
CAST(likes.liked_at AS DATE)
) AS varchar
) AS activity_date,
'like' AS event_name,
'reply' AS entity_type,
topic.title,
topic.content_type,
topic.category_id,
CAST(
to_iso8601(likes.liked_at AT TIME ZONE 'UTC') AS varchar
) AS activity_timestamp,
CAST(likes.author_id AS varchar) AS author_id,
CAST(likes.author_id AS varchar) AS user_id,
CAST(likes.topic_public_id AS varchar) AS topic_public_id,
CAST(
likes.topic_private_id AS varchar
) AS topic_private_id,
CAST(likes.reply_public_id AS varchar) AS reply_public_id,
CAST(
likes.reply_private_id AS varchar
) AS reply_private_id
FROM
"community-id".likes
JOIN _topic_cache AS topic ON likes.topic_public_id = topic.topic_public_id
WHERE
likes.reply_public_id IS NOT NULL
),
union_table AS (
SELECT
*
FROM
post_created
UNION ALL
SELECT
*
FROM
helpfulness
UNION ALL
SELECT
*
FROM
visitors
UNION ALL
SELECT
*
FROM
likes_received
),
union_table_with_user_roles as (
SELECT
union_table.activity_date,
union_table.topic_public_id,
union_table.title,
union_table.category_id,
union_table.content_type,
union_table.topic_private_id,
union_table.entity_type,
union_table.event_name,
_user.role_id,
_user.user_id
FROM
union_table
LEFT JOIN _user on union_table.user_id = _user.user_id
),
popular_topics as (
SELECT
activity_date as date,
title,
content_type,
topic_public_id,
topic_private_id,
COUNT(
CASE WHEN event_name = 'post_created'
and entity_type = 'reply' THEN 1 END
) "replies",
COUNT(
CASE WHEN event_name = 'view'
and entity_type = 'topic' THEN 1 END
) "views",
COUNT(
CASE WHEN event_name = 'helpfulness_helpful' THEN 1 END
) "helpful",
COUNT(
CASE WHEN event_name = 'helpfulness_neutral' THEN 1 END
) "neutral",
COUNT(
CASE WHEN event_name = 'helpfulness_not_helpful' THEN 1 END
) "not_helpful",
COUNT(
CASE WHEN event_name = 'like' THEN 1 END
) "likes_received",
category_id,
user_id,
role_id
FROM
union_table_with_user_roles
group by
activity_date,
topic_public_id,
title,
content_type,
topic_private_id,
category_id,
role_id,
user_id
)
SELECT
title,
content_type,
topic_private_id,
SUM(replies) as replies,
SUM(views) as views,
SUM(helpful) as helpful,
SUM(neutral) as neutral,
SUM(not_helpful) as not_helpful,
SUM(likes_received) as likes_received
FROM
popular_topics
where
DATE(date) between DATE 'YYYY-MM-DD'
and DATE 'YYYY-MM-DD'
GROUP BY
topic_public_id,
title,
content_type,
topic_private_id
ORDER BY
views DESC,
replies DESC,
likes_received DESC,
helpful DESC,
title
LIMIT
100
Popular Categories
The most popular categories are sorted by the number of views, posts, topic replies, and likes. Users can choose to sort the list based on their preferred criteria, allowing for easy identification of trending topics aligned with user interests and engagement levels.
WITH _pageview_cache AS (
SELECT
occurred_at,
topic_public_id,
reply_public_id,
user_id,
visitor_id,
ROW_NUMBER() OVER (PARTITION BY visitor_id ORDER BY from_iso8601_timestamp(occurred_at) NULLS LAST, user_id, topic_public_id, reply_public_id) AS row_num
FROM ""community-id".pageview
),
_topic_cache AS (
SELECT topic_public_id, author_id, published_at AS created_at, content_type, topic_private_id, title,
category_id
FROM ""community-id".topic
WHERE content_type IN ('productUpdate', 'article')
AND published_at IS NOT NULL
AND trashed_at IS NULL
UNION ALL
SELECT topic_public_id, author_id, created_at, content_type, topic_private_id, title, category_id
FROM ""community-id".topic
WHERE content_type IN ('question', 'discussion', 'idea')
AND trashed_at IS NULL
AND created_at IS NOT NULL
),
_topic_reply_cache AS (
SELECT
replied_at AS created_at,
topic_reply.topic_public_id,
topic_reply.topic_private_id,
topic_reply.reply_public_id,
topic_reply.reply_private_id,
topic_reply.author_id,
topic_reply.author_id AS user_id
FROM ""community-id".topic_reply
JOIN _topic_cache ON topic_reply.topic_public_id = _topic_cache.topic_public_id AND trashed_at IS NULL
),
post_created_stats AS (
SELECT
'post_created-topic-' || topic.topic_public_id || '-' || COALESCE(topic.author_id, 'anonymous') AS event_id,
date(topic.created_at) AS activity_date,
'post_created' AS event_name,
'topic' AS entity_type,
created_at AS activity_timestamp,
topic.author_id AS author_id,
topic.author_id AS user_id,
topic.topic_public_id AS topic_public_id,
topic.topic_private_id AS topic_private_id,
NULL AS reply_public_id,
NULL AS reply_private_id
FROM _topic_cache AS topic
UNION ALL
SELECT
'post_created-reply-' || replies.topic_public_id || '-' || replies.reply_public_id || '-' || COALESCE(replies.author_id, 'anonymous') AS event_id,
date(replies.created_at) AS activity_date,
'post_created' AS event_name,
'reply' AS entity_type,
replies.created_at AS activity_timestamp,
replies.author_id AS author_id,
replies.author_id AS user_id,
replies.topic_public_id AS topic_public_id,
replies.topic_private_id AS topic_private_id,
replies.reply_public_id AS reply_public_id,
replies.reply_private_id AS reply_private_id
FROM _topic_reply_cache AS replies
),
like_stats as (
SELECT
'like-topic-' || topic_public_id || '-' || liked_by AS event_id,
cast(liked_at AS date) AS activity_date,
'like' AS event_name,
'topic' AS entity_type,
liked_at AS activity_timestamp,
author_id AS author_id,
liked_by AS user_id,
topic_public_id AS topic_public_id,
topic_private_id AS topic_private_id,
NULL AS reply_public_id,
NULL AS reply_private_id
FROM ""community-id".likes
WHERE reply_public_id IS NULL
UNION ALL
SELECT
'like-reply-' || '-' || topic_public_id || '-' || reply_public_id || '-' || liked_by AS event_id,
cast(liked_at AS date) AS activity_date,
'like' AS event_name,
'reply' AS entity_type,
liked_at AS activity_timestamp,
author_id AS author_id,
liked_by AS user_id,
topic_public_id AS topic_public_id,
topic_private_id AS topic_private_id,
reply_public_id AS reply_public_id,
reply_private_id AS reply_private_id
FROM ""community-id".likes
WHERE reply_public_id IS NOT NULL
),
helpfulness as (
SELECT
'helpfulness-helpful-' || topic.topic_public_id || '-'
|| COALESCE(helpfulness_topic_helpful.user_id, 'guest')
|| '-'
|| format('%.0f', 1000 * to_unixtime(from_iso8601_timestamp(occurred_at)))
|| '-'
|| CAST(ROW_NUMBER() OVER (PARTITION BY topic.topic_public_id, helpfulness_topic_helpful.user_id ORDER BY from_iso8601_timestamp(occurred_at)) AS VARCHAR)
AS event_id,
DATE(from_iso8601_timestamp(occurred_at)) AS activity_date,
'helpfulness_helpful' AS event_name,
'topic' AS entity_type,
from_iso8601_timestamp(occurred_at) AS activity_timestamp,
topic.author_id AS author_id,
topic.author_id AS user_id,
topic.topic_public_id AS topic_public_id,
topic.topic_private_id AS topic_private_id,
NULL AS reply_public_id,
NULL AS reply_private_id
FROM
""community-id".helpfulness_topic_helpful
JOIN _topic_cache AS topic ON topic.topic_public_id = helpfulness_topic_helpful.topic_public_id
UNION ALL
SELECT 'helpfulness-neutral-' || topic.topic_public_id || '-'
|| COALESCE(helpfulness_topic_neutral.user_id, 'guest')
|| '-'
|| format('%.0f', 1000 * to_unixtime(from_iso8601_timestamp(occurred_at)))
|| '-'
|| CAST(ROW_NUMBER() OVER (PARTITION BY topic.topic_public_id, helpfulness_topic_neutral.user_id ORDER BY from_iso8601_timestamp(occurred_at)) AS VARCHAR)
AS event_id,
DATE(from_iso8601_timestamp(occurred_at)) AS activity_date,
'helpfulness_neutral' AS event_name,
'topic' AS entity_type,
from_iso8601_timestamp(occurred_at) AS activity_timestamp,
topic.author_id AS author_id,
topic.author_id AS user_id,
topic.topic_public_id AS topic_public_id,
topic.topic_private_id AS topic_private_id,
NULL AS reply_public_id,
NULL AS reply_private_id
FROM
""community-id".helpfulness_topic_neutral
JOIN _topic_cache AS topic ON topic.topic_public_id = helpfulness_topic_neutral.topic_public_id
UNION ALL
SELECT 'helpfulness-not_helpful-' || topic.topic_public_id || '-'
|| COALESCE(helpfulness_topic_not_helpful.user_id, 'guest')
|| '-'
|| format('%.0f', 1000 * to_unixtime(from_iso8601_timestamp(occurred_at)))
|| '-'
|| CAST(ROW_NUMBER() OVER (PARTITION BY topic.topic_public_id, helpfulness_topic_not_helpful.user_id ORDER BY from_iso8601_timestamp(occurred_at)) AS VARCHAR)
AS event_id,
DATE(from_iso8601_timestamp(occurred_at)) AS activity_date,
'helpfulness_not_helpful' AS event_name,
'topic' AS entity_type,
from_iso8601_timestamp(occurred_at) AS activity_timestamp,
topic.author_id AS author_id,
topic.author_id AS user_id,
topic.topic_public_id AS topic_public_id,
topic.topic_private_id AS topic_private_id,
NULL AS reply_public_id,
NULL AS reply_private_id
FROM
""community-id".helpfulness_topic_not_helpful
JOIN _topic_cache AS topic ON topic.topic_public_id = helpfulness_topic_not_helpful.topic_public_id
),
post_views as (
SELECT
'view-topic-' || topic.topic_public_id
|| '-' || COALESCE(pageview.user_id, 'guest') || '-'
|| format('%.0f', 1000 * to_unixtime(from_iso8601_timestamp(occurred_at)))
|| '-' || CAST(row_num AS VARCHAR) AS event_id,
date(from_iso8601_timestamp(occurred_at)) AS activity_date,
'view' AS event_name,
'topic' AS entity_type,
from_iso8601_timestamp(occurred_at) AS activity_timestamp,
topic.author_id AS author_id,
pageview.user_id AS user_id,
topic.topic_public_id AS topic_public_id,
topic.topic_private_id AS topic_private_id,
NULL AS reply_public_id,
NULL AS reply_private_id
FROM _pageview_cache AS pageview
JOIN _topic_cache AS topic ON pageview.topic_public_id = topic.topic_public_id
UNION ALL
SELECT
'view-reply-' || replies.topic_public_id || '-'
|| replies.reply_public_id || '-' || COALESCE(pageview.user_id, 'guest')
|| format('%.0f', 1000 * to_unixtime(from_iso8601_timestamp(occurred_at)))
|| '-' || CAST(row_num AS VARCHAR) AS event_id,
date(from_iso8601_timestamp(occurred_at)) AS activity_date,
'view' AS event_name,
'reply' AS entity_type,
from_iso8601_timestamp(occurred_at) AS activity_timestamp,
replies.author_id AS author_id,
pageview.user_id AS user_id,
replies.topic_public_id AS topic_public_id,
replies.topic_private_id AS topic_private_id,
replies.reply_public_id AS reply_public_id,
replies.reply_private_id AS reply_private_id
FROM _pageview_cache AS pageview
JOIN _topic_reply_cache AS replies ON pageview.reply_public_id = replies.reply_public_id
),
activities as (
SELECT * FROM post_views
UNION ALL
SELECT * FROM post_created_stats
UNION ALL
SELECT * FROM like_stats
UNION ALL
SELECT * FROM helpfulness
),
_user as (
select ""community-id".user.user_id, ""community-id".user_role.role_id
from ""community-id".user
join ""community-id".user_role on "user".user_id = ""community-id".user_role.user_id
join ""community-id".role on ""community-id".user_role.role_id = ""community-id".role.role_id and primary_role = 1
where role.name not in ('roles.requires-approval', 'roles.not-activated', 'roles.super-admin', 'roles.banned', 'roles.guest', 'roles.deleted')
),
_category_and_group as (
select category_id as category_or_group_id, title as category_or_group_name from ""community-id".category
union all
select group_id as category_or_group_id, name as category_or_group_name from ""community-id"."group"
),
topic_categories as (
select topic_public_id, category_or_group_name, content_type, category_or_group_id
from ""community-id".topic
join _category_and_group on topic.category_id = _category_and_group.category_or_group_id
),
union_table as (
select activities.*, category_or_group_name, _user.role_id, category_or_group_id, content_type
from activities
join topic_categories on activities.topic_public_id = topic_categories.topic_public_id
left join _user on activities.user_id = _user.user_id
),
popular_categories as (
select
activity_date as date,
content_type,
COUNT(CASE WHEN event_name = 'post_created' and entity_type = 'reply' THEN 1 END) "replies",
COUNT(CASE WHEN event_name = 'post_created' and entity_type = 'topic' THEN 1 END) "topics",
COUNT(CASE WHEN event_name = 'post_created' THEN 1 END) "posts",
COUNT(CASE WHEN event_name = 'view' THEN 1 END) "views",
COUNT(CASE WHEN event_name = 'helpfulness_helpful' THEN 1 END) "helpful",
COUNT(CASE WHEN event_name = 'helpfulness_neutral' THEN 1 END) "neutral",
COUNT(CASE WHEN event_name = 'helpfulness_not_helpful' THEN 1 END) "not_helpful",
COUNT(CASE WHEN event_name = 'like' THEN 1 END) "likes_received",
category_or_group_id,
category_or_group_name,
user_id,
role_id
from union_table
group by activity_date, category_or_group_id, category_or_group_name, role_id, user_id, content_type
)
SELECT
category_or_group_name,
category_or_group_id,
SUM(replies) as replies,
SUM(views) as views,
SUM(posts) as posts,
SUM(topics) as topics,
SUM(helpful) as helpful,
SUM(neutral) as neutral,
SUM(not_helpful) as not_helpful,
SUM(likes_received) as likes_received
FROM popular_categories
WHERE category_or_group_id IS NOT NULL
AND date BETWEEN DATE 'YYYY-MM-DD' AND DATE 'YYYY-MM-DD'
GROUP BY category_or_group_id, category_or_group_name
ORDER BY views DESC, posts DESC, topics DESC, category_or_group_name
limit 100
