Daily Revenue

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

SELECT
  DATE((financial_transactions.completed_at)::timestamptz AT TIME ZONE 'America/New_York') AS "completed_date",
  -1.0 * (SUM(CASE WHEN financial_transactions.category IN ('entity_story_fee', 'entity_story_adjustment', 'entity_story_refund') THEN (CASE financial_transactions.type
  WHEN  'Debit'
  THEN financial_transactions.amount / -100
  ELSE financial_transactions.amount / 100
END
) ELSE NULL END)) AS "total_net_amount"
FROM financial_transactions
LEFT JOIN profiles AS external_profiles ON financial_transactions.brand_profile_id = entities.id
WHERE entities.internal = 'No'
GROUP BY 1

# models/schema.yml
version: 2

models:
  - name: daily_revenue
    columns:
      - name: transaction_completed
      - name: total_net_amount
      - name: weekday

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.

SELECT
  DATE((financial_transactions.completed_at)::timestamptz AT TIME ZONE 'America/New_York') AS "completed_date",
  -1.0 * (SUM(CASE WHEN financial_transactions.category IN ('entity_story_fee', 'entity_story_adjustment', 'entity_story_refund') THEN (CASE financial_transactions.type
  WHEN  'Debit'
  THEN financial_transactions.amount / -100
  ELSE financial_transactions.amount / 100
END
) ELSE NULL END)) AS "total_net_amount"
FROM financial_transactions
LEFT JOIN profiles AS external_profiles ON financial_transactions.brand_profile_id = entities.id
WHERE entities.internal = 'No'
GROUP BY 1

view: daily_revenue {
  derived_table: {
    sql: SELECT
  DATE((financial_transactions.completed_at)::timestamptz AT TIME ZONE 'America/New_York') AS "completed_date",
  -1.0 * (SUM(CASE WHEN financial_transactions.category IN ('entity_story_fee', 'entity_story_adjustment', 'entity_story_refund') THEN (CASE financial_transactions.type
  WHEN  'Debit'
  THEN financial_transactions.amount / -100
  ELSE financial_transactions.amount / 100
END
) ELSE NULL END)) AS "total_net_amount"
FROM financial_transactions
LEFT JOIN profiles AS external_profiles ON financial_transactions.brand_profile_id = entities.id
WHERE entities.internal = 'No'
GROUP BY 1 ;;
  }

  dimension: transaction_completed {
    type: time
    sql: ${TABLE}.completed_date ;;
  }

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

  dimension: weekday {
    type: yesno
    sql: date_part('dow',${transaction_completed_date}) between 1 and 5 ;;
  }

  measure: average_daily_revenue {
    type: average
    value_format: "$#,##0"
    filters: [weekday: "True"]
    sql: ${total_net_amount} ;;
  }
}

- view: daily_revenue
  derived_table:
    sql: SELECT
  DATE((financial_transactions.completed_at)::timestamptz AT TIME ZONE 'America/New_York') AS "completed_date",
  -1.0 * (SUM(CASE WHEN financial_transactions.category IN ('entity_story_fee', 'entity_story_adjustment', 'entity_story_refund') THEN (CASE financial_transactions.type
  WHEN  'Debit'
  THEN financial_transactions.amount / -100
  ELSE financial_transactions.amount / 100
END
) ELSE NULL END)) AS "total_net_amount"
FROM financial_transactions
LEFT JOIN profiles AS external_profiles ON financial_transactions.brand_profile_id = entities.id
WHERE entities.internal = 'No'
GROUP BY 1
  fields:
  - dimension: transaction_completed
    type: time
    sql: ${TABLE}.completed_date
  - dimension: total_net_amount
    type: number
    sql: ${TABLE}.total_net_amount
  - dimension: weekday
    type: yesno
    sql: date_part('dow',${transaction_completed_date}) between 1 and 5
  - measure: average_daily_revenue
    type: average
    value_format: '$#,##0'
    filters:
    - weekday: True
    sql: ${total_net_amount}