SQL Saas recruitment

Feature Usage

Feature Usage model for saas recruitment.

source-preserved

Blocks

dbt SQL LKML LookML

feature_usage.sql · 20 lines

inferred

{{ config(materialized='view') }}

SELECT
jobactivity.start_time::date as start_date,
jobactivity.end_time::date as end_date,
account.accountname_text AS company_name,
bool_or(applicant.commented_flag) AS used_comments,
bool_or(applicant.rating_number is not null) AS used_ratings,
bool_or(applicant.stage_text <> 'new') AS used_stages
FROM applicantfact AS applicant
JOIN employer ON applicant.employer_id = employer.employer_id
  and companyname_text not ilike '<% parameter company_name_2 %>'
  and companyname_text not ilike '<% parameter company_name_1 %>'
JOIN job_reporting AS job ON applicant.job_id = job.job_id
JOIN jobactivity ON applicant.jobactivity_id = jobactivity.jobactivity_id
LEFT JOIN contact_reporting AS contact ON employer.external_contact_id = contact.external_contact_id
LEFT JOIN account_reporting AS account ON contact.external_account_id = {{ this }}.external_account_id
WHERE applicant.fittype_id = 1 and job.jobtype_text <> 'SampleJob' and not employer.istest_flag
GROUP BY 1,2,3
ORDER BY 1 DESC

Assumptions

materialized='view' is the default; no persist hint found.

feature_usage.sql · 18 lines

source-preserved validation: not checked

SELECT
jobactivity.start_time::date as start_date,
jobactivity.end_time::date as end_date,
account.accountname_text AS company_name,
bool_or(applicant.commented_flag) AS used_comments,
bool_or(applicant.rating_number is not null) AS used_ratings,
bool_or(applicant.stage_text <> 'new') AS used_stages
FROM applicantfact AS applicant
JOIN employer ON applicant.employer_id = employer.employer_id
  and companyname_text not ilike '<% parameter company_name_2 %>'
  and companyname_text not ilike '<% parameter company_name_1 %>'
JOIN job_reporting AS job ON applicant.job_id = job.job_id
JOIN jobactivity ON applicant.jobactivity_id = jobactivity.jobactivity_id
LEFT JOIN contact_reporting AS contact ON employer.external_contact_id = contact.external_contact_id
LEFT JOIN account_reporting AS account ON contact.external_account_id = ${TABLE}.external_account_id
WHERE applicant.fittype_id = 1 and job.jobtype_text <> 'SampleJob' and not employer.istest_flag
GROUP BY 1,2,3
ORDER BY 1 DESC

feature_usage.view.lkml · 59 lines

inferred

view: feature_usage {
  derived_table: {
    sql: SELECT
jobactivity.start_time::date as start_date,
jobactivity.end_time::date as end_date,
account.accountname_text AS company_name,
bool_or(applicant.commented_flag) AS used_comments,
bool_or(applicant.rating_number is not null) AS used_ratings,
bool_or(applicant.stage_text <> 'new') AS used_stages
FROM applicantfact AS applicant
JOIN employer ON applicant.employer_id = employer.employer_id
  and companyname_text not ilike '<% parameter company_name_2 %>'
  and companyname_text not ilike '<% parameter company_name_1 %>'
JOIN job_reporting AS job ON applicant.job_id = job.job_id
JOIN jobactivity ON applicant.jobactivity_id = jobactivity.jobactivity_id
LEFT JOIN contact_reporting AS contact ON employer.external_contact_id = contact.external_contact_id
LEFT JOIN account_reporting AS account ON contact.external_account_id = ${TABLE}.external_account_id
WHERE applicant.fittype_id = 1 and job.jobtype_text <> 'SampleJob' and not employer.istest_flag
GROUP BY 1,2,3
ORDER BY 1 DESC ;;
  }

  dimension_group: end {
    type: time
    timeframes: [date, week, month]
    datatype: date
    sql: ${TABLE}.end_date ;;
  }

  dimension_group: start {
    type: time
    timeframes: [date, week, month]
    datatype: date
    sql: ${TABLE}.start_date ;;
  }

  measure: users {
    type: count_distinct
    sql: ${TABLE}.company_name ;;
  }

  measure: used_comments_rate {
    type: number
    value_format: "#.00%"
    sql: ${used_comments}::float / nullif(${users}::float,0) ;;
  }

  measure: used_stages_rate {
    type: number
    value_format: "#.00%"
    sql: ${used_stages}::float / nullif(${users}::float,0) ;;
  }

  measure: used_ratings_rate {
    type: number
    value_format: "#.00%"
    sql: ${used_ratings}::float / nullif(${users}::float,0) ;;
  }
}

feature_usage.view.lookml · 52 lines

source-preserved

- view: feature_usage
  derived_table:
    sql: SELECT
jobactivity.start_time::date as start_date,
jobactivity.end_time::date as end_date,
account.accountname_text AS company_name,
bool_or(applicant.commented_flag) AS used_comments,
bool_or(applicant.rating_number is not null) AS used_ratings,
bool_or(applicant.stage_text <> 'new') AS used_stages
FROM applicantfact AS applicant
JOIN employer ON applicant.employer_id = employer.employer_id
  and companyname_text not ilike '<% parameter company_name_2 %>'
  and companyname_text not ilike '<% parameter company_name_1 %>'
JOIN job_reporting AS job ON applicant.job_id = job.job_id
JOIN jobactivity ON applicant.jobactivity_id = jobactivity.jobactivity_id
LEFT JOIN contact_reporting AS contact ON employer.external_contact_id = contact.external_contact_id
LEFT JOIN account_reporting AS account ON contact.external_account_id = ${TABLE}.external_account_id
WHERE applicant.fittype_id = 1 and job.jobtype_text <> 'SampleJob' and not employer.istest_flag
GROUP BY 1,2,3
ORDER BY 1 DESC
  fields:
  - dimension_group: end
    type: time
    timeframes:
    - date
    - week
    - month
    datatype: date
    sql: ${TABLE}.end_date
  - dimension_group: start
    type: time
    timeframes:
    - date
    - week
    - month
    datatype: date
    sql: ${TABLE}.start_date
  - measure: users
    type: count_distinct
    sql: ${TABLE}.company_name
  - measure: used_comments_rate
    type: number
    value_format: '#.00%'
    sql: ${used_comments}::float / nullif(${users}::float,0)
  - measure: used_stages_rate
    type: number
    value_format: '#.00%'
    sql: ${used_stages}::float / nullif(${users}::float,0)
  - measure: used_ratings_rate
    type: number
    value_format: '#.00%'
    sql: ${used_ratings}::float / nullif(${users}::float,0)

feature_usage.sql
feature_usage.sql
feature_usage.view.lkml
feature_usage.view.lookml

Feature Usage

Blocks

Assumptions

Contents