dbt Parking reservations

Revenue Percent Of Forecast

Revenue Percent Of Forecast model for parking reservations.

inferred

Blocks

dbt SQL LKML LookML

revenue_percent_of_forecast.sql · 39 lines

inferred

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

SELECT a.parking_period_begin
      ,a.parking_type
      ,a.net_revenue_simple
      ,b.forecast_net
      ,b.forecast_gross
FROM (SELECT DATE(CONVERT_TIMEZONE('America/New_York', 'America/Chicago', bookings.parking_period_begin)) AS parking_period_begin
           ,bookings.parking_type AS parking_type
           ,COALESCE(SUM(CASE WHEN (transactions.type LIKE 'seller_fee' OR transactions.type LIKE 'buyer_fee' OR transactions.type LIKE 'seller_coupon') THEN -1.0*transactions.amount ELSE NULL END),0) as net_revenue_simple
      FROM ${{{ this }}.combined_bookings} AS bookings
      LEFT JOIN {{ this }}.location_transactions AS transactions
             ON (CASE WHEN transactions.booking_id is not null THEN transactions.booking_id
                      WHEN transactions.monthly_booking_id is not null THEN transactions.monthly_booking_id
                      ELSE NULL END) = bookings.id
      LEFT JOIN {{ this }}.cancellations AS cancellations
             ON cancellations.booking_id = bookings.id
      WHERE (((bookings.parking_period_begin) >= (CONVERT_TIMEZONE('America/Chicago', 'America/New_York', timestamp '2016-01-01'))
        AND   (bookings.parking_period_begin) < (CONVERT_TIMEZONE('America/Chicago', 'America/New_York', DATEADD(year,1, timestamp '2016-01-01' )))))
        AND (cancellations.id is null OR (TO_CHAR(CONVERT_TIMEZONE('America/New_York', 'America/Chicago', cancellations.created_at), 'YYYY-MM-DD HH24:MI:SS')) > (TO_CHAR(CONVERT_TIMEZONE('America/New_York', 'America/Chicago', bookings.parking_period_begin), 'YYYY-MM-DD HH24:MI:SS')))
      GROUP BY 1, 2
      ) as a
INNER JOIN (SELECT to_char(to_date(month_start, 'YYYYMMDD'), 'YYYY-MM') as month
                  ,parking_type
                  ,sum(forecast_net) as forecast_net
                  ,sum(forecast_gross) as forecast_gross
            FROM ${{{ this }}.forecast_revenue} as forecast_revenue
            GROUP BY 1, 2
            ) as b on b.month = TO_CHAR(a.parking_period_begin, 'YYYY-MM') and b.parking_type = a.parking_type

# models/schema.yml
version: 2

models:
  - name: revenue_percent_of_forecast
    columns:
      - name: parking_period_begin
      - 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_forecast.sql · 27 lines

source-preserved validation: not checked

SELECT a.parking_period_begin
      ,a.parking_type
      ,a.net_revenue_simple
      ,b.forecast_net
      ,b.forecast_gross
FROM (SELECT DATE(CONVERT_TIMEZONE('America/New_York', 'America/Chicago', bookings.parking_period_begin)) AS parking_period_begin
           ,bookings.parking_type AS parking_type
           ,COALESCE(SUM(CASE WHEN (transactions.type LIKE 'seller_fee' OR transactions.type LIKE 'buyer_fee' OR transactions.type LIKE 'seller_coupon') THEN -1.0*transactions.amount ELSE NULL END),0) as net_revenue_simple
      FROM ${${TABLE}.combined_bookings} AS bookings
      LEFT JOIN ${TABLE}.location_transactions AS transactions
             ON (CASE WHEN transactions.booking_id is not null THEN transactions.booking_id
                      WHEN transactions.monthly_booking_id is not null THEN transactions.monthly_booking_id
                      ELSE NULL END) = bookings.id
      LEFT JOIN ${TABLE}.cancellations AS cancellations
             ON cancellations.booking_id = bookings.id
      WHERE (((bookings.parking_period_begin) >= (CONVERT_TIMEZONE('America/Chicago', 'America/New_York', timestamp '2016-01-01'))
        AND   (bookings.parking_period_begin) < (CONVERT_TIMEZONE('America/Chicago', 'America/New_York', DATEADD(year,1, timestamp '2016-01-01' )))))
        AND (cancellations.id is null OR (TO_CHAR(CONVERT_TIMEZONE('America/New_York', 'America/Chicago', cancellations.created_at), 'YYYY-MM-DD HH24:MI:SS')) > (TO_CHAR(CONVERT_TIMEZONE('America/New_York', 'America/Chicago', bookings.parking_period_begin), 'YYYY-MM-DD HH24:MI:SS')))
      GROUP BY 1, 2
      ) as a
