SQL Crowdfunding education

School Facts

School Facts model for crowdfunding education.

source-preserved

Blocks

dbt SQL LKML LookML

school_facts.sql · 44 lines

inferred

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

SELECT
  DISTINCT s.schoolid AS schoolid
  , COUNT(DISTINCT CASE WHEN school_teacher.excluded IS FALSE THEN school_teacher.teacherid ELSE NULL END) AS teachers_registered
  , COUNT(DISTINCT p.proposalid) AS lifetime_projects
  , COUNT(DISTINCT CASE WHEN p.fullyfunded THEN p.proposalid ELSE NULL END) AS lifetime_projects_fully_funded
  , COUNT(DISTINCT CASE WHEN posted_date.posted IS NOT NULL THEN p.proposalid ELSE NULL END) AS lifetime_projects_posted

-- Why are we not starting from school? Because it will be joined to school in school view, which includes all schools
-- Why are we starting from orgteacher? Because we want all registered teachers, regardless of whether or not they posted
-- Must join school with project directly (not throught teacher), because projects stay attached to schools even if teachers move to another school
-- For currently registered teachers, must join orgteacher with school directly, not through schoolid.
-- For teachers whose projects are tied to schools, join through project onto school
  -- LEFT JOIN orgteacher project_teacher ON project_teacher.teacherid = p.createduserid
-- Too slow. Do not join LEFT JOIN proposalworkflowstatus pwfs ON p.proposalid = pwfs.proposalid

FROM school s
LEFT JOIN proposal_data p ON s.schoolid = p.schoolid
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 orgteacher school_teacher ON school_teacher.schoolid = s.schoolid
GROUP BY s.schoolid

# models/schema.yml
version: 2

models:
  - name: school_facts
    columns:
      - name: schoolid
        tests:
          - unique
          - not_null
      - name: teachers_registered
      - name: lifetime_projects
      - name: lifetime_projects_posted
      - name: lifetime_projects_fully_funded
      - name: has_lifetime_project_created
      - name: has_lifetime_project_posted
      - name: has_lifetime_project_fully_funded

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.

school_facts.sql · 23 lines

source-preserved validation: parses

SELECT
  DISTINCT s.schoolid AS schoolid
  , COUNT(DISTINCT CASE WHEN school_teacher.excluded IS FALSE THEN school_teacher.teacherid ELSE NULL END) AS teachers_registered
  , COUNT(DISTINCT p.proposalid) AS lifetime_projects
  , COUNT(DISTINCT CASE WHEN p.fullyfunded THEN p.proposalid ELSE NULL END) AS lifetime_projects_fully_funded
  , COUNT(DISTINCT CASE WHEN posted_date.posted IS NOT NULL THEN p.proposalid ELSE NULL END) AS lifetime_projects_posted

-- Why are we not starting from school? Because it will be joined to school in school view, which includes all schools
-- Why are we starting from orgteacher? Because we want all registered teachers, regardless of whether or not they posted
-- Must join school with project directly (not throught teacher), because projects stay attached to schools even if teachers move to another school
-- For currently registered teachers, must join orgteacher with school directly, not through schoolid.
-- For teachers whose projects are tied to schools, join through project onto school
  -- LEFT JOIN orgteacher project_teacher ON project_teacher.teacherid = p.createduserid
-- Too slow. Do not join LEFT JOIN proposalworkflowstatus pwfs ON p.proposalid = pwfs.proposalid

FROM school s
LEFT JOIN proposal_data p ON s.schoolid = p.schoolid
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 orgteacher school_teacher ON school_teacher.schoolid = s.schoolid
GROUP BY s.schoolid

school_facts.view.lkml · 71 lines

inferred

