SQL Crowdfunding education

Teacher Facts

Teacher Facts model for crowdfunding education.

source-preserved

Blocks

dbt SQL LKML LookML

teacher_facts.sql · 72 lines

inferred

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

SELECT
  p.createduserid AS teacherid
  , SUM(d.amounttotal) AS lifetime_donated_amount
  , COUNT(DISTINCT p.schoolid) AS lifetime_schools
  , MIN(d.created) AS first_donation
  , CURRENT_DATE - MAX(d.created) AS days_since_last_donation
  , COUNT(DISTINCT d.donationid) AS lifetime_donations
  , MAX(posted_date.posted) AS last_project_posted_date
  , MIN(posted_date.posted) AS first_project_posted_date
  , MAX(funded_date.funded) AS last_funded_project_date
  , MIN(funded_date.funded) AS first_funded_project_date
  , COUNT(DISTINCT CASE WHEN posted_date.posted IS NOT NULL THEN p.proposalid ELSE NULL END) AS lifetime_posted_projects
  , COUNT(DISTINCT CASE WHEN p.fullyfunded THEN p.proposalid ELSE NULL END) AS projects_fully_funded
  , COUNT(DISTINCT CASE
      WHEN p.fullyfunded IS FALSE
        AND posted_date.posted IS NOT NULL
        AND p.expirationdate < current_date
      THEN p.proposalid ELSE NULL END) AS projects_failed
  , ARRAY_TO_STRING(ARRAY(SELECT unnest(array_agg(DISTINCT (CASE WHEN p.fullyfunded THEN
    (SELECT DISTINCT
      (SELECT 'FY' || to_char(fiscal_year_start(MAX(datecreated)) + interval '1 year', 'YY')
      FROM proposalworkflowstatus pwfs
      WHERE workflowstatusid = 10
      AND pwfs.proposalid = p.proposalid)
    ) ELSE NULL END))) ORDER BY 1), ', ') AS projects_fully_funded_fy_list
  , COUNT(DISTINCT CASE WHEN p.currentworkflowstatuscode BETWEEN 6 AND 9 AND p.expirationdate >= current_date THEN p.proposalid ELSE NULL END) AS projects_live
  , COUNT(DISTINCT CASE WHEN p.currentworkflowstatuscode BETWEEN 3 AND 5 THEN p.proposalid ELSE NULL END) AS projects_under_review
  , COUNT(DISTINCT CASE WHEN p.currentworkflowstatuscode = 1 THEN p.proposalid ELSE NULL END) AS projects_draft

FROM proposal_data p
LEFT JOIN donation_data d ON p.proposalid = d.proposalid
LEFT JOIN (SELECT pwfs.proposalid, MAX(datecreated) posted
          FROM proposalworkflowstatus pwfs
          WHERE workflowstatusid = 6
          GROUP BY proposalid) posted_date ON posted_date.proposalid = p.proposalid
LEFT JOIN (SELECT pwfs.proposalid, MAX(datecreated) funded
          FROM proposalworkflowstatus pwfs
          WHERE workflowstatusid = 10
          GROUP BY proposalid) funded_date ON funded_date.proposalid = p.proposalid
GROUP BY p.createduserid

# models/schema.yml
version: 2

models:
  - name: teacher_facts
    columns:
      - name: teacherid
        tests:
          - unique
          - not_null
      - name: days_since_last_donation
      - name: lifetime_donations
      - name: lifetime_donations_tier
      - name: lifetime_donated_amount
      - name: lifetime_schools
      - name: last_project_posted_date
      - name: lifetime_posted_projects
      - name: projects_fully_funded
      - name: projects_failed
      - name: success_rate
      - name: never_before_funded
      - name: projects_live
      - name: projects_under_review
      - name: projects_draft
      - name: lifetime_projects_tier
      - name: projects_fully_funded_fy_list
      - name: has_funded_project_fy14
      - name: has_funded_project_fy15

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.

teacher_facts.sql · 40 lines

source-preserved validation: parses

