Dimension Tables

create view gold.dim_customer as 
select 
    ROW_NUMBER() over (order by cst_id) as customer_key,
    ci.cst_id as customer_id,
    ci.cst_key as customer_number,
    ci.cst_firstname as first_name,
    ci.cst_lastname as last_name,
    ci.cst_marital_status as marital_status,
    case 
        when ci.cst_gndr !='n/a' then ci.cst_gndr 
        else coalesce(ca.gen, 'n/a') 
    end as gender,
    ca.bdate as birth_date,
    ci.cst_create_date as create_date
from silver.crm_cust_info ci
left join silver.erp_cust_az12 ca
    on ci.cst_key = ca.cid
left join silver.erp_loc_a101 la
    on ci.cst_key = la.cid;

create view gold.dim_product as
select
    row_number() over (order by pn.prd_start_dt, pn.prd_key) as product_key,
    pn.prd_id as product_id,
    pn.prd_key as product_number,
    pn.prd_name as product_name,
    pn.cat_id as category_id,
    pc.cat as category,
    pc.subcat as subcategory,
    pc.manteinance,
    pn.prd_cost as product_cost,
    pn.prd_line as product_line,
    pn.prd_start_dt as product_start_date
from silver.crm_prd_info pn
left join silver.erp_px_cat_g1v2 pc
    on pn.cat_id = pc.id
where pn.prd_end_dt is null;

select 
    gender,
    marital_status,
    count(*) as customer_count
from gold.dim_customer
group by gender, marital_status
order by customer_count desc;

select 
    category,
    subcategory,
    count(*) as product_count,
    avg(product_cost) as avg_cost
from gold.dim_product
group by category, subcategory
order by category, subcategory;