view: school_facts {
  derived_table: {
    sql: SELECT
  DISTINCT s.schoolid AS schoolid
  , COUNT(DISTINCT CASE WHEN school_teacher.excluded IS FALSE THEN school_teacher.teacherid ELSE NULL END) AS teachers_registered
  , COUNT(DISTINCT p.proposalid) AS lifetime_projects
  , COUNT(DISTINCT CASE WHEN p.fullyfunded THEN p.proposalid ELSE NULL END) AS lifetime_projects_fully_funded
  , COUNT(DISTINCT CASE WHEN posted_date.posted IS NOT NULL THEN p.proposalid ELSE NULL END) AS lifetime_projects_posted

-- Why are we not starting from school? Because it will be joined to school in school view, which includes all schools
-- Why are we starting from orgteacher? Because we want all registered teachers, regardless of whether or not they posted
-- Must join school with project directly (not throught teacher), because projects stay attached to schools even if teachers move to another school
-- For currently registered teachers, must join orgteacher with school directly, not through schoolid.
-- For teachers whose projects are tied to schools, join through project onto school
  -- LEFT JOIN orgteacher project_teacher ON project_teacher.teacherid = p.createduserid
-- Too slow. Do not join LEFT JOIN proposalworkflowstatus pwfs ON p.proposalid = pwfs.proposalid

FROM school s
LEFT JOIN proposal_data p ON s.schoolid = p.schoolid
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 orgteacher school_teacher ON school_teacher.schoolid = s.schoolid
GROUP BY s.schoolid ;;
  }

  dimension: schoolid {
    label: "School ID"
    type: int
    primary_key: yes
    hidden: yes
  }

  dimension: teachers_registered {
    type: int
  }

  dimension: lifetime_projects {
    label: "Lifetime Projects Created"
    type: int
  }

  dimension: lifetime_projects_posted {
    type: int
  }

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

  dimension: has_lifetime_project_created {
    type: yesno
    hidden: yes
    sql: ${lifetime_projects} > 0 ;;
  }

  dimension: has_lifetime_project_posted {
    type: yesno
    hidden: yes
    sql: ${lifetime_projects_posted} > 0 ;;
  }

  dimension: has_lifetime_project_fully_funded {
    label: "Has Lifetime Project Funded (Yes / No)"
    type: yesno
    hidden: yes
    sql: ${lifetime_projects_fully_funded} > 0 ;;
  }
}

school_facts.view.lookml · 54 lines

source-preserved

- view: school_facts
  derived_table:
    sql: SELECT
  DISTINCT s.schoolid AS schoolid
  , COUNT(DISTINCT CASE WHEN school_teacher.excluded IS FALSE THEN school_teacher.teacherid ELSE NULL END) AS teachers_registered
  , COUNT(DISTINCT p.proposalid) AS lifetime_projects
  , COUNT(DISTINCT CASE WHEN p.fullyfunded THEN p.proposalid ELSE NULL END) AS lifetime_projects_fully_funded
  , COUNT(DISTINCT CASE WHEN posted_date.posted IS NOT NULL THEN p.proposalid ELSE NULL END) AS lifetime_projects_posted

-- Why are we not starting from school? Because it will be joined to school in school view, which includes all schools
-- Why are we starting from orgteacher? Because we want all registered teachers, regardless of whether or not they posted
-- Must join school with project directly (not throught teacher), because projects stay attached to schools even if teachers move to another school
-- For currently registered teachers, must join orgteacher with school directly, not through schoolid.
-- For teachers whose projects are tied to schools, join through project onto school
  -- LEFT JOIN orgteacher project_teacher ON project_teacher.teacherid = p.createduserid
-- Too slow. Do not join LEFT JOIN proposalworkflowstatus pwfs ON p.proposalid = pwfs.proposalid

FROM school s
LEFT JOIN proposal_data p ON s.schoolid = p.schoolid
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 orgteacher school_teacher ON school_teacher.schoolid = s.schoolid
GROUP BY s.schoolid
  fields:
  - dimension: schoolid
    type: int
    primary_key: true
    hidden: true
    label: School ID
  - dimension: teachers_registered
    type: int
  - dimension: lifetime_projects
    type: int
    label: Lifetime Projects Created
  - dimension: lifetime_projects_posted
    type: int
  - dimension: lifetime_projects_fully_funded
    type: int
    label: Lifetime Projects Funded
  - dimension: has_lifetime_project_created
    type: yesno
    hidden: true
    sql: ${lifetime_projects} > 0
  - dimension: has_lifetime_project_posted
    type: yesno
    hidden: true
    sql: ${lifetime_projects_posted} > 0
  - dimension: has_lifetime_project_fully_funded
    type: yesno
    hidden: true
    label: Has Lifetime Project Funded (Yes / No)
    sql: ${lifetime_projects_fully_funded} > 0

school_facts.sql
school_facts.sql
school_facts.view.lkml
school_facts.view.lookml

School Facts

Blocks

Assumptions

Contents