dbt Ecommerce discount

Credits Accounting

Credits Accounting model for ecommerce discount.

inferred

Blocks

dbt SQL LKML LookML

credits_accounting.sql · 42 lines

inferred

-- models/credits_accounting.sql
{{ config(materialized='view') }}

SELECT
v.id,
v.name,
DATE_FORMAT(oic.created_on, '%Y-%m') AS month,
SUM(ROUND(IF(oi.credit < 0.01,
cc.original_amount,
oi.credit) / (oi.quantity * oi.price + oi.shipping) * (oi.quantity * oi.product_cost),
2)) AS cogs_amount_old,
SUM(ROUND(oi.credit / (oi.quantity * oi.price + oi.shipping) * (oi.quantity * oi.product_cost),
2)) AS cogs_amount_new
FROM
order_item_credits oic
JOIN
customer_credits cc ON cc.id = oic.customer_credit_id
AND cc.state != 'revoked'
JOIN
order_items oi ON oi.id = oic.order_item_id
LEFT JOIN
shipments s ON s.id = oi.shipment_id
JOIN
products p ON p.id = oi.product_id
JOIN
vendors v ON v.id = p.vendor_id
AND s.state != 'shipped'
AND {% condition credit_date %} oic.created_on {% endcondition %}
AND p.type != 'digital'
GROUP BY v.id , month
ORDER BY v.name , month

# models/schema.yml
version: 2

models:
  - name: credits_accounting
    columns:
      - name: id
      - name: name
      - name: cogs_amount_old
      - name: cogs_amount_new

Assumptions

materialized='view' is the default; no persist hint found.
schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

credits_accounting.sql · 28 lines

source-preserved validation: not checked

SELECT
v.id,
v.name,
DATE_FORMAT(oic.created_on, '%Y-%m') AS month,
SUM(ROUND(IF(oi.credit < 0.01,
cc.original_amount,
oi.credit) / (oi.quantity * oi.price + oi.shipping) * (oi.quantity * oi.product_cost),
2)) AS cogs_amount_old,
SUM(ROUND(oi.credit / (oi.quantity * oi.price + oi.shipping) * (oi.quantity * oi.product_cost),
2)) AS cogs_amount_new
FROM
order_item_credits oic
JOIN
customer_credits cc ON cc.id = oic.customer_credit_id
AND cc.state != 'revoked'
JOIN
order_items oi ON oi.id = oic.order_item_id
LEFT JOIN
shipments s ON s.id = oi.shipment_id
JOIN
products p ON p.id = oi.product_id
JOIN
vendors v ON v.id = p.vendor_id
AND s.state != 'shipped'
AND {% condition credit_date %} oic.created_on {% endcondition %}
AND p.type != 'digital'
GROUP BY v.id , month
ORDER BY v.name , month

credits_accounting.view.lkml · 74 lines

inferred

view: credits_accounting {
  derived_table: {
    sql: SELECT
v.id,
v.name,
DATE_FORMAT(oic.created_on, '%Y-%m') AS month,
SUM(ROUND(IF(oi.credit < 0.01,
cc.original_amount,
oi.credit) / (oi.quantity * oi.price + oi.shipping) * (oi.quantity * oi.product_cost),
2)) AS cogs_amount_old,
SUM(ROUND(oi.credit / (oi.quantity * oi.price + oi.shipping) * (oi.quantity * oi.product_cost),
2)) AS cogs_amount_new
FROM
order_item_credits oic
JOIN
customer_credits cc ON cc.id = oic.customer_credit_id
AND cc.state != 'revoked'
JOIN
order_items oi ON oi.id = oic.order_item_id
LEFT JOIN
shipments s ON s.id = oi.shipment_id
JOIN
products p ON p.id = oi.product_id
JOIN
vendors v ON v.id = p.vendor_id
AND s.state != 'shipped'
AND {% condition credit_date %} oic.created_on {% endcondition %}
AND p.type != 'digital'
GROUP BY v.id , month
ORDER BY v.name , month ;;
  }

  dimension: id {
    type: int
    sql: ${TABLE}.id ;;
  }

  dimension: name {
    type: string
    sql: ${TABLE}.name ;;
  }

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

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

  dimension_group: month {
    type: string
    sql: ${TABLE}.month ;;
  }

  measure: count {
    type: count
    drill_fields: [detail*]
  }

  measure: amount_cogs_old {
    type: number
    value_format: "$#,##0.00"
    sql: ${TABLE}.cogs_amount_old ;;
  }

  measure: amount_cogs_new {
    type: number
    value_format: "$#,##0.00"
    sql: ${TABLE}.cogs_amount_new ;;
  }
}

credits_accounting.view.lookml · 58 lines

source-preserved

- view: credits_accounting
  derived_table:
    sql: SELECT
v.id,
v.name,
DATE_FORMAT(oic.created_on, '%Y-%m') AS month,
SUM(ROUND(IF(oi.credit < 0.01,
cc.original_amount,
oi.credit) / (oi.quantity * oi.price + oi.shipping) * (oi.quantity * oi.product_cost),
2)) AS cogs_amount_old,
SUM(ROUND(oi.credit / (oi.quantity * oi.price + oi.shipping) * (oi.quantity * oi.product_cost),
2)) AS cogs_amount_new
FROM
order_item_credits oic
JOIN
customer_credits cc ON cc.id = oic.customer_credit_id
AND cc.state != 'revoked'
JOIN
order_items oi ON oi.id = oic.order_item_id
LEFT JOIN
shipments s ON s.id = oi.shipment_id
JOIN
products p ON p.id = oi.product_id
JOIN
vendors v ON v.id = p.vendor_id
AND s.state != 'shipped'
AND {% condition credit_date %} oic.created_on {% endcondition %}
AND p.type != 'digital'
GROUP BY v.id , month
ORDER BY v.name , month
  fields:
  - dimension: id
    type: int
    sql: ${TABLE}.id
  - dimension: name
    type: string
    sql: ${TABLE}.name
  - dimension: cogs_amount_old
    type: number
    sql: ${TABLE}.cogs_amount_old
  - dimension: cogs_amount_new
    type: number
    sql: ${TABLE}.cogs_amount_new
  - dimension_group: month
    type: string
    sql: ${TABLE}.month
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: amount_cogs_old
    type: number
    value_format: '$#,##0.00'
    sql: ${TABLE}.cogs_amount_old
  - measure: amount_cogs_new
    type: number
    value_format: '$#,##0.00'
    sql: ${TABLE}.cogs_amount_new

credits_accounting.sql
credits_accounting.sql
credits_accounting.view.lkml
credits_accounting.view.lookml

Credits Accounting

Blocks

Assumptions

Contents