user_scores_daily CQ

user_scores_daily_cq.md

This is the continuous query we run to get daily aggregations of user_scores. There is a unique record produced per user per day.

SELECT
  MEAN(total_user_score) AS total_user_score,
  MEAN(smartbites_commented_count) AS smartbites_commented_count,
  MEAN(smartbites_completed_count) AS smartbites_completed_count,
  MEAN(smartbites_created_count) AS smartbites_created_count,
  MEAN(smartbites_consumed_count) AS smartbites_consumed_count,
  MEAN(smartbites_liked_count) AS smartbites_liked_count,
  MEAN(total_smartbite_score) AS total_smartbite_score,
  MEAN(time_spent_minutes) AS time_spent_minutes
INTO
  user_scores_daily
FROM (
  SELECT
    SUM(score_value) AS total_user_score
  FROM user_scores
  GROUP BY org_id,user_id,time(1d)
),(
  SELECT
    COUNT(score_value) AS smartbites_commented_count
  FROM user_scores
  WHERE
    event='card_comment_created'
  AND (
    role='actor' OR
    role='actor_and_owner'
  )
  GROUP BY org_id,user_id,time(1d)
),(
  SELECT
    COUNT(score_value) AS smartbites_completed_count
  FROM user_scores
  WHERE
    event='card_marked_as_complete'
  AND (
    role='actor' OR
    role='actor_and_owner'
  )
  GROUP BY org_id,user_id,time(1d)
),(
  SELECT
    COUNT(score_value) AS smartbites_created_count
  FROM user_scores
  WHERE
    event='card_created'
  AND (
    role='actor' OR
    role='actor_and_owner'
  )
 GROUP BY org_id,user_id,time(1d)
),(
  SELECT
    COUNT(score_value) AS smartbites_consumed_count
  FROM user_scores
  WHERE
    event='card_viewed'
  AND (
    role='actor' OR
    role='actor_and_owner'
  )
 GROUP BY org_id,user_id,time(1d)
),(
  SELECT
    COUNT(score_value) AS smartbites_liked_count
  FROM user_scores
  WHERE
    event='card_liked'
  AND (
    role='actor' OR
    role='actor_and_owner'
  )
 GROUP BY org_id,user_id,time(1d)
),(
  SELECT
    SUM(score_value) AS total_smartbite_score
    FROM user_scores
    WHERE (
      event='card_created' OR
      event='card_deleted' OR
      event='card_promoted' OR
      event='card_unpromoted' OR
      event='card_added_to_channel' OR
      event='card_removed_from_channel' OR
      event='card_marked_as_complete' OR
      event='card_marked_as_uncomplete' OR
      event='card_pinned' OR
      event='card_unpinned' OR
      event='card_bookmarked' OR
      event='card_unbookmarked' OR
      event='card_comment_created' OR
      event='card_comment_deleted' OR
      event='card_liked' OR
      event='card_unliked' OR
      event='card_viewed' OR
      event='card_video_stream_stopped_viewing' OR
      event='card_video_stream_started_viewing' OR
      event='card_video_stream_downloaded' OR
      event='card_video_stream_previewed' OR
      event='card_edited' OR
      event='card_added_to_pathway' OR
      event='card_approved_for_channel' OR
      event='card_rejected_for_channel' OR
      event='card_removed_from_pathway' OR
      event='card_assigned' OR
      event='card_published' OR
      event='card_dismissed' OR
      event='card_shared' OR
      event='card_relevance_rated' OR
      event='card_poll_response_created' OR
      event='card_quiz_response_created'
    )
    AND (
      role='actor' OR
      role='actor_and_owner'
    )
    GROUP BY org_id,user_id,time(1d)
),(
  SELECT
  COUNT(time_spent_minute) AS time_spent_minutes
  FROM (
    SELECT COUNT(score_value) AS time_spent_minute
    FROM user_scores
    WHERE (
      role='actor' OR
      role='actor_and_owner'
    )
    GROUP BY org_id,user_id,time(1m)
  )
  WHERE
    time_spent_minute > 0
  GROUP BY org_id,user_id,time(1d)
)
GROUP BY org_id,user_id,time(1d)