dim_loyalty_segments.sql

with segments as (
    select * from dbt_user.stg_loyalty_segments
),

customers as (
    select * from dbt_user.stg_customers
),

segment_customers as (
    select 
        loyalty_segment_id,
        count(*) as number_of_customers
    from customers
    group by 1
    order by 1
),

final as (
    select
        segments.loyalty_segment_id,
        segments.loyalty_segment_description,
        segment_customers.number_of_customers
    from segments
    left join segment_customers on segments.loyalty_segment_id = segment_customers.loyalty_segment_id
)
select * from final