SQL Design marketplace

Transaction Facts

Transaction Facts model for design marketplace.

source-preserved

Blocks

dbt SQL LKML LookML

transaction_facts.sql · 65 lines

inferred

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

SELECT
  proposal.subscriber_id AS maker_id
  , COUNT(*) AS lifetime_transactions
  , (SUM(CASE WHEN proposal.accepted_on > '2014-01-01' THEN 1 ELSE 0 END)) AS 2014_transactions
  , SUM(CASE WHEN project.job_type = 1 THEN 1 ELSE 0 END) AS lifetime_private_transactions
  , SUM(CASE WHEN project.job_type = 2 THEN 1 ELSE 0 END) AS lifetime_public_transactions
  , SUM(CASE WHEN DATEDIFF(proposal.delivery_date, project.done_on) > -1 THEN 1 ELSE 0 END) AS on_time_completed_jobs
  , SUM(CASE WHEN DATEDIFF(proposal.delivery_date, project.done_on) < 0 THEN 1 ELSE 0 END) AS late_completed_jobs
  , AVG(DATEDIFF(project.done_on, proposal.accepted_on)) AS average_days_job_completion
  , SUM(CASE WHEN project.status = 8 THEN 1 ELSE 0 END) AS in_progress_jobs
  , MIN(proposal.accepted_on) AS initial_transaction
  , MAX(proposal.accepted_on) AS last_transaction
  , ROUND(AVG(proposal.amount + proposal.shipping),2) AS average_gross_transaction_value
  , ROUND(SUM(proposal.amount + proposal.shipping),2) AS lifetime_gross_transaction_value
  , ROUND(SUM(CASE WHEN project.job_type = 1 THEN proposal.amount + proposal.shipping ELSE 0 END),2) AS lifetime_private_gross_transaction_value
  , ROUND(SUM(CASE WHEN project.job_type = 2 THEN proposal.amount + proposal.shipping ELSE 0 END),2) AS lifetime_public_gross_transaction_value
  , ROUND(SUM(CASE WHEN proposal.accepted_on > '2014-01-01' THEN proposal.amount + proposal.shipping ELSE 0 END),2) AS 2014_gross_transaction_value
  , ROUND(AVG(CASE WHEN proposal.accepted_on > '2014-01-01' THEN (proposal.amount + proposal.shipping) ELSE 0 END),2) AS 2014_average_gross_transaction_value
  , COUNT(DISTINCT(review.id)) AS review_count
  , AVG(review.rating*1.00000) AS average_review_score
  , MIN(proposal.delivery_date) AS first_delivery
  , MAX(proposal.delivery_date) AS last_delivery
FROM
  tracking_affiliatelead AS project
  LEFT JOIN marketplace_partnerlead AS request ON request.id = project.affiliate_body_id
  LEFT JOIN project_proposals AS proposal ON proposal.project_id = project.id
  LEFT JOIN jobboard_review as review ON review.project_id = project.id
WHERE
  proposal.accepted_on IS NOT NULL
  AND project.bi_duplicate_source IS NULL
  AND project.status NOT IN (1,7)
GROUP BY
  maker_id

# models/schema.yml
version: 2

models:
  - name: transaction_facts
    columns:
      - name: maker_id
        tests:
          - unique
          - not_null
      - name: average_review_score
      - name: average_review_score_tiered
      - name: lifetime_transactions
      - name: 2014_transactions
      - name: lifetime_private_transactions
      - name: lifetime_public_transactions
      - name: on_time_completed_jobs
      - name: average_days_job_completion
      - name: in_progress_jobs
      - name: lifetime_transactions_tiered
      - name: lifetime_transactions_tiered2
      - name: 2014_gross_transaction_value
      - name: average_gross_transaction_value
      - name: 2014_average_gross_transaction_value
      - name: lifetime_gross_transaction_value
      - name: lifetime_gross_transaction_value_tiered
      - name: review_count
      - name: lifetime_project_on_time_completion_percentage

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.

transaction_facts.sql · 33 lines

source-preserved validation: not checked

