Network

ComponentSchema

-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.prod_rollup_network

{% set rollup_cols_in_abridged = [
    'network_id', 
    'payer_network_name', 
    'payer_name', 
    'network_name', 
    'network_type', 
    'network_class',
    'distinct_provider_count',
    'distinct_state_count'    
] %}

CREATE TABLE IF NOT EXISTS {{ schema_name }}.prod_rollup_network 
AS
WITH
df AS (
    SELECT 
        p.network_id,
        p.payer_network_name,
        p.payer_name,
        p.network_name,
        p.network_type,
        p.network_class,
        {% for col in cld_params.views.NETWORK_DETAIL_COLUMNS %}
            {% if col not in rollup_cols_in_abridged and col not in cld_params.views.ROLLUP_COLUMNS %}
                MIN(p.{{ col }}) as {{ col }},
            {% endif %}
        {% endfor %}
        {{ cld_params.views.ROLLUP_STATS }},
        COUNT(DISTINCT p.provider_id) AS distinct_provider_count,
        COUNT(DISTINCT p.state) AS distinct_state_count
    FROM {{ schema_name }}.prod_combined_all p
    WHERE p.payer_name IS NOT NULL
    GROUP BY 1,2,3,4,5,6
)
SELECT 
    {% for col in cld_params.views.NETWORK_DETAIL_COLUMNS %}
        {{ col }}{% if not loop.last %}, {% endif %}
    {% endfor %}
FROM df