INNER JOIN (SELECT to_char(to_date(month_start, 'YYYYMMDD'), 'YYYY-MM') as month
                  ,parking_type
                  ,sum(forecast_net) as forecast_net
                  ,sum(forecast_gross) as forecast_gross
            FROM ${${TABLE}.forecast_revenue} as forecast_revenue
            GROUP BY 1, 2
            ) as b on b.month = TO_CHAR(a.parking_period_begin, 'YYYY-MM') and b.parking_type = a.parking_type

revenue_percent_of_forecast.view.lkml · 71 lines

inferred

view: revenue_percent_of_forecast {
  derived_table: {
    sql: SELECT a.parking_period_begin
      ,a.parking_type
      ,a.net_revenue_simple
      ,b.forecast_net
      ,b.forecast_gross
FROM (SELECT DATE(CONVERT_TIMEZONE('America/New_York', 'America/Chicago', bookings.parking_period_begin)) AS parking_period_begin
           ,bookings.parking_type AS parking_type
           ,COALESCE(SUM(CASE WHEN (transactions.type LIKE 'seller_fee' OR transactions.type LIKE 'buyer_fee' OR transactions.type LIKE 'seller_coupon') THEN -1.0*transactions.amount ELSE NULL END),0) as net_revenue_simple
      FROM ${${TABLE}.combined_bookings} AS bookings
      LEFT JOIN ${TABLE}.location_transactions AS transactions
             ON (CASE WHEN transactions.booking_id is not null THEN transactions.booking_id
                      WHEN transactions.monthly_booking_id is not null THEN transactions.monthly_booking_id
                      ELSE NULL END) = bookings.id
      LEFT JOIN ${TABLE}.cancellations AS cancellations
             ON cancellations.booking_id = bookings.id
      WHERE (((bookings.parking_period_begin) >= (CONVERT_TIMEZONE('America/Chicago', 'America/New_York', timestamp '2016-01-01'))
        AND   (bookings.parking_period_begin) < (CONVERT_TIMEZONE('America/Chicago', 'America/New_York', DATEADD(year,1, timestamp '2016-01-01' )))))
        AND (cancellations.id is null OR (TO_CHAR(CONVERT_TIMEZONE('America/New_York', 'America/Chicago', cancellations.created_at), 'YYYY-MM-DD HH24:MI:SS')) > (TO_CHAR(CONVERT_TIMEZONE('America/New_York', 'America/Chicago', bookings.parking_period_begin), 'YYYY-MM-DD HH24:MI:SS')))
      GROUP BY 1, 2
      ) as a
INNER JOIN (SELECT to_char(to_date(month_start, 'YYYYMMDD'), 'YYYY-MM') as month
                  ,parking_type
                  ,sum(forecast_net) as forecast_net
                  ,sum(forecast_gross) as forecast_gross
            FROM ${${TABLE}.forecast_revenue} as forecast_revenue
            GROUP BY 1, 2
            ) as b on b.month = TO_CHAR(a.parking_period_begin, 'YYYY-MM') and b.parking_type = a.parking_type ;;
    persist_for: "12 hours"
  }

  dimension: parking_period_begin {
    type: time
    sql: ${TABLE}.parking_period_begin ;;
  }

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

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

  measure: forecast_net {
    type: sum_distinct
    sql: ${TABLE}.forecast_net ;;
  }

  measure: forecast_gross {
    type: sum_distinct
    sql: ${TABLE}.forecast_gross ;;
  }

  measure: percent_of_net {
    type: number
    sql: ${net_revenue_simple} / nullif(${forecast_net},0) ;;
  }

  measure: percent_to_date {
    type: running_total
    sql: ${percent_of_net} ;;
  }

  measure: percent_of_gross {
    type: number
    sql: ${net_revenue_simple} / nullif(${forecast_gross},0) ;;
  }
}