SELECT
  p.createduserid AS teacherid
  , SUM(d.amounttotal) AS lifetime_donated_amount
  , COUNT(DISTINCT p.schoolid) AS lifetime_schools
  , MIN(d.created) AS first_donation
  , CURRENT_DATE - MAX(d.created) AS days_since_last_donation
  , COUNT(DISTINCT d.donationid) AS lifetime_donations
  , MAX(posted_date.posted) AS last_project_posted_date
  , MIN(posted_date.posted) AS first_project_posted_date
  , MAX(funded_date.funded) AS last_funded_project_date
  , MIN(funded_date.funded) AS first_funded_project_date
  , COUNT(DISTINCT CASE WHEN posted_date.posted IS NOT NULL THEN p.proposalid ELSE NULL END) AS lifetime_posted_projects
  , COUNT(DISTINCT CASE WHEN p.fullyfunded THEN p.proposalid ELSE NULL END) AS projects_fully_funded
  , COUNT(DISTINCT CASE
      WHEN p.fullyfunded IS FALSE
        AND posted_date.posted IS NOT NULL
        AND p.expirationdate < current_date
      THEN p.proposalid ELSE NULL END) AS projects_failed
  , ARRAY_TO_STRING(ARRAY(SELECT unnest(array_agg(DISTINCT (CASE WHEN p.fullyfunded THEN
    (SELECT DISTINCT
      (SELECT 'FY' || to_char(fiscal_year_start(MAX(datecreated)) + interval '1 year', 'YY')
      FROM proposalworkflowstatus pwfs
      WHERE workflowstatusid = 10
      AND pwfs.proposalid = p.proposalid)
    ) ELSE NULL END))) ORDER BY 1), ', ') AS projects_fully_funded_fy_list
  , COUNT(DISTINCT CASE WHEN p.currentworkflowstatuscode BETWEEN 6 AND 9 AND p.expirationdate >= current_date THEN p.proposalid ELSE NULL END) AS projects_live
  , COUNT(DISTINCT CASE WHEN p.currentworkflowstatuscode BETWEEN 3 AND 5 THEN p.proposalid ELSE NULL END) AS projects_under_review
  , COUNT(DISTINCT CASE WHEN p.currentworkflowstatuscode = 1 THEN p.proposalid ELSE NULL END) AS projects_draft

FROM proposal_data p
LEFT JOIN donation_data d ON p.proposalid = d.proposalid
LEFT JOIN (SELECT pwfs.proposalid, MAX(datecreated) posted
          FROM proposalworkflowstatus pwfs
          WHERE workflowstatusid = 6
          GROUP BY proposalid) posted_date ON posted_date.proposalid = p.proposalid
LEFT JOIN (SELECT pwfs.proposalid, MAX(datecreated) funded
          FROM proposalworkflowstatus pwfs
          WHERE workflowstatusid = 10
          GROUP BY proposalid) funded_date ON funded_date.proposalid = p.proposalid
GROUP BY p.createduserid

teacher_facts.view.lkml · 166 lines

inferred

