score_card.sql

    ------------------------------------------------------------------------------
    -- Identify the kind they are using for scoring
    ------------------------------------------------------------------------------
    WITH scored_entity_kind AS (
        SELECT
            id
        FROM
            entity_entitykind
        WHERE
            name='collection.Role'
    ),
    
    ------------------------------------------------------------------------------
    -- Pull all super entities of scored kind (will ignore override benchmarks)
    ------------------------------------------------------------------------------
    scored_super_entities AS (
        SELECT
            entity_entity.id AS entity_id,
            display_name AS entity_display_name
        FROM
            entity_entity
        JOIN
            scored_entity_kind
        ON
            entity_entity.entity_kind_id = scored_entity_kind.id
        WHERE
            entity_entity.is_active
    ),
    
    ------------------------------------------------------------------------------
    -- Pull param_config info forall sub-entites that belong to super_entities
    ------------------------------------------------------------------------------
    scored_entities AS (
    
        SELECT
            scored_super_entities.entity_display_name as super_entity_display_name,
            param_config.entity_id as super_entity_id,
            param_config.metric_config_id,
            metric_config.display_name as metric_name,
            score_config.display_name as score_name,
            score_config.time_group_config_id as timegroup_config_id,
            param_config.benchmark,
            param_config.weight,
            relationship.sub_entity_id
        FROM
            ambition_score_paramconfig param_config
        JOIN
            scored_super_entities
        ON
            scored_super_entities.entity_id = param_config.entity_id
        JOIN
            entity_entityrelationship relationship
        ON
            relationship.super_entity_id = param_config.entity_id
        JOIN
            animal_metricconfig metric_config
        ON
            param_config.metric_config_id = metric_config.id
        JOIN
            ambition_score_scoreconfig score_config
        ON
            score_config.id = param_config.score_config_id
            
        WHERE
            param_config.start_time <= {{now}}
        AND
            param_config.end_time > {{now}}
    ),

    ------------------------------------------------------------------------------
    -- Get metric values over timeframe with joined param and super info
    ------------------------------------------------------------------------------
    metric_values AS (
        SELECT
            scored_entities.*,
            timegroup_config.name as time_group_name,
            metric.value
        FROM
            scored_entities
        JOIN
            animal_timegroupconfig timegroup_config
        ON
            timegroup_config.id = scored_entities.timegroup_config_id
        JOIN
            animal_metric metric
        ON
            metric.metric_config_id = scored_entities.metric_config_id
        AND
            metric.entity_id = scored_entities.sub_entity_id
        AND
            metric.time_group_config_id = scored_entities.timegroup_config_id
            
        AND
            metric.time >= {{then}}
        AND
            metric.time < {{now}}
            
    ),
    
    ------------------------------------------------------------------------------
    -- Compute the stats for 80th percentile recomendations
    ------------------------------------------------------------------------------
    score_stats AS (
        SELECT
            score_name,
            time_group_name,
            super_entity_display_name,
            metric_name,
            super_entity_id,
            metric_config_id,
            timegroup_config_id,
            benchmark,
            count(distinct sub_entity_id) as num_users,
            percentile_cont({{percentile}}) within group (order by metric_values.value) as eightieth_percentile
        FROM
            metric_values
        GROUP BY
            score_name,
            time_group_name,
            super_entity_display_name,
            metric_name,
            super_entity_id,
            metric_config_id,
            timegroup_config_id,
            benchmark
    )
    
    ------------------------------------------------------------------------------
    -- Probably don't need that last CTE, but...
    ------------------------------------------------------------------------------
    
    select * from score_stats