SELECT
  proposal.subscriber_id AS maker_id
  , COUNT(*) AS lifetime_transactions
  , (SUM(CASE WHEN proposal.accepted_on > '2014-01-01' THEN 1 ELSE 0 END)) AS 2014_transactions
  , SUM(CASE WHEN project.job_type = 1 THEN 1 ELSE 0 END) AS lifetime_private_transactions
  , SUM(CASE WHEN project.job_type = 2 THEN 1 ELSE 0 END) AS lifetime_public_transactions
  , SUM(CASE WHEN DATEDIFF(proposal.delivery_date, project.done_on) > -1 THEN 1 ELSE 0 END) AS on_time_completed_jobs
  , SUM(CASE WHEN DATEDIFF(proposal.delivery_date, project.done_on) < 0 THEN 1 ELSE 0 END) AS late_completed_jobs
  , AVG(DATEDIFF(project.done_on, proposal.accepted_on)) AS average_days_job_completion
  , SUM(CASE WHEN project.status = 8 THEN 1 ELSE 0 END) AS in_progress_jobs
  , MIN(proposal.accepted_on) AS initial_transaction
  , MAX(proposal.accepted_on) AS last_transaction
  , ROUND(AVG(proposal.amount + proposal.shipping),2) AS average_gross_transaction_value
  , ROUND(SUM(proposal.amount + proposal.shipping),2) AS lifetime_gross_transaction_value
  , ROUND(SUM(CASE WHEN project.job_type = 1 THEN proposal.amount + proposal.shipping ELSE 0 END),2) AS lifetime_private_gross_transaction_value
  , ROUND(SUM(CASE WHEN project.job_type = 2 THEN proposal.amount + proposal.shipping ELSE 0 END),2) AS lifetime_public_gross_transaction_value
  , ROUND(SUM(CASE WHEN proposal.accepted_on > '2014-01-01' THEN proposal.amount + proposal.shipping ELSE 0 END),2) AS 2014_gross_transaction_value
  , ROUND(AVG(CASE WHEN proposal.accepted_on > '2014-01-01' THEN (proposal.amount + proposal.shipping) ELSE 0 END),2) AS 2014_average_gross_transaction_value
  , COUNT(DISTINCT(review.id)) AS review_count
  , AVG(review.rating*1.00000) AS average_review_score
  , MIN(proposal.delivery_date) AS first_delivery
  , MAX(proposal.delivery_date) AS last_delivery
FROM
  tracking_affiliatelead AS project
  LEFT JOIN marketplace_partnerlead AS request ON request.id = project.affiliate_body_id
  LEFT JOIN project_proposals AS proposal ON proposal.project_id = project.id
  LEFT JOIN jobboard_review as review ON review.project_id = project.id
WHERE
  proposal.accepted_on IS NOT NULL
  AND project.bi_duplicate_source IS NULL
  AND project.status NOT IN (1,7)
GROUP BY
  maker_id

transaction_facts.view.lkml · 158 lines

inferred

