SQL Design marketplace

Revenue Summary

Revenue Summary model for design marketplace.

source-preserved

Blocks

dbt SQL LKML LookML

revenue_summary.sql · 73 lines

inferred

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

SELECT DATE(timecreated) AS timecreated
  , COUNT(CASE WHEN S.TYPE = 'contest' THEN id ELSE 0 END) AS contest_orders
  , SUM(CASE WHEN S.TYPE = 'contest' THEN productcount ELSE 0 END) AS contest_productcount
  , SUM(CASE WHEN S.TYPE = 'contest' THEN platformcount ELSE 0 END) AS contest_platformcount
  , SUM(CASE WHEN S.TYPE = 'contest' THEN revenue ELSE 0 END) AS contest_revenue
  , SUM(CASE WHEN S.TYPE = 'contest' THEN profit ELSE 0 END) AS contest_profit
  , SUM(CASE WHEN S.TYPE = 'contest' THEN discount ELSE 0 END) AS contest_discount
  , COUNT(CASE WHEN S.TYPE = 'project' THEN id ELSE 0 END) AS project_orders
  , SUM(CASE WHEN S.TYPE = 'project' THEN productcount ELSE 0 END) AS project_productcount
  , SUM(CASE WHEN S.TYPE = 'project' THEN platformcount ELSE 0 END) AS project_platformcount
  , SUM(CASE WHEN S.TYPE = 'project' THEN revenue ELSE 0 END) AS project_revenue
  , SUM(CASE WHEN S.TYPE = 'project' THEN profit ELSE 0 END) AS project_profit
  , SUM(CASE WHEN S.TYPE = 'project' THEN discount ELSE 0 END) AS project_discount
  , COUNT(CASE WHEN S.TYPE = 'tasks' THEN id ELSE 0 END) AS task_orders
  , SUM(CASE WHEN S.TYPE = 'task' THEN productcount ELSE 0 END) AS task_productcount
  , SUM(CASE WHEN S.TYPE = 'task' THEN platformcount ELSE 0 END) AS task_platformcount
  , SUM(CASE WHEN S.TYPE = 'task' THEN revenue ELSE 0 END) AS task_revenue
  , SUM(CASE WHEN S.TYPE = 'task' THEN profit ELSE 0 END) AS task_profit
  , SUM(CASE WHEN S.TYPE = 'task' THEN discount ELSE 0 END) AS task_discount
  , COUNT(CASE WHEN S.TYPE = 'template' THEN id ELSE 0 END) AS template_orders
  , SUM(CASE WHEN S.TYPE = 'template' THEN productcount ELSE 0 END) AS template_productcount
  , SUM(CASE WHEN S.TYPE = 'tempalte' THEN platformcount ELSE 0 END) AS template_platformcount
  , SUM(CASE WHEN S.TYPE = 'template' THEN revenue ELSE 0 END) AS template_revenue
  , SUM(CASE WHEN S.TYPE = 'template' THEN profit ELSE 0 END) AS template_profit
  , SUM(CASE WHEN S.TYPE = 'template' THEN discount ELSE 0 END) AS template_discount
FROM sale S
GROUP BY 1

# models/schema.yml
version: 2

models:
  - name: revenue_summary
    columns:
      - name: timecreated
      - name: total_revenue_dim
      - name: total_diff
      - name: total_time_frames
      - name: contest_orders
      - name: contest_productcount
      - name: contest_platformcount
      - name: contest_revenue
      - name: contest_profit
      - name: contest_discount
      - name: contest_diff
      - name: contest_time_frames
      - name: project_orders
      - name: project_productcount
      - name: project_platformcount
      - name: project_revenue
      - name: project_profit
      - name: project_discount
      - name: project_diff
      - name: project_time_frames
      - name: task_orders
      - name: task_productcount
      - name: task_platformcount
      - name: task_revenue
      - name: task_profit
      - name: task_discount
      - name: task_diff
      - name: task_time_frames
      - name: template_orders
      - name: template_productcount
      - name: template_platformcount
      - name: template_revenue
      - name: template_profit
      - name: template_discount
      - name: template_diff
      - name: template_time_frames

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_summary.sql · 27 lines

