SQL Crowdfunding education

Student Counter

Student Counter model for crowdfunding education.

source-preserved

Blocks

dbt SQL LKML LookML

student_counter.sql · 48 lines

inferred

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

SELECT
  p.createduserid teacherid,
  CASE WHEN p.gradetypeid IN (1,2) THEN 27 WHEN p.gradetypeid IN (3,4) THEN 70 ELSE 44 END AS class_size,
  CASE
    WHEN pwfs.fully_funded_date BETWEEN '2014-07-01' AND '2015-06-30' THEN '{% parameter fiscal_year_placeholder %}'
    WHEN pwfs.fully_funded_date BETWEEN '2013-07-01' AND '2014-06-30' THEN 'FY14'
    WHEN pwfs.fully_funded_date BETWEEN '2012-07-01' AND '2013-06-30' THEN 'FY13'
    WHEN pwfs.fully_funded_date BETWEEN '2011-07-01' AND '2012-06-30' THEN 'FY12'
    WHEN pwfs.fully_funded_date BETWEEN '2010-07-01' AND '2011-06-30' THEN 'FY11'
    WHEN pwfs.fully_funded_date BETWEEN '2009-07-01' AND '2010-06-30' THEN 'FY10'
    WHEN pwfs.fully_funded_date BETWEEN '2008-07-01' AND '2009-06-30' THEN 'FY09'
    WHEN pwfs.fully_funded_date BETWEEN '2007-07-01' AND '2008-06-30' THEN 'FY08'
    WHEN pwfs.fully_funded_date BETWEEN '2006-07-01' AND '2007-06-30' THEN 'FY07'
    WHEN pwfs.fully_funded_date BETWEEN '2005-07-01' AND '2006-06-30' THEN 'FY06'
    WHEN pwfs.fully_funded_date BETWEEN '2004-07-01' AND '2005-06-30' THEN 'FY05'
    WHEN pwfs.fully_funded_date BETWEEN '2003-07-01' AND '2004-06-30' THEN 'FY04'
    WHEN pwfs.fully_funded_date BETWEEN '2002-07-01' AND '2003-06-30' THEN 'FY03'
    WHEN pwfs.fully_funded_date BETWEEN '2001-07-01' AND '2002-06-30' THEN 'FY02'
    WHEN pwfs.fully_funded_date BETWEEN '2000-07-01' AND '2001-06-30' THEN 'FY01'
    WHEN pwfs.fully_funded_date BETWEEN '1999-07-01' AND '2000-06-30' THEN 'FY00' END AS fiscal_year_funded

FROM proposal_data p
LEFT JOIN
  (SELECT
    proposalid,
    MAX(datecreated) fully_funded_date
    FROM proposalworkflowstatus
    WHERE workflowstatusid = 10
    GROUP BY 1
  ) AS pwfs ON pwfs.proposalid = p.proposalid
WHERE pwfs.fully_funded_date IS NOT NULL
GROUP BY 1,2,3

# models/schema.yml
version: 2

models:
  - name: student_counter
    columns:
      - name: teacherid
        tests:
          - unique
          - not_null
      - name: class_size
      - name: fiscal_year_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.

student_counter.sql · 32 lines

source-preserved validation: not checked