view: transaction_facts {
  derived_table: {
    sql: SELECT
  proposal.subscriber_id AS maker_id
  , COUNT(*) AS lifetime_transactions
  , (SUM(CASE WHEN proposal.accepted_on > '2014-01-01' THEN 1 ELSE 0 END)) AS 2014_transactions
  , SUM(CASE WHEN project.job_type = 1 THEN 1 ELSE 0 END) AS lifetime_private_transactions
  , SUM(CASE WHEN project.job_type = 2 THEN 1 ELSE 0 END) AS lifetime_public_transactions
  , SUM(CASE WHEN DATEDIFF(proposal.delivery_date, project.done_on) > -1 THEN 1 ELSE 0 END) AS on_time_completed_jobs
  , SUM(CASE WHEN DATEDIFF(proposal.delivery_date, project.done_on) < 0 THEN 1 ELSE 0 END) AS late_completed_jobs
  , AVG(DATEDIFF(project.done_on, proposal.accepted_on)) AS average_days_job_completion
  , SUM(CASE WHEN project.status = 8 THEN 1 ELSE 0 END) AS in_progress_jobs
  , MIN(proposal.accepted_on) AS initial_transaction
  , MAX(proposal.accepted_on) AS last_transaction
  , ROUND(AVG(proposal.amount + proposal.shipping),2) AS average_gross_transaction_value
  , ROUND(SUM(proposal.amount + proposal.shipping),2) AS lifetime_gross_transaction_value
  , ROUND(SUM(CASE WHEN project.job_type = 1 THEN proposal.amount + proposal.shipping ELSE 0 END),2) AS lifetime_private_gross_transaction_value
  , ROUND(SUM(CASE WHEN project.job_type = 2 THEN proposal.amount + proposal.shipping ELSE 0 END),2) AS lifetime_public_gross_transaction_value
  , ROUND(SUM(CASE WHEN proposal.accepted_on > '2014-01-01' THEN proposal.amount + proposal.shipping ELSE 0 END),2) AS 2014_gross_transaction_value
  , ROUND(AVG(CASE WHEN proposal.accepted_on > '2014-01-01' THEN (proposal.amount + proposal.shipping) ELSE 0 END),2) AS 2014_average_gross_transaction_value
  , COUNT(DISTINCT(review.id)) AS review_count
  , AVG(review.rating*1.00000) AS average_review_score
  , MIN(proposal.delivery_date) AS first_delivery
  , MAX(proposal.delivery_date) AS last_delivery
FROM
  tracking_affiliatelead AS project
  LEFT JOIN marketplace_partnerlead AS request ON request.id = project.affiliate_body_id
  LEFT JOIN project_proposals AS proposal ON proposal.project_id = project.id
  LEFT JOIN jobboard_review as review ON review.project_id = project.id
WHERE
  proposal.accepted_on IS NOT NULL
  AND project.bi_duplicate_source IS NULL
  AND project.status NOT IN (1,7)
GROUP BY
  maker_id ;;
    persist_for: "60 minutes"
  }

  dimension: maker_id {
    type: int
    primary_key: yes
    hidden: yes
  }

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

  dimension: average_review_score_tiered {
    type: tier
    sql: ${TABLE}.average_review_score ;;
  }

  dimension: lifetime_transactions {
    type: number
  }

  dimension: 2014_transactions {
    type: number
    sql: ${TABLE}.2014_transactions ;;
  }

  dimension: lifetime_private_transactions {
    type: number
  }

  dimension: lifetime_public_transactions {
    type: number
  }

  dimension: on_time_completed_jobs {
    type: int
    hidden: yes
  }

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

  dimension: in_progress_jobs {
    type: int
  }

  dimension: lifetime_transactions_tiered {
    type: tier
    sql: ${lifetime_transactions} ;;
  }

  dimension: lifetime_transactions_tiered2 {
    type: tier
    sql: ${lifetime_transactions} ;;
  }

  dimension: 2014_gross_transaction_value {
    type: number
  }

  dimension: average_gross_transaction_value {
    type: number
  }

  dimension: 2014_average_gross_transaction_value {
    type: number
  }

  dimension: lifetime_gross_transaction_value {
    type: number
  }

  dimension: lifetime_gross_transaction_value_tiered {
    type: tier
    sql: ${lifetime_gross_transaction_value} ;;
  }

  dimension: review_count {
    type: int
  }

  dimension: lifetime_project_on_time_completion_percentage {
    type: number
    sql: 100.00 * ${TABLE}.on_time_completed_jobs/(${TABLE}.on_time_completed_jobs + ${TABLE}.late_completed_jobs) ;;
  }

  dimension_group: initial_transaction {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.initial_transaction ;;
  }

  dimension_group: last_transaction {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.last_transaction ;;
  }

  dimension_group: first_scheduled_delivery {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.first_delivery ;;
  }

  measure: total_2014_gross_transaction_value {
    type: sum
    sql: ${2014_gross_transaction_value} ;;
  }

  measure: total_lifetime_gross_transaction_value {
    type: sum
    sql: ${lifetime_gross_transaction_value} ;;
  }

  measure: percentage_of_projects_reviewed {
    type: number
    sql: 100.00 * ${review_count}/${lifetime_transactions} ;;
  }
}

transaction_facts.view.lookml · 117 lines

source-preserved

