Actual Invoice Monthly

-- models/actual_invoice_monthly.sql
{{ config(materialized='table') }}

SELECT i.account_id, ii.branch_id, TO_CHAR(i.invoice_date, 'YYYY-MM') AS invoice_month,
MAX(
CASE a.account_type
       WHEN 'account_type_1'  Then 'AGENT'
       WHEN 'account_type_2' then 'AGENT'
       WHEN 'account_type_3' Then 'AGENT'
       WHEN 'account_type_4'  Then 'AGENT'
       WHEN 'account_type_5' THEN 'DEVELOPER'
       WHEN 'account_type_6' THEN 'OVERSEAS'
       WHEN 'account_type_7' THEN 'COMMERCIAL'
       ELSE 'OTHER'
    END) AS account_type,
SUM(amount_invoiced_net) AS invoiced_net_amount, SUM(amount_invoiced) AS gross_amount,
COUNT(1) AS number_of_invoice_items, COUNT(DISTINCT i.invoice_id) AS number_of_invoices
FROM invoice_items ii
INNER JOIN invoices i
ON ii.invoice_id = i.invoice_id
INNER JOIN agent_accounts_nopii a
ON i.account_id = a.account_id
GROUP BY 1,2,3

# models/schema.yml
version: 2

models:
  - name: actual_invoice_monthly
    columns:
      - name: account_id
      - name: account_type
      - name: branch_id
      - name: invoice
      - name: invoiced_net_amount
      - name: gross_amount
      - name: number_of_invoice_items
      - name: number_of_invoices

Assumptions

• materialized='table' inferred from a LookML persist hint (persist_for / datagroup / sql_trigger_value).
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

SELECT i.account_id, ii.branch_id, TO_CHAR(i.invoice_date, 'YYYY-MM') AS invoice_month,
MAX(
CASE a.account_type
       WHEN 'account_type_1'  Then 'AGENT'
       WHEN 'account_type_2' then 'AGENT'
       WHEN 'account_type_3' Then 'AGENT'
       WHEN 'account_type_4'  Then 'AGENT'
       WHEN 'account_type_5' THEN 'DEVELOPER'
       WHEN 'account_type_6' THEN 'OVERSEAS'
       WHEN 'account_type_7' THEN 'COMMERCIAL'
       ELSE 'OTHER'
    END) AS account_type,
SUM(amount_invoiced_net) AS invoiced_net_amount, SUM(amount_invoiced) AS gross_amount,
COUNT(1) AS number_of_invoice_items, COUNT(DISTINCT i.invoice_id) AS number_of_invoices
FROM invoice_items ii
INNER JOIN invoices i
ON ii.invoice_id = i.invoice_id
INNER JOIN agent_accounts_nopii a
ON i.account_id = a.account_id
GROUP BY 1,2,3

view: actual_invoice_monthly {
  derived_table: {
    sql: SELECT i.account_id, ii.branch_id, TO_CHAR(i.invoice_date, 'YYYY-MM') AS invoice_month,
MAX(
CASE a.account_type
       WHEN 'account_type_1'  Then 'AGENT'
       WHEN 'account_type_2' then 'AGENT'
       WHEN 'account_type_3' Then 'AGENT'
       WHEN 'account_type_4'  Then 'AGENT'
       WHEN 'account_type_5' THEN 'DEVELOPER'
       WHEN 'account_type_6' THEN 'OVERSEAS'
       WHEN 'account_type_7' THEN 'COMMERCIAL'
       ELSE 'OTHER'
    END) AS account_type,
SUM(amount_invoiced_net) AS invoiced_net_amount, SUM(amount_invoiced) AS gross_amount,
COUNT(1) AS number_of_invoice_items, COUNT(DISTINCT i.invoice_id) AS number_of_invoices
FROM invoice_items ii
INNER JOIN invoices i
ON ii.invoice_id = i.invoice_id
INNER JOIN agent_accounts_nopii a
ON i.account_id = a.account_id
GROUP BY 1,2,3 ;;
    persist_for: "24 hours"
  }

  dimension: account_id {
    type: number
    sql: ${TABLE}.account_id ;;
  }

  dimension: account_type {
    sql: ${TABLE}.account_type ;;
  }

  dimension: branch_id {
    type: number
    sql: ${TABLE}.branch_id ;;
  }

  dimension: invoice {
    type: time
    sql: DATE(CONCAT(${TABLE}.invoice_month, '-01')) ;;
  }

  dimension: invoiced_net_amount {
    type: number
    hidden: yes
    sql: ${TABLE}.invoiced_net_amount ;;
  }

  dimension: gross_amount {
    type: number
    hidden: yes
    sql: ${TABLE}.gross_amount ;;
  }

  dimension: number_of_invoice_items {
    type: number
    sql: ${TABLE}.number_of_invoice_items ;;
  }

  dimension: number_of_invoices {
    type: number
    sql: ${TABLE}.number_of_invoices ;;
  }

  measure: count {
    type: count
  }

  measure: total_invoiced_net_amount {
    type: sum
    sql: ${invoiced_net_amount} ;;
  }

  measure: total_gross_amount {
    type: sum
    sql: ${gross_amount} ;;
  }
}

- view: actual_invoice_monthly
  derived_table:
    sql: SELECT i.account_id, ii.branch_id, TO_CHAR(i.invoice_date, 'YYYY-MM') AS invoice_month,
MAX(
CASE a.account_type
       WHEN 'account_type_1'  Then 'AGENT'
       WHEN 'account_type_2' then 'AGENT'
       WHEN 'account_type_3' Then 'AGENT'
       WHEN 'account_type_4'  Then 'AGENT'
       WHEN 'account_type_5' THEN 'DEVELOPER'
       WHEN 'account_type_6' THEN 'OVERSEAS'
       WHEN 'account_type_7' THEN 'COMMERCIAL'
       ELSE 'OTHER'
    END) AS account_type,
SUM(amount_invoiced_net) AS invoiced_net_amount, SUM(amount_invoiced) AS gross_amount,
COUNT(1) AS number_of_invoice_items, COUNT(DISTINCT i.invoice_id) AS number_of_invoices
FROM invoice_items ii
INNER JOIN invoices i
ON ii.invoice_id = i.invoice_id
INNER JOIN agent_accounts_nopii a
ON i.account_id = a.account_id
GROUP BY 1,2,3
    persist_for: 24 hours
  fields:
  - dimension: account_id
    type: number
    sql: ${TABLE}.account_id
  - dimension: account_type
    sql: ${TABLE}.account_type
  - dimension: branch_id
    type: number
    sql: ${TABLE}.branch_id
  - dimension: invoice
    type: time
    sql: DATE(CONCAT(${TABLE}.invoice_month, '-01'))
  - dimension: invoiced_net_amount
    type: number
    hidden: true
    sql: ${TABLE}.invoiced_net_amount
  - dimension: gross_amount
    type: number
    hidden: true
    sql: ${TABLE}.gross_amount
  - dimension: number_of_invoice_items
    type: number
    sql: ${TABLE}.number_of_invoice_items
  - dimension: number_of_invoices
    type: number
    sql: ${TABLE}.number_of_invoices
  - measure: count
    type: count
  - measure: total_invoiced_net_amount
    type: sum
    sql: ${invoiced_net_amount}
  - measure: total_gross_amount
    type: sum
    sql: ${gross_amount}