SQL Crowdfunding education

Donor Ideal

Donor Ideal model for crowdfunding education.

source-preserved

Blocks

dbt SQL LKML LookML

donor_ideal.sql · 43 lines

inferred

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

SELECT
  pay.donorid AS donorid
  , COUNT(DISTINCT pay.paymentid) AS twelve_month_payments
  , SUM(pay.green_dollars) AS twelve_month_green_dollars_amount
  , COUNT(DISTINCT d.proposalid) AS twelve_month_projects
  , COUNT(DISTINCT t.personid) AS twelve_month_teachers
FROM {{ ref('payment') }} pay
LEFT JOIN donation_data d ON d.donationid = pay.donationid
LEFT JOIN proposal_data p ON p.proposalid = d.proposalid
LEFT JOIN appuser AS t ON p.createduserid = t.personid
LEFT JOIN person AS donor_person ON pay.donorid = donor_person.personid
LEFT JOIN address AS donor_address ON donor_person.addressid = donor_address.addressid
JOIN (
  SELECT DISTINCT (donorid)
  FROM donormmi s
  WHERE s.searchcriteria IS NOT NULL
  AND createdate BETWEEN current_date - interval '1 year' AND current_date
  ) AS search ON search.donorid = pay.donorid

WHERE pay.received_date BETWEEN current_date - interval '1 year' AND current_date
 AND NOT COALESCE(pay.is_teacher_referred, FALSE)
AND pay.received = {% parameter y_value_placeholder %}
AND donor_address.address1 <> ''
GROUP BY pay.donorid

# models/schema.yml
version: 2

models:
  - name: donor_ideal
    columns:
      - name: donorid
        tests:
          - unique
          - not_null
      - name: twelve_month_payments
      - name: twelve_month_green_dollars_amount
      - name: twelve_month_projects
      - name: twelve_month_teachers
      - name: donor.is_ideal

Assumptions

Each ${view.SQL_TABLE_NAME} was mapped to {{ ref('view') }}; confirm a dbt model exists for each referenced view.
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.

donor_ideal.sql · 24 lines

source-preserved validation: not checked

SELECT
  pay.donorid AS donorid
  , COUNT(DISTINCT pay.paymentid) AS twelve_month_payments
  , SUM(pay.green_dollars) AS twelve_month_green_dollars_amount
  , COUNT(DISTINCT d.proposalid) AS twelve_month_projects
  , COUNT(DISTINCT t.personid) AS twelve_month_teachers
FROM ${payment.SQL_TABLE_NAME} pay
LEFT JOIN donation_data d ON d.donationid = pay.donationid
LEFT JOIN proposal_data p ON p.proposalid = d.proposalid
LEFT JOIN appuser AS t ON p.createduserid = t.personid
LEFT JOIN person AS donor_person ON pay.donorid = donor_person.personid
LEFT JOIN address AS donor_address ON donor_person.addressid = donor_address.addressid
JOIN (
  SELECT DISTINCT (donorid)
  FROM donormmi s
  WHERE s.searchcriteria IS NOT NULL
  AND createdate BETWEEN current_date - interval '1 year' AND current_date
  ) AS search ON search.donorid = pay.donorid

WHERE pay.received_date BETWEEN current_date - interval '1 year' AND current_date
 AND NOT COALESCE(pay.is_teacher_referred, FALSE)
AND pay.received = {% parameter y_value_placeholder %}
AND donor_address.address1 <> ''
GROUP BY pay.donorid

donor_ideal.view.lkml · 66 lines

inferred