- view: transaction_facts
  derived_table:
    sql: SELECT
  proposal.subscriber_id AS maker_id
  , COUNT(*) AS lifetime_transactions
  , (SUM(CASE WHEN proposal.accepted_on > '2014-01-01' THEN 1 ELSE 0 END)) AS 2014_transactions
  , SUM(CASE WHEN project.job_type = 1 THEN 1 ELSE 0 END) AS lifetime_private_transactions
  , SUM(CASE WHEN project.job_type = 2 THEN 1 ELSE 0 END) AS lifetime_public_transactions
  , SUM(CASE WHEN DATEDIFF(proposal.delivery_date, project.done_on) > -1 THEN 1 ELSE 0 END) AS on_time_completed_jobs
  , SUM(CASE WHEN DATEDIFF(proposal.delivery_date, project.done_on) < 0 THEN 1 ELSE 0 END) AS late_completed_jobs
  , AVG(DATEDIFF(project.done_on, proposal.accepted_on)) AS average_days_job_completion
  , SUM(CASE WHEN project.status = 8 THEN 1 ELSE 0 END) AS in_progress_jobs
  , MIN(proposal.accepted_on) AS initial_transaction
  , MAX(proposal.accepted_on) AS last_transaction
  , ROUND(AVG(proposal.amount + proposal.shipping),2) AS average_gross_transaction_value
  , ROUND(SUM(proposal.amount + proposal.shipping),2) AS lifetime_gross_transaction_value
  , ROUND(SUM(CASE WHEN project.job_type = 1 THEN proposal.amount + proposal.shipping ELSE 0 END),2) AS lifetime_private_gross_transaction_value
  , ROUND(SUM(CASE WHEN project.job_type = 2 THEN proposal.amount + proposal.shipping ELSE 0 END),2) AS lifetime_public_gross_transaction_value
  , ROUND(SUM(CASE WHEN proposal.accepted_on > '2014-01-01' THEN proposal.amount + proposal.shipping ELSE 0 END),2) AS 2014_gross_transaction_value
  , ROUND(AVG(CASE WHEN proposal.accepted_on > '2014-01-01' THEN (proposal.amount + proposal.shipping) ELSE 0 END),2) AS 2014_average_gross_transaction_value
  , COUNT(DISTINCT(review.id)) AS review_count
  , AVG(review.rating*1.00000) AS average_review_score
  , MIN(proposal.delivery_date) AS first_delivery
  , MAX(proposal.delivery_date) AS last_delivery
FROM
  tracking_affiliatelead AS project
  LEFT JOIN marketplace_partnerlead AS request ON request.id = project.affiliate_body_id
  LEFT JOIN project_proposals AS proposal ON proposal.project_id = project.id
  LEFT JOIN jobboard_review as review ON review.project_id = project.id
WHERE
  proposal.accepted_on IS NOT NULL
  AND project.bi_duplicate_source IS NULL
  AND project.status NOT IN (1,7)
GROUP BY
  maker_id
    persist_for: 60 minutes
  fields:
  - dimension: maker_id
    type: int
    primary_key: true
    hidden: true
  - dimension: average_review_score
    type: number
    sql: ${TABLE}.average_review_score
  - dimension: average_review_score_tiered
    type: tier
    sql: ${TABLE}.average_review_score
  - dimension: lifetime_transactions
    type: number
  - dimension: 2014_transactions
    type: number
    sql: ${TABLE}.2014_transactions
  - dimension: lifetime_private_transactions
    type: number
  - dimension: lifetime_public_transactions
    type: number
  - dimension: on_time_completed_jobs
    type: int
    hidden: true
  - dimension: average_days_job_completion
    type: number
    sql: ${TABLE}.average_days_job_completion
  - dimension: in_progress_jobs
    type: int
  - dimension: lifetime_transactions_tiered
    type: tier
    sql: ${lifetime_transactions}
  - dimension: lifetime_transactions_tiered2
    type: tier
    sql: ${lifetime_transactions}
  - dimension: 2014_gross_transaction_value
    type: number
  - dimension: average_gross_transaction_value
    type: number
  - dimension: 2014_average_gross_transaction_value
    type: number
  - dimension: lifetime_gross_transaction_value
    type: number
  - dimension: lifetime_gross_transaction_value_tiered
    type: tier
    sql: ${lifetime_gross_transaction_value}
  - dimension: review_count
    type: int
  - dimension: lifetime_project_on_time_completion_percentage
    type: number
    sql: 100.00 * ${TABLE}.on_time_completed_jobs/(${TABLE}.on_time_completed_jobs + ${TABLE}.late_completed_jobs)
  - dimension_group: initial_transaction
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.initial_transaction
  - dimension_group: last_transaction
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.last_transaction
  - dimension_group: first_scheduled_delivery
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.first_delivery
  - measure: total_2014_gross_transaction_value
    type: sum
    sql: ${2014_gross_transaction_value}
  - measure: total_lifetime_gross_transaction_value
    type: sum
    sql: ${lifetime_gross_transaction_value}
  - measure: percentage_of_projects_reviewed
    type: number
    sql: 100.00 * ${review_count}/${lifetime_transactions}

transaction_facts.sql
transaction_facts.sql
transaction_facts.view.lkml
transaction_facts.view.lookml

Transaction Facts

Blocks

Assumptions

Contents