view: teacher_facts {
  derived_table: {
    sql: SELECT
  p.createduserid AS teacherid
  , SUM(d.amounttotal) AS lifetime_donated_amount
  , COUNT(DISTINCT p.schoolid) AS lifetime_schools
  , MIN(d.created) AS first_donation
  , CURRENT_DATE - MAX(d.created) AS days_since_last_donation
  , COUNT(DISTINCT d.donationid) AS lifetime_donations
  , MAX(posted_date.posted) AS last_project_posted_date
  , MIN(posted_date.posted) AS first_project_posted_date
  , MAX(funded_date.funded) AS last_funded_project_date
  , MIN(funded_date.funded) AS first_funded_project_date
  , COUNT(DISTINCT CASE WHEN posted_date.posted IS NOT NULL THEN p.proposalid ELSE NULL END) AS lifetime_posted_projects
  , COUNT(DISTINCT CASE WHEN p.fullyfunded THEN p.proposalid ELSE NULL END) AS projects_fully_funded
  , COUNT(DISTINCT CASE
      WHEN p.fullyfunded IS FALSE
        AND posted_date.posted IS NOT NULL
        AND p.expirationdate < current_date
      THEN p.proposalid ELSE NULL END) AS projects_failed
  , ARRAY_TO_STRING(ARRAY(SELECT unnest(array_agg(DISTINCT (CASE WHEN p.fullyfunded THEN
    (SELECT DISTINCT
      (SELECT 'FY' || to_char(fiscal_year_start(MAX(datecreated)) + interval '1 year', 'YY')
      FROM proposalworkflowstatus pwfs
      WHERE workflowstatusid = 10
      AND pwfs.proposalid = p.proposalid)
    ) ELSE NULL END))) ORDER BY 1), ', ') AS projects_fully_funded_fy_list
  , COUNT(DISTINCT CASE WHEN p.currentworkflowstatuscode BETWEEN 6 AND 9 AND p.expirationdate >= current_date THEN p.proposalid ELSE NULL END) AS projects_live
  , COUNT(DISTINCT CASE WHEN p.currentworkflowstatuscode BETWEEN 3 AND 5 THEN p.proposalid ELSE NULL END) AS projects_under_review
  , COUNT(DISTINCT CASE WHEN p.currentworkflowstatuscode = 1 THEN p.proposalid ELSE NULL END) AS projects_draft

FROM proposal_data p
LEFT JOIN donation_data d ON p.proposalid = d.proposalid
LEFT JOIN (SELECT pwfs.proposalid, MAX(datecreated) posted
          FROM proposalworkflowstatus pwfs
          WHERE workflowstatusid = 6
          GROUP BY proposalid) posted_date ON posted_date.proposalid = p.proposalid
LEFT JOIN (SELECT pwfs.proposalid, MAX(datecreated) funded
          FROM proposalworkflowstatus pwfs
          WHERE workflowstatusid = 10
          GROUP BY proposalid) funded_date ON funded_date.proposalid = p.proposalid
GROUP BY p.createduserid ;;
  }

  dimension: teacherid {
    label: "Teacher ID"
    type: int
    primary_key: yes
    hidden: yes
  }

  dimension: days_since_last_donation {
    type: int
  }

  dimension: lifetime_donations {
    type: int
  }

  dimension: lifetime_donations_tier {
    type: tier
    sql: ${lifetime_donations} ;;
  }

  dimension: lifetime_donated_amount {
    type: number
  }

  dimension: lifetime_schools {
    type: int
  }

  dimension: last_project_posted_date {
    type: date
    sql: ${TABLE}.last_project_posted_date ;;
  }

  dimension: lifetime_posted_projects {
    label: "Lifetime Projects Posted"
    type: int
  }

  dimension: projects_fully_funded {
    label: "Lifetime Projects Funded"
    type: int
  }

  dimension: projects_failed {
    label: "Lifetime Projects Failed"
    type: int
  }

  dimension: success_rate {
    label: "Success Rate (Old Definition)"
    type: number
    sql: 100.0*${projects_fully_funded}/NULLIF(${lifetime_posted_projects}, 0) ;;
  }

  dimension: never_before_funded {
    type: yesno
    sql: ${projects_fully_funded} = 0 ;;
  }

  dimension: projects_live {
    label: "Number of Currently Active Projects"
    type: int
  }

  dimension: projects_under_review {
    label: "Number of Currently Under Review Projects"
    type: int
  }

  dimension: projects_draft {
    label: "Number of Currently Draft Projects"
    type: int
  }

  dimension: lifetime_projects_tier {
    label: "Lifetime Projects Posted (Tier)"
    type: tier
    sql: ${lifetime_posted_projects} ;;
  }

  dimension: projects_fully_funded_fy_list {
    label: "Projects Funded FY List"
    hidden: yes
  }

  dimension: has_funded_project_fy14 {
    label: "Has Funded Project FY14 (Yes / No)"
    type: yesno
    hidden: yes
    sql: ${projects_fully_funded_fy_list} LIKE '%FY14%' ;;
  }

  dimension: has_funded_project_fy15 {
    label: "Has Funded Project {% parameter fiscal_year_placeholder %} (Yes / No)"
    type: yesno
    hidden: yes
    sql: ${projects_fully_funded_fy_list} LIKE '%{% parameter fiscal_year_placeholder %}%' ;;
  }

  dimension_group: first_donation {
    type: time
    timeframes: [date]
  }

  dimension_group: first_project_posted {
    type: time
    timeframes: [date, dom, month_num, year]
    sql: ${TABLE}.first_project_posted_date ;;
  }

  dimension_group: first_funded_project {
    type: time
    timeframes: [date]
    sql: ${TABLE}.first_funded_project_date ;;
  }

  dimension_group: last_funded_project {
    type: time
    timeframes: [date]
    sql: ${TABLE}.last_funded_project_date ;;
  }
}

teacher_facts.view.lookml · 126 lines

source-preserved

