Grow Bootcamp _ dbt Solution Key

dbt_grow_exercises.sql

--  1A) dim_customers SQL

select 
    store_id || "-" || cast(id as string) as unique_id
    , id
    , store_id
    , name
    , email
from {{ source('apjuice', 'users') }}  

--  2A) test and docs

    - name: dim_customers
      description: customer details
      columns:
        - name: unique_id
          description: unique customer_id and store_id combination
          tests:
            - not_null
            - unique

--  3A) top_customers

select 
    s.store_id
    , ss.unique_customer_id
    , c.name
    , sum(product_cost) as total_spend

from {{ ref('sales_items') }} as s 
join {{ ref('sales') }} as ss on s.sale_id = ss.id
join {{ ref('dim_customers') }} as c on ss.unique_customer_id = c.unique_id

where 
    ss.unique_customer_id is not null
group by s.store_id, ss.unique_customer_id, c.name
order by total_spend desc
limit 100