SELECT
  p.createduserid teacherid,
  CASE WHEN p.gradetypeid IN (1,2) THEN 27 WHEN p.gradetypeid IN (3,4) THEN 70 ELSE 44 END AS class_size,
  CASE
    WHEN pwfs.fully_funded_date BETWEEN '2014-07-01' AND '2015-06-30' THEN '{% parameter fiscal_year_placeholder %}'
    WHEN pwfs.fully_funded_date BETWEEN '2013-07-01' AND '2014-06-30' THEN 'FY14'
    WHEN pwfs.fully_funded_date BETWEEN '2012-07-01' AND '2013-06-30' THEN 'FY13'
    WHEN pwfs.fully_funded_date BETWEEN '2011-07-01' AND '2012-06-30' THEN 'FY12'
    WHEN pwfs.fully_funded_date BETWEEN '2010-07-01' AND '2011-06-30' THEN 'FY11'
    WHEN pwfs.fully_funded_date BETWEEN '2009-07-01' AND '2010-06-30' THEN 'FY10'
    WHEN pwfs.fully_funded_date BETWEEN '2008-07-01' AND '2009-06-30' THEN 'FY09'
    WHEN pwfs.fully_funded_date BETWEEN '2007-07-01' AND '2008-06-30' THEN 'FY08'
    WHEN pwfs.fully_funded_date BETWEEN '2006-07-01' AND '2007-06-30' THEN 'FY07'
    WHEN pwfs.fully_funded_date BETWEEN '2005-07-01' AND '2006-06-30' THEN 'FY06'
    WHEN pwfs.fully_funded_date BETWEEN '2004-07-01' AND '2005-06-30' THEN 'FY05'
    WHEN pwfs.fully_funded_date BETWEEN '2003-07-01' AND '2004-06-30' THEN 'FY04'
    WHEN pwfs.fully_funded_date BETWEEN '2002-07-01' AND '2003-06-30' THEN 'FY03'
    WHEN pwfs.fully_funded_date BETWEEN '2001-07-01' AND '2002-06-30' THEN 'FY02'
    WHEN pwfs.fully_funded_date BETWEEN '2000-07-01' AND '2001-06-30' THEN 'FY01'
    WHEN pwfs.fully_funded_date BETWEEN '1999-07-01' AND '2000-06-30' THEN 'FY00' END AS fiscal_year_funded

FROM proposal_data p
LEFT JOIN
  (SELECT
    proposalid,
    MAX(datecreated) fully_funded_date
    FROM proposalworkflowstatus
    WHERE workflowstatusid = 10
    GROUP BY 1
  ) AS pwfs ON pwfs.proposalid = p.proposalid
WHERE pwfs.fully_funded_date IS NOT NULL
GROUP BY 1,2,3

student_counter.view.lkml · 59 lines

inferred

view: student_counter {
  derived_table: {
    sql: SELECT
  p.createduserid teacherid,
  CASE WHEN p.gradetypeid IN (1,2) THEN 27 WHEN p.gradetypeid IN (3,4) THEN 70 ELSE 44 END AS class_size,
  CASE
    WHEN pwfs.fully_funded_date BETWEEN '2014-07-01' AND '2015-06-30' THEN '{% parameter fiscal_year_placeholder %}'
    WHEN pwfs.fully_funded_date BETWEEN '2013-07-01' AND '2014-06-30' THEN 'FY14'
    WHEN pwfs.fully_funded_date BETWEEN '2012-07-01' AND '2013-06-30' THEN 'FY13'
    WHEN pwfs.fully_funded_date BETWEEN '2011-07-01' AND '2012-06-30' THEN 'FY12'
    WHEN pwfs.fully_funded_date BETWEEN '2010-07-01' AND '2011-06-30' THEN 'FY11'
    WHEN pwfs.fully_funded_date BETWEEN '2009-07-01' AND '2010-06-30' THEN 'FY10'
    WHEN pwfs.fully_funded_date BETWEEN '2008-07-01' AND '2009-06-30' THEN 'FY09'
    WHEN pwfs.fully_funded_date BETWEEN '2007-07-01' AND '2008-06-30' THEN 'FY08'
    WHEN pwfs.fully_funded_date BETWEEN '2006-07-01' AND '2007-06-30' THEN 'FY07'
    WHEN pwfs.fully_funded_date BETWEEN '2005-07-01' AND '2006-06-30' THEN 'FY06'
    WHEN pwfs.fully_funded_date BETWEEN '2004-07-01' AND '2005-06-30' THEN 'FY05'
    WHEN pwfs.fully_funded_date BETWEEN '2003-07-01' AND '2004-06-30' THEN 'FY04'
    WHEN pwfs.fully_funded_date BETWEEN '2002-07-01' AND '2003-06-30' THEN 'FY03'
    WHEN pwfs.fully_funded_date BETWEEN '2001-07-01' AND '2002-06-30' THEN 'FY02'
    WHEN pwfs.fully_funded_date BETWEEN '2000-07-01' AND '2001-06-30' THEN 'FY01'
    WHEN pwfs.fully_funded_date BETWEEN '1999-07-01' AND '2000-06-30' THEN 'FY00' END AS fiscal_year_funded

FROM proposal_data p
LEFT JOIN
  (SELECT
    proposalid,
    MAX(datecreated) fully_funded_date
    FROM proposalworkflowstatus
    WHERE workflowstatusid = 10
    GROUP BY 1
  ) AS pwfs ON pwfs.proposalid = p.proposalid
WHERE pwfs.fully_funded_date IS NOT NULL
GROUP BY 1,2,3 ;;
  }

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

  dimension: class_size {
    type: int
    hidden: yes
  }

  dimension: fiscal_year_funded {
    hidden: yes
  }

  measure: total_students_impacted_calculated {
    label: "Total Students Impacted (Calculated)"
    type: sum
    hidden: yes
    sql: ${class_size} ;;
  }
}