- view: teacher_facts
  derived_table:
    sql: SELECT
  p.createduserid AS teacherid
  , SUM(d.amounttotal) AS lifetime_donated_amount
  , COUNT(DISTINCT p.schoolid) AS lifetime_schools
  , MIN(d.created) AS first_donation
  , CURRENT_DATE - MAX(d.created) AS days_since_last_donation
  , COUNT(DISTINCT d.donationid) AS lifetime_donations
  , MAX(posted_date.posted) AS last_project_posted_date
  , MIN(posted_date.posted) AS first_project_posted_date
  , MAX(funded_date.funded) AS last_funded_project_date
  , MIN(funded_date.funded) AS first_funded_project_date
  , COUNT(DISTINCT CASE WHEN posted_date.posted IS NOT NULL THEN p.proposalid ELSE NULL END) AS lifetime_posted_projects
  , COUNT(DISTINCT CASE WHEN p.fullyfunded THEN p.proposalid ELSE NULL END) AS projects_fully_funded
  , COUNT(DISTINCT CASE
      WHEN p.fullyfunded IS FALSE
        AND posted_date.posted IS NOT NULL
        AND p.expirationdate < current_date
      THEN p.proposalid ELSE NULL END) AS projects_failed
  , ARRAY_TO_STRING(ARRAY(SELECT unnest(array_agg(DISTINCT (CASE WHEN p.fullyfunded THEN
    (SELECT DISTINCT
      (SELECT 'FY' || to_char(fiscal_year_start(MAX(datecreated)) + interval '1 year', 'YY')
      FROM proposalworkflowstatus pwfs
      WHERE workflowstatusid = 10
      AND pwfs.proposalid = p.proposalid)
    ) ELSE NULL END))) ORDER BY 1), ', ') AS projects_fully_funded_fy_list
  , COUNT(DISTINCT CASE WHEN p.currentworkflowstatuscode BETWEEN 6 AND 9 AND p.expirationdate >= current_date THEN p.proposalid ELSE NULL END) AS projects_live
  , COUNT(DISTINCT CASE WHEN p.currentworkflowstatuscode BETWEEN 3 AND 5 THEN p.proposalid ELSE NULL END) AS projects_under_review
  , COUNT(DISTINCT CASE WHEN p.currentworkflowstatuscode = 1 THEN p.proposalid ELSE NULL END) AS projects_draft

FROM proposal_data p
LEFT JOIN donation_data d ON p.proposalid = d.proposalid
LEFT JOIN (SELECT pwfs.proposalid, MAX(datecreated) posted
          FROM proposalworkflowstatus pwfs
          WHERE workflowstatusid = 6
          GROUP BY proposalid) posted_date ON posted_date.proposalid = p.proposalid
LEFT JOIN (SELECT pwfs.proposalid, MAX(datecreated) funded
          FROM proposalworkflowstatus pwfs
          WHERE workflowstatusid = 10
          GROUP BY proposalid) funded_date ON funded_date.proposalid = p.proposalid
GROUP BY p.createduserid
  fields:
  - dimension: teacherid
    type: int
    primary_key: true
    hidden: true
    label: Teacher ID
  - dimension: days_since_last_donation
    type: int
  - dimension: lifetime_donations
    type: int
  - dimension: lifetime_donations_tier
    type: tier
    sql: ${lifetime_donations}
  - dimension: lifetime_donated_amount
    type: number
  - dimension: lifetime_schools
    type: int
  - dimension: last_project_posted_date
    type: date
    sql: ${TABLE}.last_project_posted_date
  - dimension: lifetime_posted_projects
    type: int
    label: Lifetime Projects Posted
  - dimension: projects_fully_funded
    type: int
    label: Lifetime Projects Funded
  - dimension: projects_failed
    type: int
    label: Lifetime Projects Failed
  - dimension: success_rate
    type: number
    label: Success Rate (Old Definition)
    sql: 100.0*${projects_fully_funded}/NULLIF(${lifetime_posted_projects}, 0)
  - dimension: never_before_funded
    type: yesno
    sql: ${projects_fully_funded} = 0
  - dimension: projects_live
    type: int
    label: Number of Currently Active Projects
  - dimension: projects_under_review
    type: int
    label: Number of Currently Under Review Projects
  - dimension: projects_draft
    type: int
    label: Number of Currently Draft Projects
  - dimension: lifetime_projects_tier
    type: tier
    label: Lifetime Projects Posted (Tier)
    sql: ${lifetime_posted_projects}
  - dimension: projects_fully_funded_fy_list
    hidden: true
    label: Projects Funded FY List
  - dimension: has_funded_project_fy14
    type: yesno
    hidden: true
    label: Has Funded Project FY14 (Yes / No)
    sql: ${projects_fully_funded_fy_list} LIKE '%FY14%'
  - dimension: has_funded_project_fy15
    type: yesno
    hidden: true
    label: 'Has Funded Project {% parameter fiscal_year_placeholder %} (Yes / No)'
    sql: ${projects_fully_funded_fy_list} LIKE '%{% parameter fiscal_year_placeholder %}%'
  - dimension_group: first_donation
    type: time
    timeframes:
    - date
  - dimension_group: first_project_posted
    type: time
    timeframes:
    - date
    - dom
    - month_num
    - year
    sql: ${TABLE}.first_project_posted_date
  - dimension_group: first_funded_project
    type: time
    timeframes:
    - date
    sql: ${TABLE}.first_funded_project_date
  - dimension_group: last_funded_project
    type: time
    timeframes:
    - date
    sql: ${TABLE}.last_funded_project_date

teacher_facts.sql
teacher_facts.sql
teacher_facts.view.lkml
teacher_facts.view.lookml

Teacher Facts

Blocks

Assumptions

Contents