PI Views

Logins
======
CREATE OR REPLACE VIEW logins AS 
(
   SELECT
     package_id
   , "date"(timestamp_derived) for_day_dt
   , organization_id
   , "count"(DISTINCT user_id_token) distinct_users_token_num
   FROM
     apps
   GROUP BY package_id, organization_id, "date"(timestamp_derived)
)

Entity
=======
CREATE OR REPLACE VIEW entitytype AS(
SELECT
     package_id, "date"(timestamp_derived) for_day_dt, organization_id,
    custom_entity_type, custom_entity,
   "count"(custom_entity) custom_entity_count_num
   FROM
     apps
   GROUP BY package_id, organization_id, "date"(timestamp_derived), custom_entity_type, custom_entity)
   
CRUD
====
CREATE OR REPLACE VIEW crud AS(
SELECT package_id, organization_id, "date"(timestamp_derived) for_day_dt, custom_entity_type, custom_entity, operation_type, sum(operation_count) as total_operation_count_num 
FROM apps 
group by package_id, organization_id, "date"(timestamp_derived), operation_type, custom_entity_type, custom_entity 
having custom_entity_type='CustomObject')

ApexException
=============
CREATE VIEW apexerror AS
(
   SELECT
    package_id
   , organization_id
   , "date"(timestamp_derived) for_day_dt
   , custom_entity_type
   , custom_entity
    ,json_extract_scalar(stack_trace,'$.exceptionType') AS exceptionType
    ,json_extract_scalar(stack_trace,'$.stackFrames[0].method') AS method
    ,json_extract_scalar(stack_trace,'$.stackFrames[0].line') AS line
    ,json_extract_scalar(stack_trace,'$.stackFrames[0].colum') AS colno  
   , stack_trace
   FROM
     apps
   WHERE log_record_type = 'ApexUnexpectedException' 
   limit 10000
)