schema.yml

version: 2

models:
  - name: 202242_solution
    description: >
      Solution SQL definition for [Preppin' Data 2022 Week 42](https://preppindata.blogspot.com/2022/10/2022-week-42-missing-training-data.html).
      Created as view and exported as [202242_solution_output.csv](https://github.com/jharris126/preppin-data-dbt/blob/master/solution_outputs/202242_solution_output.csv) in [/preppin-data-dbt/solution_outputs](https://github.com/jharris126/preppin-data-dbt/tree/master/solution_outputs) folder.
      All solutions sql files automatically generate a `.csv` output file when run using the [model_to_csv macro](#!/macro/macro.preppin_data.model_to_csv).
    columns:
      - name: player
        type: VARCHAR
      - name: session
        type: VARCHAR
      - name: session_date
        type: DATE
      - name: score
        type: BIGINT
        tests:
          - not_null
      - name: flag
        type: VARCHAR
        tests:
          - accepted_values:
              values: [Actual, Carried Over]

  - name: 202239_solution
    description: >
      Solution SQL definition for [Preppin' Data 2022 Week 39](https://preppindata.blogspot.com/2022/09/2022-week-39-filling-in-for-hr.html).
      Created as view and exported as [202239_solution_output.csv](https://github.com/jharris126/preppin-data-dbt/blob/master/solution_outputs/202239_solution_output.csv) in [/preppin-data-dbt/solution_outputs](https://github.com/jharris126/preppin-data-dbt/tree/master/solution_outputs) folder.
      All solutions sql files automatically generate a `.csv` output file when run using the [model_to_csv macro](#!/macro/macro.preppin_data.model_to_csv).
    columns:
      - name: record_id
        type: BIGINT
        tests:
          - unique
          - not_null
      - name: employee
        type: VARCHAR
        tests:
          - not_null
      - name: work_level
        type: VARCHAR
        tests:
          - not_null
      - name: stage
        type: VARCHAR
      - name: date
        type: DATE

  - name: 202243_solution
    description: >
      Solution SQL definition for [Preppin' Data 2022 Week 43](https://preppindata.blogspot.com/2022/10/2022-week-43-missing-training-data-20.html).
      Created as view and exported as [202243_solution_output.csv](https://github.com/jharris126/preppin-data-dbt/blob/master/solution_outputs/202243_solution_output.csv) in [/preppin-data-dbt/solution_outputs](https://github.com/jharris126/preppin-data-dbt/tree/master/solution_outputs) folder.
      All solutions sql files automatically generate a `.csv` output file when run using the [model_to_csv macro](#!/macro/macro.preppin_data.model_to_csv).
    columns:
      - name: player
        type: VARCHAR
      - name: session
        type: VARCHAR
      - name: session_date
        type: DATE
      - name: score
        type: BIGINT
        tests:
          - not_null
      - name: flag
        type: VARCHAR
        tests:
          - accepted_values:
              values: [Actual, Carried Over, Pre First Session]

  - name: 202201_solution
    description: >
      Solution SQL definition for [Preppin' Data 2022 Week 1](https://preppindata.blogspot.com/2022/01/2022-week-1-prep-school-parental.html).
      Created as view and exported as [202201_solution_output.csv](https://github.com/jharris126/preppin-data-dbt/blob/master/solution_outputs/202201_solution_output.csv) in [/preppin-data-dbt/solution_outputs](https://github.com/jharris126/preppin-data-dbt/tree/master/solution_outputs) folder.
      All solutions sql files automatically generate a `.csv` output file when run using the [model_to_csv macro](#!/macro/macro.preppin_data.model_to_csv).
    columns:
      - name: academic_year
        type: INTEGER
      - name: pupils_name
        type: VARCHAR
      - name: parental_contact_full_name
        type: VARCHAR
      - name: parental_contact_email_address
        type: VARCHAR

  - name: 202244_solution
    description: >
      Solution SQL definition for [Preppin' Data 2022 Week 44](https://preppindata.blogspot.com/2022/11/2022-week-44-creating-order-ids.html).
      Created as view and exported as [202244_solution_output.csv](https://github.com/jharris126/preppin-data-dbt/blob/master/solution_outputs/202244_solution_output.csv) in [/preppin-data-dbt/solution_outputs](https://github.com/jharris126/preppin-data-dbt/tree/master/solution_outputs) folder.
      All solutions sql files automatically generate a `.csv` output file when run using the [model_to_csv macro](#!/macro/macro.preppin_data.model_to_csv).
    columns:
      - name: order_id
        type: VARCHAR
        tests:
          - not_null
          - unique
      - name: order_number
        type: BIGINT
        tests:
          - not_null
      - name: customer
        type: VARCHAR
        tests:
          - not_null
      - name: order_date
        type: DATE
        tests:
          - not_null

  - name: 202245_solution
    description: >
      Solution SQL definition for [Preppin' Data 2022 Week 45](https://preppindata.blogspot.com/2022/11/2022-week-45-strange-table-structure.html).
      Created as view and exported as [202245_solution_output.csv](https://github.com/jharris126/preppin-data-dbt/blob/master/solution_outputs/202245_solution_output.csv) in [/preppin-data-dbt/solution_outputs](https://github.com/jharris126/preppin-data-dbt/tree/master/solution_outputs) folder.
      All solutions sql files automatically generate a `.csv` output file when run using the [model_to_csv macro](#!/macro/macro.preppin_data.model_to_csv).
    columns:
      - name: store
        type: VARCHAR
        tests:
          - not_null
          - accepted_values:
              values: [Dulwich, Wimbledon, Notting Hill, Lewisham]
      - name: date
        type: DATE
        tests:
          - not_null
      - name: sales
        type: BIGINT
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: '> 0'
      - name: profit
        type: BIGINT
        tests:
          - not_null

  - name: 202247_solution
    description: >
      Solution SQL definition for [Preppin' Data 2022 Week 47](https://preppindata.blogspot.com/2022/11/2022-week-47-chelsea-managers-per-prime.html).
      Created as view and exported as [202247_solution_output.csv](https://github.com/jharris126/preppin-data-dbt/blob/master/solution_outputs/202247_solution_output.csv) in [/preppin-data-dbt/solution_outputs](https://github.com/jharris126/preppin-data-dbt/tree/master/solution_outputs) folder.
      All solutions sql files automatically generate a `.csv` output file when run using the [model_to_csv macro](#!/macro/macro.preppin_data.model_to_csv).
    columns:
      - name: prime_minister
        type: VARCHAR
        tests:
          - not_null
      - name: term_begin
        type: DATE
        tests:
          - not_null
      - name: term_end
        type: DATE
      - name: managers
        type: HUGEINT
      - name: matches
        type: HUGEINT
      - name: matches_won
        type: HUGEINT
      - name: matches_drawn
        type: HUGEINT
      - name: matches_lost
        type: HUGEINT
      - name: win_percent
        type: DECIMAL(8,2)

  - name: 202248_solution_1
    description: >
      Solution SQL definition #1 for [Preppin' Data 2022 Week 48](https://preppindata.blogspot.com/2022/11/2022-week-48-tiddlywinks-tidy.html).
      Created as view and exported as [202248_solution_1_output.csv](https://github.com/jharris126/preppin-data-dbt/blob/master/solution_outputs/202248_solution_1_output.csv) in [/preppin-data-dbt/solution_outputs](https://github.com/jharris126/preppin-data-dbt/tree/master/solution_outputs) folder.
      All solutions sql files automatically generate a `.csv` output file when run using the [model_to_csv macro](#!/macro/macro.preppin_data.model_to_csv).
    columns:
      - name: event_id
        type: INT
        tests:
          - not_null
      - name: competitor
        type: VARCHAR
        tests:
          - not_null
      - name: game_order
        type: VARCHAR
      - name: score
        type: NUMERIC(8,6)
      - name: potout
        type: BOOLEAN
      - name: note
        type: VARCHAR

  - name: 202248_solution_2
    description: >
      Solution SQL definition #2 for [Preppin' Data 2022 Week 48](https://preppindata.blogspot.com/2022/11/2022-week-48-tiddlywinks-tidy.html).
      Created as view and exported as [202248_solution_2_output.csv](https://github.com/jharris126/preppin-data-dbt/blob/master/solution_outputs/202248_solution_2_output.csv) in [/preppin-data-dbt/solution_outputs](https://github.com/jharris126/preppin-data-dbt/tree/master/solution_outputs) folder.
      All solutions sql files automatically generate a `.csv` output file when run using the [model_to_csv macro](#!/macro/macro.preppin_data.model_to_csv).
    columns:
      - name: event_id
        type: INT
        tests:
          - not_null
      - name: competitor
        type: VARCHAR
        tests:
          - not_null
      - name: event
        type: VARCHAR
      - name: event_start_date
        type: DATE
      - name: event_description
        type: VARCHAR
      - name: association
        type: VARCHAR
      - name: points
        type: NUMERIC(8,6)
      - name: wins
        type: INT
      - name: losses
        type: INT
      - name: ties
        type: INT

  - name: 202249_solution_1
    description: >
      Solution SQL definition #1 for [Preppin' Data 2022 Week 49](https://preppindata.blogspot.com/2022/12/2022-week-49-cleaning-status-history-hr.html).
      Created as view and exported as [202249_solution_1_output.csv](https://github.com/jharris126/preppin-data-dbt/blob/master/solution_outputs/202249_solution_1_output.csv) in [/preppin-data-dbt/solution_outputs](https://github.com/jharris126/preppin-data-dbt/tree/master/solution_outputs) folder.
      All solutions sql files automatically generate a `.csv` output file when run using the [model_to_csv macro](#!/macro/macro.preppin_data.model_to_csv).
    columns:
      - name: candidate_id
        type: INT
        tests:
          - not_null
      - name: position_id
        type: INT
        tests:
          - not_null
      - name: status
        type: VARCHAR
        tests:
          - not_null
      - name: ts
        type: DATETIME
      - name: filename
        type: VARCHAR

  - name: 202249_solution_2
    description: >
      Solution SQL definition #2 for [Preppin' Data 2022 Week 49](https://preppindata.blogspot.com/2022/12/2022-week-49-cleaning-status-history-hr.html).
      Created as view and exported as [202249_solution_2_output.csv](https://github.com/jharris126/preppin-data-dbt/blob/master/solution_outputs/202249_solution_2_output.csv) in [/preppin-data-dbt/solution_outputs](https://github.com/jharris126/preppin-data-dbt/tree/master/solution_outputs) folder.
      All solutions sql files automatically generate a `.csv` output file when run using the [model_to_csv macro](#!/macro/macro.preppin_data.model_to_csv).
    columns:
      - name: candidate_id
        type: INT
        tests:
          - not_null
      - name: position_id
        type: INT
        tests:
          - not_null
      - name: current_status
        type: VARCHAR
        tests:
          - not_null

  - name: 202250_solution
    description: >
      Solution SQL definition for [Preppin' Data 2022 Week 50](https://preppindata.blogspot.com/2022/12/2022-week-50-withdrawals-hr-month.html).
      Created as view and exported as [202250_solution_output.csv](https://github.com/jharris126/preppin-data-dbt/blob/master/solution_outputs/202250_solution_output.csv) in [/preppin-data-dbt/solution_outputs](https://github.com/jharris126/preppin-data-dbt/tree/master/solution_outputs) folder.
      All solutions sql files automatically generate a `.csv` output file when run using the [model_to_csv macro](#!/macro/macro.preppin_data.model_to_csv).
    columns:
      - name: status_before_withdrawal
        type: VARCHAR
        tests:
          - not_null
          - unique
      - name: withdrawals
        type: INT
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: '>= 0'
      - name: total_in_status
        type: INT
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: '> 0'
      - name: pct_withdrawn
        type: NUMERIC(8,1)
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: '>= 0'
          - dbt_utils.expression_is_true:
              expression: '<= 100'

  - name: 202251_solution
    description: >
      Solution SQL definition for [Preppin' Data 2022 Week 51](https://preppindata.blogspot.com/2022/12/2022-week-51-parsing-unfortunate-report.html).
      Created as view and exported as [202251_solution_output.csv](https://github.com/jharris126/preppin-data-dbt/blob/master/solution_outputs/202251_solution_output.csv) in [/preppin-data-dbt/solution_outputs](https://github.com/jharris126/preppin-data-dbt/tree/master/solution_outputs) folder.
      All solutions sql files automatically generate a `.csv` output file when run using the [model_to_csv macro](#!/macro/macro.preppin_data.model_to_csv).
    columns:
      - name: application_month
        type: DATE
        tests:
          - not_null
          - unique
      - name: total_candidates
        type: INT
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: '>= 0'
      - name: candidates_with_preferred_qualifications
        type: INT
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: '>= 0'
      - name: percent_preferred_of_total_candidates
        type: NUMERIC(8,1)
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: '>= 0'
          - dbt_utils.expression_is_true:
              expression: '<= 100'

  - name: 202252_solution
    description: >
      Solution SQL definition for [Preppin' Data 2022 Week 52](https://preppindata.blogspot.com/2022/12/2022-week-52-find-sub-departments-from.html).
      Created as view and exported as [202252_solution_output.csv](https://github.com/jharris126/preppin-data-dbt/blob/master/solution_outputs/202252_solution_output.csv) in [/preppin-data-dbt/solution_outputs](https://github.com/jharris126/preppin-data-dbt/tree/master/solution_outputs) folder.
      All solutions sql files automatically generate a `.csv` output file when run using the [model_to_csv macro](#!/macro/macro.preppin_data.model_to_csv).
    columns:
      - name: position_id
        type: VARCHAR
        tests:
          - not_null
          - unique
      - name: employee_id
        type: VARCHAR
        tests:
          - not_null
          - unique
      - name: title
        type: VARCHAR
        tests:
          - not_null
      - name: department
        type: VARCHAR
        tests:
          - not_null
      - name: supervisor_id
        type: VARCHAR
      - name: team_id
        type: VARCHAR
      - name: direct_reports
        type: INT
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: '>= 0'
      - name: team_name
        type: VARCHAR
      - name: team_hierarchy
        type: VARCHAR
      - name: employee_id_hierarchy
        type: VARCHAR
      - name: dependent_team_ids
        type: VARCHAR
      - name: hierarchy_level
        type: INT
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: '>= 1'
      - name: subdept_team_id
        type: VARCHAR
      - name: subdept_name
        type: VARCHAR

  - name: 202301_solution_1
    description: >
      Solution SQL definition #1 for [Preppin' Data 2023 Week 1](https://preppindata.blogspot.com/2023/01/2023-week-1-data-source-bank.html).
      Created as view and exported as [202301_solution_1_output.csv](https://github.com/jharris126/preppin-data-dbt/blob/master/solution_outputs/202301_solution_1_output.csv) in [/preppin-data-dbt/solution_outputs](https://github.com/jharris126/preppin-data-dbt/tree/master/solution_outputs) folder.
      All solutions sql files automatically generate a `.csv` output file when run using the [model_to_csv macro](#!/macro/macro.preppin_data.model_to_csv).
    columns:
      - name: bank
        type: VARCHAR
        tests:
          - not_null
          - unique
      - name: value
        type: BIGINT
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: '> 1'
  
  - name: 202301_solution_2
    description: >
      Solution SQL definition #2 for [Preppin' Data 2023 Week 1](https://preppindata.blogspot.com/2023/01/2023-week-1-data-source-bank.html).
      Created as view and exported as [202301_solution_2_output.csv](https://github.com/jharris126/preppin-data-dbt/blob/master/solution_outputs/202301_solution_2_output.csv) in [/preppin-data-dbt/solution_outputs](https://github.com/jharris126/preppin-data-dbt/tree/master/solution_outputs) folder.
      All solutions sql files automatically generate a `.csv` output file when run using the [model_to_csv macro](#!/macro/macro.preppin_data.model_to_csv).
    columns:
      - name: bank
        type: VARCHAR
        tests:
          - not_null
      - name: online_or_in_person
        type: VARCHAR
        tests:
          - not_null
      - name: transaction_date
        type: VARCHAR
        tests:
          - not_null
      - name: value
        type: BIGINT
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: '> 1'

  - name: 202301_solution_3
    description: >
      Solution SQL definition #3 for [Preppin' Data 2023 Week 1](https://preppindata.blogspot.com/2023/01/2023-week-1-data-source-bank.html).
      Created as view and exported as [202301_solution_3_output.csv](https://github.com/jharris126/preppin-data-dbt/blob/master/solution_outputs/202301_solution_3_output.csv) in [/preppin-data-dbt/solution_outputs](https://github.com/jharris126/preppin-data-dbt/tree/master/solution_outputs) folder.
      All solutions sql files automatically generate a `.csv` output file when run using the [model_to_csv macro](#!/macro/macro.preppin_data.model_to_csv).
    columns:
      - name: bank
        type: VARCHAR
        tests:
          - not_null
      - name: customer_code
        type: VARCHAR
        tests:
          - not_null
      - name: value
        type: BIGINT
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: '> 0'

  - name: 202302_solution
    description: >
      Solution SQL definition for [Preppin' Data 2023 Week 2](https://preppindata.blogspot.com/2023/01/2023-week-2-international-bank-account.html).
      Created as view and exported as [202302_solution_output.csv](https://github.com/jharris126/preppin-data-dbt/blob/master/solution_outputs/202302_solution_output.csv) in [/preppin-data-dbt/solution_outputs](https://github.com/jharris126/preppin-data-dbt/tree/master/solution_outputs) folder.
      All solutions sql files automatically generate a `.csv` output file when run using the [model_to_csv macro](#!/macro/macro.preppin_data.model_to_csv).
    columns:
      - name: transaction_id
        type: BIGINT
        tests:
          - unique
          - not_null
      - name: iban
        type: VARCHAR
        tests:
          - not_null

  - name: 202303_solution
    description: >
      Solution SQL definition for [Preppin' Data 2023 Week 3](https://preppindata.blogspot.com/2023/01/2023-week-3-targets-for-dsb.html).
      Created as view and exported as [202303_solution_output.csv](https://github.com/jharris126/preppin-data-dbt/blob/master/solution_outputs/202303_solution_output.csv) in [/preppin-data-dbt/solution_outputs](https://github.com/jharris126/preppin-data-dbt/tree/master/solution_outputs) folder.
      All solutions sql files automatically generate a `.csv` output file when run using the [model_to_csv macro](#!/macro/macro.preppin_data.model_to_csv).
    columns:
      - name: online_or_in_person
        type: VARCHAR
        tests:
          - not_null
      - name: quarter
        type: INT
        tests:
          - not_null
      - name: value
        type: BIGINT
      - name: quarterly_targets
        type: BIGINT
      - name: variance_to_target
        type: BIGINT

  - name: 202304_solution
    description: >
      Solution SQL definition for [Preppin' Data 2023 Week 4](https://preppindata.blogspot.com/2023/01/2023-week-4-new-customers.html).
      Created as view and exported as [202304_solution_output.csv](https://github.com/jharris126/preppin-data-dbt/blob/master/solution_outputs/202304_solution_output.csv) in [/preppin-data-dbt/solution_outputs](https://github.com/jharris126/preppin-data-dbt/tree/master/solution_outputs) folder.
      All solutions sql files automatically generate a `.csv` output file when run using the [model_to_csv macro](#!/macro/macro.preppin_data.model_to_csv).
    columns:
      - name: id
        type: BIGINT
        tests:
          - not_null
      - name: joining_date
        type: DATE
        tests:
          - not_null
      - name: account_type
        type: VARCHAR
        tests:
          - not_null
          - accepted_values:
              values: [Basic, Gold, Platinum]
      - name: date_of_birth
        type: DATE
        tests:
          - not_null
      - name: ethnicity
        type: VARCHAR
        tests:
          - not_null
          - accepted_values:
              values: [Asian, Black, White, Other]

  - name: 202305_solution
    description: >
      Solution SQL definition for [Preppin' Data 2023 Week 5](https://preppindata.blogspot.com/2023/02/2023-week-5-dsb-ranking.html).
      Created as view and exported as [202305_solution_output.csv](https://github.com/jharris126/preppin-data-dbt/blob/master/solution_outputs/202305_solution_output.csv) in [/preppin-data-dbt/solution_outputs](https://github.com/jharris126/preppin-data-dbt/tree/master/solution_outputs) folder.
      All solutions sql files automatically generate a `.csv` output file when run using the [model_to_csv macro](#!/macro/macro.preppin_data.model_to_csv).
    columns:
      - name: transaction_date
        type: VARCHAR
        tests:
          - not_null
          - accepted_values:
              values: [January, February, March, April, May, June, July, August, September, October, November, December]
      - name: bank
        type: VARCHAR
        tests:
          - not_null
          - accepted_values:
              values: [DS, DTB, DSB]
      - name: value
        type: BIGINT
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: '> 0'
      - name: bank_rank_per_month
        type: INT
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: '>= 1'
          - dbt_utils.expression_is_true:
              expression: '<= 3'
      - name: avg_transaction_value_per_rank
        type: NUMERIC
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: '> 0'
      - name: avg_rank_per_bank
        type: NUMERIC
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: '> 1'
          - dbt_utils.expression_is_true:
              expression: '< 3'