source-preserved validation: parses

SELECT DATE(timecreated) AS timecreated
  , COUNT(CASE WHEN S.TYPE = 'contest' THEN id ELSE 0 END) AS contest_orders
  , SUM(CASE WHEN S.TYPE = 'contest' THEN productcount ELSE 0 END) AS contest_productcount
  , SUM(CASE WHEN S.TYPE = 'contest' THEN platformcount ELSE 0 END) AS contest_platformcount
  , SUM(CASE WHEN S.TYPE = 'contest' THEN revenue ELSE 0 END) AS contest_revenue
  , SUM(CASE WHEN S.TYPE = 'contest' THEN profit ELSE 0 END) AS contest_profit
  , SUM(CASE WHEN S.TYPE = 'contest' THEN discount ELSE 0 END) AS contest_discount
  , COUNT(CASE WHEN S.TYPE = 'project' THEN id ELSE 0 END) AS project_orders
  , SUM(CASE WHEN S.TYPE = 'project' THEN productcount ELSE 0 END) AS project_productcount
  , SUM(CASE WHEN S.TYPE = 'project' THEN platformcount ELSE 0 END) AS project_platformcount
  , SUM(CASE WHEN S.TYPE = 'project' THEN revenue ELSE 0 END) AS project_revenue
  , SUM(CASE WHEN S.TYPE = 'project' THEN profit ELSE 0 END) AS project_profit
  , SUM(CASE WHEN S.TYPE = 'project' THEN discount ELSE 0 END) AS project_discount
  , COUNT(CASE WHEN S.TYPE = 'tasks' THEN id ELSE 0 END) AS task_orders
  , SUM(CASE WHEN S.TYPE = 'task' THEN productcount ELSE 0 END) AS task_productcount
  , SUM(CASE WHEN S.TYPE = 'task' THEN platformcount ELSE 0 END) AS task_platformcount
  , SUM(CASE WHEN S.TYPE = 'task' THEN revenue ELSE 0 END) AS task_revenue
  , SUM(CASE WHEN S.TYPE = 'task' THEN profit ELSE 0 END) AS task_profit
  , SUM(CASE WHEN S.TYPE = 'task' THEN discount ELSE 0 END) AS task_discount
  , COUNT(CASE WHEN S.TYPE = 'template' THEN id ELSE 0 END) AS template_orders
  , SUM(CASE WHEN S.TYPE = 'template' THEN productcount ELSE 0 END) AS template_productcount
  , SUM(CASE WHEN S.TYPE = 'tempalte' THEN platformcount ELSE 0 END) AS template_platformcount
  , SUM(CASE WHEN S.TYPE = 'template' THEN revenue ELSE 0 END) AS template_revenue
  , SUM(CASE WHEN S.TYPE = 'template' THEN profit ELSE 0 END) AS template_profit
  , SUM(CASE WHEN S.TYPE = 'template' THEN discount ELSE 0 END) AS template_discount
FROM sale S
GROUP BY 1

revenue_summary.view.lkml · 291 lines

inferred

