Provider

ComponentSchema

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

{% set rollup_cols_in_abridged = [
    'provider_id', 
    'npi', 
    'hq_latitude', 
    'hq_longitude', 
    'zip_code', 
    'county', 
    'taxonomy_grouping',
    'provider_sub_types'
] %}

CREATE OR REPLACE TABLE {{ schema_name }}.prod_rollup_provider 
AS
WITH
df AS (
    SELECT 
        p.provider_id,
        ANY_VALUE(p.npi) as npi,
        MIN(p.hq_latitude) as hq_latitude,
        MIN(p.hq_longitude) as hq_longitude,
        MIN(p.zip_code) as zip_code,
        MIN(p.county) as county,
        MIN(p.taxonomy_grouping) as taxonomy_grouping,
        ARRAY_SORT(ARRAY_AGG(DISTINCT p.service_line) FILTER (WHERE p.service_line IS NOT NULL AND p.provider_type = 'ASC')) as provider_sub_types,
        {% for col in cld_params.views.PROVIDER_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 }}
    FROM {{ schema_name }}.prod_combined_all p
    GROUP BY 1
)
SELECT 
    {% for col in cld_params.views.PROVIDER_DETAIL_COLUMNS %}
    {{ col }}{% if not loop.last %}, {% endif %}
    {% endfor %}
FROM df