CREATE VIEW vwknowledgeaggregatesdata AS (
 SELECT ka.keyid,
    ka.intervalstart AS interval_start,
    ka.intervalend AS interval_end,
    ka.usagedate AS usage_date,
    ka.updated AS last_updated,
    ka.userid AS user_id,
    ud.name AS user_name,
    ud.username AS user_username,
    ud.email AS user_email,
    ud.department AS user_department,
    ud.managerid AS user_manager_id,
    ud.managername AS user_manager_name,
    ud.divisionid AS user_division_id,
    ud.divisionname AS user_division_name,
    ka.knowledgebaseid AS knowledge_base_id,
    kb.name AS knowledge_base_name,
    kb.description AS knowledge_base_description,
    kb.corelanguage AS knowledge_base_language,
    kb.published AS knowledge_base_published,
    ka.apptype AS app_type,
    ka.conversationchanneltype AS conversation_channel_type,
    ka.conversationid AS conversation_id,
    ka.mediatype AS media_type,
    ka.querytype AS query_type,
    ka.queueid AS queue_id,
    ka.sessionid AS session_id,
    ka.feedbackrating AS feedback_rating,
    ka.surfacingmethod AS surfacing_method,
    ka.knowledgebasedocumentid AS document_id,
    ka.languagecode AS language_code,
    ka.nknowledgedocumentsurfaced AS documents_surfaced_count,
    ka.ndistinctknowledgesessions AS distinct_sessions_count,
    ka.nknowledgedocumentcopied AS documents_copied_count,
    ka.nknowledgedocumentfeedback AS document_feedback_count,
    ka.nknowledgedocumentpresented AS documents_presented_count,
    ka.nknowledgedocumentviewed AS documents_viewed_count,
    ka.nknowledgesearch AS search_count,
    ka.nknowledgesearchanswered AS search_answered_count,
    ka.nknowledgesearchfeedback AS search_feedback_count,
    ka.nknowledgesearchunanswered AS search_unanswered_count,
    ka.nknowledgesessions AS sessions_count,
    ka.oknowledgedocumentquery AS queries_per_session,
    ka.oknowledgedocumentqueryselfserved AS self_served_queries,
    ka.oknowledgesearch AS searches_per_session,
        CASE
            WHEN (ka.usagedate = CURRENT_DATE) THEN 'Today'::text
            WHEN (ka.usagedate = (CURRENT_DATE - 1)) THEN 'Yesterday'::text
            WHEN (ka.usagedate >= (CURRENT_DATE - 7)) THEN 'Last 7 Days'::text
            WHEN (ka.usagedate >= (CURRENT_DATE - 30)) THEN 'Last 30 Days'::text
            ELSE 'Older'::text
        END AS usage_period,
    to_char((ka.usagedate)::timestamp with time zone, 'Day'::text) AS day_of_week,
    EXTRACT(dow FROM ka.usagedate) AS day_of_week_number,
        CASE
            WHEN (((COALESCE(ka.nknowledgedocumentsurfaced, 0) + COALESCE(ka.nknowledgesearch, 0)) + COALESCE(ka.nknowledgedocumentviewed, 0)) >= 100) THEN 'High Usage'::text
            WHEN (((COALESCE(ka.nknowledgedocumentsurfaced, 0) + COALESCE(ka.nknowledgesearch, 0)) + COALESCE(ka.nknowledgedocumentviewed, 0)) >= 30) THEN 'Medium Usage'::text
            WHEN (((COALESCE(ka.nknowledgedocumentsurfaced, 0) + COALESCE(ka.nknowledgesearch, 0)) + COALESCE(ka.nknowledgedocumentviewed, 0)) >= 5) THEN 'Low Usage'::text
            ELSE 'Minimal Usage'::text
        END AS usage_intensity
   FROM ((knowledgeaggregatesdata ka
     LEFT JOIN vwuserdetail ud ON (((ka.userid)::text = (ud.id)::text)))
     LEFT JOIN knowledgebase kb ON (((ka.knowledgebaseid)::text = (kb.id)::text)))
)