view: revenue_summary {
  derived_table: {
    sql: SELECT DATE(timecreated) AS timecreated
  , COUNT(CASE WHEN S.TYPE = 'contest' THEN id ELSE 0 END) AS contest_orders
  , SUM(CASE WHEN S.TYPE = 'contest' THEN productcount ELSE 0 END) AS contest_productcount
  , SUM(CASE WHEN S.TYPE = 'contest' THEN platformcount ELSE 0 END) AS contest_platformcount
  , SUM(CASE WHEN S.TYPE = 'contest' THEN revenue ELSE 0 END) AS contest_revenue
  , SUM(CASE WHEN S.TYPE = 'contest' THEN profit ELSE 0 END) AS contest_profit
  , SUM(CASE WHEN S.TYPE = 'contest' THEN discount ELSE 0 END) AS contest_discount
  , COUNT(CASE WHEN S.TYPE = 'project' THEN id ELSE 0 END) AS project_orders
  , SUM(CASE WHEN S.TYPE = 'project' THEN productcount ELSE 0 END) AS project_productcount
  , SUM(CASE WHEN S.TYPE = 'project' THEN platformcount ELSE 0 END) AS project_platformcount
  , SUM(CASE WHEN S.TYPE = 'project' THEN revenue ELSE 0 END) AS project_revenue
  , SUM(CASE WHEN S.TYPE = 'project' THEN profit ELSE 0 END) AS project_profit
  , SUM(CASE WHEN S.TYPE = 'project' THEN discount ELSE 0 END) AS project_discount
  , COUNT(CASE WHEN S.TYPE = 'tasks' THEN id ELSE 0 END) AS task_orders
  , SUM(CASE WHEN S.TYPE = 'task' THEN productcount ELSE 0 END) AS task_productcount
  , SUM(CASE WHEN S.TYPE = 'task' THEN platformcount ELSE 0 END) AS task_platformcount
  , SUM(CASE WHEN S.TYPE = 'task' THEN revenue ELSE 0 END) AS task_revenue
  , SUM(CASE WHEN S.TYPE = 'task' THEN profit ELSE 0 END) AS task_profit
  , SUM(CASE WHEN S.TYPE = 'task' THEN discount ELSE 0 END) AS task_discount
  , COUNT(CASE WHEN S.TYPE = 'template' THEN id ELSE 0 END) AS template_orders
  , SUM(CASE WHEN S.TYPE = 'template' THEN productcount ELSE 0 END) AS template_productcount
  , SUM(CASE WHEN S.TYPE = 'tempalte' THEN platformcount ELSE 0 END) AS template_platformcount
  , SUM(CASE WHEN S.TYPE = 'template' THEN revenue ELSE 0 END) AS template_revenue
  , SUM(CASE WHEN S.TYPE = 'template' THEN profit ELSE 0 END) AS template_profit
  , SUM(CASE WHEN S.TYPE = 'template' THEN discount ELSE 0 END) AS template_discount
FROM sale S
GROUP BY 1 ;;
    persist_for: "12 hours"
  }

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

  dimension: total_revenue_dim {
    hidden: yes
    sql: ${contest_revenue} + ${project_revenue} + ${task_revenue} + ${template_revenue} ;;
  }

  dimension: total_diff {
    type: number
    hidden: yes
    sql: ${total_revenue} - ${targets.totals} ;;
  }

  dimension: total_time_frames {
    sql: ${TABLE}.timecreated ;;
  }

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

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

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

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

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

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

  dimension: contest_diff {
    type: number
    hidden: yes
    sql: ${contest_revenue} - ${targets.contests} ;;
  }

  dimension: contest_time_frames {
    sql: ${TABLE}.timecreated ;;
  }

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

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

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

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

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

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

  dimension: project_diff {
    type: number
    hidden: yes
    sql: ${project_revenue} - ${targets.projects} ;;
  }

  dimension: project_time_frames {
    sql: ${TABLE}.timecreated ;;
  }

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

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

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

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

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

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

  dimension: task_diff {
    type: number
    hidden: yes
    sql: ${task_revenue} - ${targets.tasks} ;;
  }

  dimension: task_time_frames {
    sql: ${TABLE}.timecreated ;;
  }

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

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

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

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

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

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

  dimension: template_diff {
    type: number
    hidden: yes
    sql: ${template_revenue} - ${targets.templates} ;;
  }

  dimension: template_time_frames {
    sql: ${TABLE}.timecreated ;;
  }

  measure: total_revenue {
    type: sum
    sql: ${total_revenue_dim} ;;
  }

  measure: total_targ {
    type: sum
    sql: ${targets.totals} ;;
  }

  measure: total_total_diff {
    type: sum
    sql: ${total_diff} ;;
  }

  measure: contest_total_revenue {
    type: sum
    sql: ${contest_revenue} ;;
  }

  measure: contest_targ {
    type: sum
    sql: ${targets.contests} ;;
  }

  measure: contest_total_diff {
    type: sum
    sql: ${contest_diff} ;;
  }

  measure: project_total_revenue {
    type: sum
    sql: ${project_revenue} ;;
  }

  measure: project_targ {
    type: sum
    sql: ${targets.projects} ;;
  }

  measure: project_total_diff {
    type: sum
    sql: ${project_diff} ;;
  }

  measure: task_total_revenue {
    type: sum
    sql: ${task_revenue} ;;
  }

  measure: task_targ {
    type: sum
    sql: ${targets.tasks} ;;
  }

  measure: task_total_diff {
    type: sum
    sql: ${task_diff} ;;
  }

  measure: template_total_revenue {
    type: sum
    sql: ${template_revenue} ;;
  }

  measure: template_targ {
    type: sum
    sql: ${targets.templates} ;;
  }

  measure: template_total_diff {
    type: sum
    sql: ${template_diff} ;;
  }
}

