Martsモデルで「分析できるデータ」をつくろう

models/marts/core/dim_customers.sqlのSQLファイルを作成します。

1. 必要なモデルの読み込み: 最初のステップとして、stg_jaffle_shop__raw_customers（顧客ステージングモデル）とstg_jaffle_shop__raw_orders（注文ステージングモデル）を、それぞれref()関数を使ってCTEとして定義します。これにより、どのモデルに依存しているかが明確になります。
2. 顧客ごとの指標集計: 次に、ordersCTEをcustomer_idでGROUP BYし、min(order_date)でfirst_order_date（最初の注文日）、max(order_date)でmost_recent_order_date（最新の注文日）、count(order_id)でnumber_of_orders（総注文回数）、sum(order_total)でtotal_order_amount（総注文金額）を集計します。この集計結果をcustomer_ordersという名前のCTEとして定義します。
3. 最終的な結合: 最後に、customersCTEを主軸（左側）とし、customer_ordersCTEをcustomer_idをキーにしてLEFT JOINします。ここでLEFT JOINを使うことが重要です。これにより、まだ一度も注文していない顧客情報も失われることなく、最終的なモデルに残すことができます。

上記のロジックを実装した完全なSQLコードは以下の通りです。これをdim_customers.sqlファイルにコピー＆ペーストしてください。

with customers as(

    select * from {{ ref('stg_jaffle_shop__raw_customers') }}

),  

orders as(

    select * from {{ ref('stg_jaffle_shop__raw_orders') }}

),

customer_orders as (

    select
        customer_id,
        min(ordered_at) as first_order_date,
        max(ordered_at) as most_recent_order_date,
        count(ordered_at) as number_of_orders,
        sum(order_total) as total_oreder_amount
    from orders
    group by customer_id

),

final as (
    select
        customers.customer_id,
        customers.customer_name,
        customer_orders.first_order_date,
        customer_orders.most_recent_order_date,
        coalesce(customer_orders.number_of_orders, 0) as number_of_orders
        customer_oreders.total_order_amount
    from customers
    left join customer_orders
        on customers.customer_id = customer_orders.customer_id
)

select * from final

models/marts/fct_orders.sqlのSQLファイルを作成します。

1. 必要なモデルの読み込み: 最初のステップとして、stg_jaffle_shop__raw_orders（注文ステージングモデル）とstg_jaffle_shop__raw_items（注文商品ステージングモデル）を、それぞれref()関数を使ってCTEとして定義します。これにより、どのモデルに依存しているかが明確になります。
2. 顧客ごとの指標集計: 次に、itemsCTEをorder_idでGROUP BYし、count(item_id)でitem_count（注文商品数）を集計します。この集計結果をitems_groupedという名前のCTEとして定義します。
3. 最終的な結合: 最後に、ordersCTEを主軸（左側）とし、item_groupedCTEをorder_idをキーにしてLEFT JOINします。

上記のロジックを実装した完全なSQLコードは以下の通りです。これをfct_orders.sqlファイルにコピー＆ペーストしてください。

with orders as (
    select * from {{ ref('stg_jaffle_shop__raw_orders') }}
),

items as (
    select * from {{ ref('stg_jaffle_shop__raw_items') }}
),

items_grouped as (

    select
        order_id,
        count(item_id) as item_count
    from items
    group by order_id

)
select 

    orders.order_id,
    orders.customer_id,
    orders.ordered_at,
    orders.store_id,
    orders.subtotal,
    orders.tax_paid,
    orders.order_total,
    items_grouped.item_count

from orders
left join items_grouped
    on orders.order_id = items_grouped.order_id