revenue_percent_of_forecast.view.lookml · 54 lines

source-preserved

- view: revenue_percent_of_forecast
  derived_table:
    sql: SELECT a.parking_period_begin
      ,a.parking_type
      ,a.net_revenue_simple
      ,b.forecast_net
      ,b.forecast_gross
FROM (SELECT DATE(CONVERT_TIMEZONE('America/New_York', 'America/Chicago', bookings.parking_period_begin)) AS parking_period_begin
           ,bookings.parking_type AS parking_type
           ,COALESCE(SUM(CASE WHEN (transactions.type LIKE 'seller_fee' OR transactions.type LIKE 'buyer_fee' OR transactions.type LIKE 'seller_coupon') THEN -1.0*transactions.amount ELSE NULL END),0) as net_revenue_simple
      FROM ${${TABLE}.combined_bookings} AS bookings
      LEFT JOIN ${TABLE}.location_transactions AS transactions
             ON (CASE WHEN transactions.booking_id is not null THEN transactions.booking_id
                      WHEN transactions.monthly_booking_id is not null THEN transactions.monthly_booking_id
                      ELSE NULL END) = bookings.id
      LEFT JOIN ${TABLE}.cancellations AS cancellations
             ON cancellations.booking_id = bookings.id
      WHERE (((bookings.parking_period_begin) >= (CONVERT_TIMEZONE('America/Chicago', 'America/New_York', timestamp '2016-01-01'))
        AND   (bookings.parking_period_begin) < (CONVERT_TIMEZONE('America/Chicago', 'America/New_York', DATEADD(year,1, timestamp '2016-01-01' )))))
        AND (cancellations.id is null OR (TO_CHAR(CONVERT_TIMEZONE('America/New_York', 'America/Chicago', cancellations.created_at), 'YYYY-MM-DD HH24:MI:SS')) > (TO_CHAR(CONVERT_TIMEZONE('America/New_York', 'America/Chicago', bookings.parking_period_begin), 'YYYY-MM-DD HH24:MI:SS')))
      GROUP BY 1, 2
      ) as a
INNER JOIN (SELECT to_char(to_date(month_start, 'YYYYMMDD'), 'YYYY-MM') as month
                  ,parking_type
                  ,sum(forecast_net) as forecast_net
                  ,sum(forecast_gross) as forecast_gross
            FROM ${${TABLE}.forecast_revenue} as forecast_revenue
            GROUP BY 1, 2
            ) as b on b.month = TO_CHAR(a.parking_period_begin, 'YYYY-MM') and b.parking_type = a.parking_type
    persist_for: 12 hours
  fields:
  - dimension: parking_period_begin
    type: time
    sql: ${TABLE}.parking_period_begin
  - dimension: parking_type
    sql: ${TABLE}.parking_type
  - measure: net_revenue_simple
    type: sum
    sql: ${TABLE}.net_revenue_simple
  - measure: forecast_net
    type: sum_distinct
    sql: ${TABLE}.forecast_net
  - measure: forecast_gross
    type: sum_distinct
    sql: ${TABLE}.forecast_gross
  - measure: percent_of_net
    type: number
    sql: ${net_revenue_simple} / nullif(${forecast_net},0)
  - measure: percent_to_date
    type: running_total
    sql: ${percent_of_net}
  - measure: percent_of_gross
    type: number
    sql: ${net_revenue_simple} / nullif(${forecast_gross},0)

revenue_percent_of_forecast.sql
revenue_percent_of_forecast.sql
revenue_percent_of_forecast.view.lkml
revenue_percent_of_forecast.view.lookml

Revenue Percent Of Forecast

Blocks

Assumptions

Contents