Hiring

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

select dd.weekstart_date
  , coalesce(a.owner_id,'UNKNOWN OWNER') as "owner_id"
  , count(distinct e.account_id) employers_hiring
  , count(distinct ja.job_id) jobs_hiring
from date_dimension dd
join jobactivity ja on dd.date_date between ja.start_time::Date and ja.end_time
  and ja.end_time - ja.start_time >= interval '6 day'
  and sourcing_flag
  and approved_time is not null
join product p on ja.product_id = p.product_id
  and p.level_number <>99
join employer e on e.employer_id = ja.employer_id
  and not istest_flag
left join account_reporting a on left(a.account_id,15) = left(e.account_id,15)
where dd.date_date between '<% parameter start_date %>' and current_date
group by 1,2

# models/schema.yml
version: 2

models:
  - name: hiring
    columns:
      - name: period
      - name: owner_id

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.

select dd.weekstart_date
  , coalesce(a.owner_id,'UNKNOWN OWNER') as "owner_id"
  , count(distinct e.account_id) employers_hiring
  , count(distinct ja.job_id) jobs_hiring
from date_dimension dd
join jobactivity ja on dd.date_date between ja.start_time::Date and ja.end_time
  and ja.end_time - ja.start_time >= interval '6 day'
  and sourcing_flag
  and approved_time is not null
join product p on ja.product_id = p.product_id
  and p.level_number <>99
join employer e on e.employer_id = ja.employer_id
  and not istest_flag
left join account_reporting a on left(a.account_id,15) = left(e.account_id,15)
where dd.date_date between '<% parameter start_date %>' and current_date
group by 1,2

view: hiring {
  derived_table: {
    sql: select dd.weekstart_date
  , coalesce(a.owner_id,'UNKNOWN OWNER') as "owner_id"
  , count(distinct e.account_id) employers_hiring
  , count(distinct ja.job_id) jobs_hiring
from date_dimension dd
join jobactivity ja on dd.date_date between ja.start_time::Date and ja.end_time
  and ja.end_time - ja.start_time >= interval '6 day'
  and sourcing_flag
  and approved_time is not null
join product p on ja.product_id = p.product_id
  and p.level_number <>99
join employer e on e.employer_id = ja.employer_id
  and not istest_flag
left join account_reporting a on left(a.account_id,15) = left(e.account_id,15)
where dd.date_date between '<% parameter start_date %>' and current_date
group by 1,2 ;;
  }

  dimension: period {
    type: time
    sql: ${TABLE}.weekstart_date ;;
  }

  dimension: owner_id {
    hidden: yes
    sql: ${TABLE}.owner_id ;;
  }

  measure: employers_hiring {
    type: sum
    sql: ${TABLE}.employers_hiring ;;
  }

  measure: jobs_hiring {
    type: sum
    sql: ${TABLE}.jobs_hiring ;;
  }

  measure: open_jobs_per_employer {
    type: number
    sql: ${jobs_hiring} / ${employers_hiring} ;;
  }
}

- view: hiring
  derived_table:
    sql: select dd.weekstart_date
  , coalesce(a.owner_id,'UNKNOWN OWNER') as "owner_id"
  , count(distinct e.account_id) employers_hiring
  , count(distinct ja.job_id) jobs_hiring
from date_dimension dd
join jobactivity ja on dd.date_date between ja.start_time::Date and ja.end_time
  and ja.end_time - ja.start_time >= interval '6 day'
  and sourcing_flag
  and approved_time is not null
join product p on ja.product_id = p.product_id
  and p.level_number <>99
join employer e on e.employer_id = ja.employer_id
  and not istest_flag
left join account_reporting a on left(a.account_id,15) = left(e.account_id,15)
where dd.date_date between '<% parameter start_date %>' and current_date
group by 1,2
  fields:
  - dimension: period
    type: time
    sql: ${TABLE}.weekstart_date
  - dimension: owner_id
    hidden: true
    sql: ${TABLE}.owner_id
  - measure: employers_hiring
    type: sum
    sql: ${TABLE}.employers_hiring
  - measure: jobs_hiring
    type: sum
    sql: ${TABLE}.jobs_hiring
  - measure: open_jobs_per_employer
    type: number
    sql: ${jobs_hiring} / ${employers_hiring}