revenue_summary.view.lookml · 188 lines

source-preserved

- view: revenue_summary
  derived_table:
    sql: SELECT DATE(timecreated) AS timecreated
  , COUNT(CASE WHEN S.TYPE = 'contest' THEN id ELSE 0 END) AS contest_orders
  , SUM(CASE WHEN S.TYPE = 'contest' THEN productcount ELSE 0 END) AS contest_productcount
  , SUM(CASE WHEN S.TYPE = 'contest' THEN platformcount ELSE 0 END) AS contest_platformcount
  , SUM(CASE WHEN S.TYPE = 'contest' THEN revenue ELSE 0 END) AS contest_revenue
  , SUM(CASE WHEN S.TYPE = 'contest' THEN profit ELSE 0 END) AS contest_profit
  , SUM(CASE WHEN S.TYPE = 'contest' THEN discount ELSE 0 END) AS contest_discount
  , COUNT(CASE WHEN S.TYPE = 'project' THEN id ELSE 0 END) AS project_orders
  , SUM(CASE WHEN S.TYPE = 'project' THEN productcount ELSE 0 END) AS project_productcount
  , SUM(CASE WHEN S.TYPE = 'project' THEN platformcount ELSE 0 END) AS project_platformcount
  , SUM(CASE WHEN S.TYPE = 'project' THEN revenue ELSE 0 END) AS project_revenue
  , SUM(CASE WHEN S.TYPE = 'project' THEN profit ELSE 0 END) AS project_profit
  , SUM(CASE WHEN S.TYPE = 'project' THEN discount ELSE 0 END) AS project_discount
  , COUNT(CASE WHEN S.TYPE = 'tasks' THEN id ELSE 0 END) AS task_orders
  , SUM(CASE WHEN S.TYPE = 'task' THEN productcount ELSE 0 END) AS task_productcount
  , SUM(CASE WHEN S.TYPE = 'task' THEN platformcount ELSE 0 END) AS task_platformcount
  , SUM(CASE WHEN S.TYPE = 'task' THEN revenue ELSE 0 END) AS task_revenue
  , SUM(CASE WHEN S.TYPE = 'task' THEN profit ELSE 0 END) AS task_profit
  , SUM(CASE WHEN S.TYPE = 'task' THEN discount ELSE 0 END) AS task_discount
  , COUNT(CASE WHEN S.TYPE = 'template' THEN id ELSE 0 END) AS template_orders
  , SUM(CASE WHEN S.TYPE = 'template' THEN productcount ELSE 0 END) AS template_productcount
  , SUM(CASE WHEN S.TYPE = 'tempalte' THEN platformcount ELSE 0 END) AS template_platformcount
  , SUM(CASE WHEN S.TYPE = 'template' THEN revenue ELSE 0 END) AS template_revenue
  , SUM(CASE WHEN S.TYPE = 'template' THEN profit ELSE 0 END) AS template_profit
  , SUM(CASE WHEN S.TYPE = 'template' THEN discount ELSE 0 END) AS template_discount
