data.ml
dbt Parking reservations

Revenue Percent Of Month

Revenue Percent Of Month model for parking reservations.

inferred

Blocks

revenue_percent_of_month.sql · 30 lines
inferred 
-- models/revenue_percent_of_month.sql
{{ config(materialized='table') }}

select daily.parking_date
      ,daily.parking_type
      ,daily.daily_revenue
      ,monthly.monthly_revenue
from (select to_char(parking_date, 'YYYY-MM') as parking_month
            ,to_char(parking_date, 'YYYY-MM-DD') as parking_date
            ,parking_type
            ,sum(net_revenue_simple) as daily_revenue
      from ${{{ this }}.revenue_daily}
      group by 1, 2, 3
      ) as daily
inner join (select to_char(parking_date, 'YYYY-MM') as parking_month
                  ,parking_type
                  ,sum(net_revenue_simple) as monthly_revenue
            from ${{{ this }}.revenue_daily}
            group by 1, 2
            ) as monthly
        on monthly.parking_month = daily.parking_month and monthly.parking_type = daily.parking_type

# models/schema.yml
version: 2

models:
  - name: revenue_percent_of_month
    columns:
      - name: parking
      - name: parking_type

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.
revenue_percent_of_month.sql · 18 lines
source-preserved validation: not checked 
select daily.parking_date
      ,daily.parking_type
      ,daily.daily_revenue
      ,monthly.monthly_revenue
from (select to_char(parking_date, 'YYYY-MM') as parking_month
            ,to_char(parking_date, 'YYYY-MM-DD') as parking_date
            ,parking_type
            ,sum(net_revenue_simple) as daily_revenue
      from ${${TABLE}.revenue_daily}
      group by 1, 2, 3
      ) as daily
inner join (select to_char(parking_date, 'YYYY-MM') as parking_month
                  ,parking_type
                  ,sum(net_revenue_simple) as monthly_revenue
            from ${${TABLE}.revenue_daily}
            group by 1, 2
            ) as monthly
        on monthly.parking_month = daily.parking_month and monthly.parking_type = daily.parking_type
revenue_percent_of_month.view.lkml · 57 lines
inferred 
view: revenue_percent_of_month {
  derived_table: {
    sql: select daily.parking_date
      ,daily.parking_type
      ,daily.daily_revenue
      ,monthly.monthly_revenue
from (select to_char(parking_date, 'YYYY-MM') as parking_month
            ,to_char(parking_date, 'YYYY-MM-DD') as parking_date
            ,parking_type
            ,sum(net_revenue_simple) as daily_revenue
      from ${${TABLE}.revenue_daily}
      group by 1, 2, 3
      ) as daily
inner join (select to_char(parking_date, 'YYYY-MM') as parking_month
                  ,parking_type
                  ,sum(net_revenue_simple) as monthly_revenue
            from ${${TABLE}.revenue_daily}
            group by 1, 2
            ) as monthly
        on monthly.parking_month = daily.parking_month and monthly.parking_type = daily.parking_type ;;
    persist_for: "12 hours"
  }

  dimension: parking {
    type: time
    sql: to_date(${TABLE}.parking_date, 'YYYY-MM-DD') ;;
  }

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

  measure: daily_revenue {
    type: sum
    sql: ${TABLE}.daily_revenue ;;
  }

  measure: monthly_revenue {
    type: sum
    sql: ${TABLE}.monthly_revenue ;;
  }

  measure: percent_of_month {
    type: number
    sql: ${daily_revenue} / nullif(${monthly_revenue},0) ;;
  }

  measure: percent_of_month_rt {
    type: running_total
    sql: ${percent_of_month} ;;
  }

  measure: count {
    type: count
  }
}
revenue_percent_of_month.view.lookml · 42 lines
source-preserved 
- view: revenue_percent_of_month
  derived_table:
    sql: select daily.parking_date
      ,daily.parking_type
      ,daily.daily_revenue
      ,monthly.monthly_revenue
from (select to_char(parking_date, 'YYYY-MM') as parking_month
            ,to_char(parking_date, 'YYYY-MM-DD') as parking_date
            ,parking_type
            ,sum(net_revenue_simple) as daily_revenue
      from ${${TABLE}.revenue_daily}
      group by 1, 2, 3
      ) as daily
inner join (select to_char(parking_date, 'YYYY-MM') as parking_month
                  ,parking_type
                  ,sum(net_revenue_simple) as monthly_revenue
            from ${${TABLE}.revenue_daily}
            group by 1, 2
            ) as monthly
        on monthly.parking_month = daily.parking_month and monthly.parking_type = daily.parking_type
    persist_for: 12 hours
  fields:
  - dimension: parking
    type: time
    sql: to_date(${TABLE}.parking_date, 'YYYY-MM-DD')
  - dimension: parking_type
    type: string
    sql: ${TABLE}.parking_type
  - measure: daily_revenue
    type: sum
    sql: ${TABLE}.daily_revenue
  - measure: monthly_revenue
    type: sum
    sql: ${TABLE}.monthly_revenue
  - measure: percent_of_month
    type: number
    sql: ${daily_revenue} / nullif(${monthly_revenue},0)
  - measure: percent_of_month_rt
    type: running_total
    sql: ${percent_of_month}
  - measure: count
    type: count

Contents

  • revenue_percent_of_month.sql
  • revenue_percent_of_month.sql
  • revenue_percent_of_month.view.lkml
  • revenue_percent_of_month.view.lookml