view: donor_ideal {
  derived_table: {
    sql: SELECT
  pay.donorid AS donorid
  , COUNT(DISTINCT pay.paymentid) AS twelve_month_payments
  , SUM(pay.green_dollars) AS twelve_month_green_dollars_amount
  , COUNT(DISTINCT d.proposalid) AS twelve_month_projects
  , COUNT(DISTINCT t.personid) AS twelve_month_teachers
FROM ${payment.SQL_TABLE_NAME} pay
LEFT JOIN donation_data d ON d.donationid = pay.donationid
LEFT JOIN proposal_data p ON p.proposalid = d.proposalid
LEFT JOIN appuser AS t ON p.createduserid = t.personid
LEFT JOIN person AS donor_person ON pay.donorid = donor_person.personid
LEFT JOIN address AS donor_address ON donor_person.addressid = donor_address.addressid
JOIN (
  SELECT DISTINCT (donorid)
  FROM donormmi s
  WHERE s.searchcriteria IS NOT NULL
  AND createdate BETWEEN current_date - interval '1 year' AND current_date
  ) AS search ON search.donorid = pay.donorid

WHERE pay.received_date BETWEEN current_date - interval '1 year' AND current_date
 AND NOT COALESCE(pay.is_teacher_referred, FALSE)
AND pay.received = {% parameter y_value_placeholder %}
AND donor_address.address1 <> ''
GROUP BY pay.donorid ;;
  }

  dimension: donorid {
    label: "Donor ID"
    type: int
    primary_key: yes
    hidden: yes
  }

  dimension: twelve_month_payments {
    label: "Twelve Month Payments"
    type: int
    hidden: yes
  }

  dimension: twelve_month_green_dollars_amount {
    label: "12 Month Green Total Amount"
    type: number
    hidden: yes
  }

  dimension: twelve_month_projects {
    label: "12 Month Projects"
    type: int
    hidden: yes
  }

  dimension: twelve_month_teachers {
    label: "12 Month Teachers"
    type: int
    hidden: yes
  }

  dimension: donor.is_ideal {
    type: yesno
    sql: ${twelve_month_green_dollars_amount} >= 100
AND ${twelve_month_teachers} >= 2
AND ${twelve_month_payments} >= 3 ;;
  }
}

donor_ideal.view.lookml · 53 lines

source-preserved

- view: donor_ideal
  derived_table:
    sql: SELECT
  pay.donorid AS donorid
  , COUNT(DISTINCT pay.paymentid) AS twelve_month_payments
  , SUM(pay.green_dollars) AS twelve_month_green_dollars_amount
  , COUNT(DISTINCT d.proposalid) AS twelve_month_projects
  , COUNT(DISTINCT t.personid) AS twelve_month_teachers
FROM ${payment.SQL_TABLE_NAME} pay
LEFT JOIN donation_data d ON d.donationid = pay.donationid
LEFT JOIN proposal_data p ON p.proposalid = d.proposalid
LEFT JOIN appuser AS t ON p.createduserid = t.personid
LEFT JOIN person AS donor_person ON pay.donorid = donor_person.personid
LEFT JOIN address AS donor_address ON donor_person.addressid = donor_address.addressid
JOIN (
  SELECT DISTINCT (donorid)
  FROM donormmi s
  WHERE s.searchcriteria IS NOT NULL
  AND createdate BETWEEN current_date - interval '1 year' AND current_date
  ) AS search ON search.donorid = pay.donorid

WHERE pay.received_date BETWEEN current_date - interval '1 year' AND current_date
 AND NOT COALESCE(pay.is_teacher_referred, FALSE)
AND pay.received = {% parameter y_value_placeholder %}
AND donor_address.address1 <> ''
GROUP BY pay.donorid
  fields:
  - dimension: donorid
    type: int
    primary_key: true
    hidden: true
    label: Donor ID
  - dimension: twelve_month_payments
    type: int
    hidden: true
    label: Twelve Month Payments
  - dimension: twelve_month_green_dollars_amount
    type: number
    hidden: true
    label: 12 Month Green Total Amount
  - dimension: twelve_month_projects
    type: int
    hidden: true
    label: 12 Month Projects
  - dimension: twelve_month_teachers
    type: int
    hidden: true
    label: 12 Month Teachers
  - dimension: donor.is_ideal
    type: yesno
    sql: ${twelve_month_green_dollars_amount} >= 100
AND ${twelve_month_teachers} >= 2
AND ${twelve_month_payments} >= 3

donor_ideal.sql
donor_ideal.sql
donor_ideal.view.lkml
donor_ideal.view.lookml

Donor Ideal

Blocks

Assumptions

Contents