FROM sale S
GROUP BY 1
    persist_for: 12 hours
  fields:
  - dimension: timecreated
    type: time
    sql: ${TABLE}.timecreated
  - dimension: total_revenue_dim
    hidden: true
    sql: ${contest_revenue} + ${project_revenue} + ${task_revenue} + ${template_revenue}
  - dimension: total_diff
    type: number
    hidden: true
    sql: ${total_revenue} - ${targets.totals}
  - dimension: total_time_frames
    sql: ${TABLE}.timecreated
  - dimension: contest_orders
    type: number
    sql: ${TABLE}.contest_orders
  - dimension: contest_productcount
    type: number
    sql: ${TABLE}.contest_productcount
  - dimension: contest_platformcount
    type: number
    sql: ${TABLE}.contest_platformcount
  - dimension: contest_revenue
    type: number
    hidden: true
    sql: ${TABLE}.contest_revenue
  - dimension: contest_profit
    type: number
    sql: ${TABLE}.contest_profit
  - dimension: contest_discount
    type: number
    sql: ${TABLE}.contest_discount
  - dimension: contest_diff
    type: number
    hidden: true
    sql: ${contest_revenue} - ${targets.contests}
  - dimension: contest_time_frames
    sql: ${TABLE}.timecreated
  - dimension: project_orders
    type: number
    sql: ${TABLE}.project_orders
  - dimension: project_productcount
    type: number
    sql: ${TABLE}.project_productcount
  - dimension: project_platformcount
    type: number
    sql: ${TABLE}.project_platformcount
  - dimension: project_revenue
    type: number
    hidden: true
    sql: ${TABLE}.project_revenue
  - dimension: project_profit
    type: number
    sql: ${TABLE}.project_profit
  - dimension: project_discount
    type: number
    sql: ${TABLE}.project_discount
  - dimension: project_diff
    type: number
    hidden: true
    sql: ${project_revenue} - ${targets.projects}
  - dimension: project_time_frames
    sql: ${TABLE}.timecreated
  - dimension: task_orders
    type: number
    sql: ${TABLE}.task_orders
  - dimension: task_productcount
    type: number
    sql: ${TABLE}.task_productcount
  - dimension: task_platformcount
    type: number
    sql: ${TABLE}.task_platformcount
  - dimension: task_revenue
    type: number
    hidden: true
    sql: ${TABLE}.task_revenue
  - dimension: task_profit
    type: number
    sql: ${TABLE}.task_profit
  - dimension: task_discount
    type: number
    sql: ${TABLE}.task_discount
  - dimension: task_diff
    type: number
    hidden: true
    sql: ${task_revenue} - ${targets.tasks}
  - dimension: task_time_frames
    sql: ${TABLE}.timecreated
  - dimension: template_orders
    type: number
    sql: ${TABLE}.template_orders
  - dimension: template_productcount
    type: number
    sql: ${TABLE}.template_productcount
  - dimension: template_platformcount
    type: number
    sql: ${TABLE}.template_platformcount
  - dimension: template_revenue
    type: number
    hidden: true
    sql: ${TABLE}.template_revenue
  - dimension: template_profit
    type: number
    sql: ${TABLE}.template_profit
  - dimension: template_discount
    type: number
    sql: ${TABLE}.template_discount
  - dimension: template_diff
    type: number
    hidden: true
    sql: ${template_revenue} - ${targets.templates}
  - dimension: template_time_frames
    sql: ${TABLE}.timecreated
  - measure: total_revenue
    type: sum
    sql: ${total_revenue_dim}
  - measure: total_targ
    type: sum
    sql: ${targets.totals}
  - measure: total_total_diff
    type: sum
    sql: ${total_diff}
  - measure: contest_total_revenue
    type: sum
    sql: ${contest_revenue}
  - measure: contest_targ
    type: sum
    sql: ${targets.contests}
  - measure: contest_total_diff
    type: sum
    sql: ${contest_diff}
  - measure: project_total_revenue
    type: sum
    sql: ${project_revenue}
  - measure: project_targ
    type: sum
    sql: ${targets.projects}
  - measure: project_total_diff
    type: sum
    sql: ${project_diff}
  - measure: task_total_revenue
    type: sum
    sql: ${task_revenue}
  - measure: task_targ
    type: sum
    sql: ${targets.tasks}
  - measure: task_total_diff
    type: sum
    sql: ${task_diff}
  - measure: template_total_revenue
    type: sum
    sql: ${template_revenue}
  - measure: template_targ
    type: sum
    sql: ${targets.templates}
  - measure: template_total_diff
    type: sum
    sql: ${template_diff}

revenue_summary.sql
revenue_summary.sql
revenue_summary.view.lkml
revenue_summary.view.lookml

Revenue Summary

Blocks

Assumptions

Contents