student_counter.view.lookml · 49 lines

source-preserved

- view: student_counter
  derived_table:
    sql: SELECT
  p.createduserid teacherid,
  CASE WHEN p.gradetypeid IN (1,2) THEN 27 WHEN p.gradetypeid IN (3,4) THEN 70 ELSE 44 END AS class_size,
  CASE
    WHEN pwfs.fully_funded_date BETWEEN '2014-07-01' AND '2015-06-30' THEN '{% parameter fiscal_year_placeholder %}'
    WHEN pwfs.fully_funded_date BETWEEN '2013-07-01' AND '2014-06-30' THEN 'FY14'
    WHEN pwfs.fully_funded_date BETWEEN '2012-07-01' AND '2013-06-30' THEN 'FY13'
    WHEN pwfs.fully_funded_date BETWEEN '2011-07-01' AND '2012-06-30' THEN 'FY12'
    WHEN pwfs.fully_funded_date BETWEEN '2010-07-01' AND '2011-06-30' THEN 'FY11'
    WHEN pwfs.fully_funded_date BETWEEN '2009-07-01' AND '2010-06-30' THEN 'FY10'
    WHEN pwfs.fully_funded_date BETWEEN '2008-07-01' AND '2009-06-30' THEN 'FY09'
    WHEN pwfs.fully_funded_date BETWEEN '2007-07-01' AND '2008-06-30' THEN 'FY08'
    WHEN pwfs.fully_funded_date BETWEEN '2006-07-01' AND '2007-06-30' THEN 'FY07'
    WHEN pwfs.fully_funded_date BETWEEN '2005-07-01' AND '2006-06-30' THEN 'FY06'
    WHEN pwfs.fully_funded_date BETWEEN '2004-07-01' AND '2005-06-30' THEN 'FY05'
    WHEN pwfs.fully_funded_date BETWEEN '2003-07-01' AND '2004-06-30' THEN 'FY04'
    WHEN pwfs.fully_funded_date BETWEEN '2002-07-01' AND '2003-06-30' THEN 'FY03'
    WHEN pwfs.fully_funded_date BETWEEN '2001-07-01' AND '2002-06-30' THEN 'FY02'
    WHEN pwfs.fully_funded_date BETWEEN '2000-07-01' AND '2001-06-30' THEN 'FY01'
    WHEN pwfs.fully_funded_date BETWEEN '1999-07-01' AND '2000-06-30' THEN 'FY00' END AS fiscal_year_funded

FROM proposal_data p
LEFT JOIN
  (SELECT
    proposalid,
    MAX(datecreated) fully_funded_date
    FROM proposalworkflowstatus
    WHERE workflowstatusid = 10
    GROUP BY 1
  ) AS pwfs ON pwfs.proposalid = p.proposalid
WHERE pwfs.fully_funded_date IS NOT NULL
GROUP BY 1,2,3
  fields:
  - dimension: teacherid
    type: int
    primary_key: true
    hidden: true
    label: Teacher ID
  - dimension: class_size
    type: int
    hidden: true
  - dimension: fiscal_year_funded
    hidden: true
  - measure: total_students_impacted_calculated
    type: sum
    label: Total Students Impacted (Calculated)
    sql: ${class_size}

student_counter.sql
student_counter.sql
student_counter.view.lkml
student_counter.view.lookml

Student Counter

Blocks

Assumptions

Contents