1. Dataset Name

position_status_duration

2. Description

A long-format dataset that measures how many days each position spent in each of three lifecycle phases — Draft, Pending Approval, and Open. Each row represents one position in one phase, with the day count computed as the difference between that phase's defining start and end timestamps. This dataset powers the Avg Time in Each Status widget on the Recruitment Effectiveness Dashboard (T2), giving a phase-by-phase view of where positions spend the most time in their journey from creation to closure.

3. Source Tables

• customer_views.positions (aliased pos, via the ef_dataset CTE) — sole source table. Three UNION ALL branches read from the same filtered ef_dataset CTE, each deriving one lifecycle phase's day count independently.

4. Grain

One row per (position_id, status_phase) combination. A position can contribute 0, 1, 2, or 3 rows depending on how far it has progressed through the workflow and whether the required timestamp pairs are present for each phase — this long/narrow format lets each phase be measured independently without needing separate wide columns.

5. Dashboards Supported

• Recruitment Effectiveness Dashboard (T2) — powers the Avg Time in Each Status widget only.

6. Metrics Supported

7. Known Exclusions / Filters Applied

• system_id values 'sourcing' and 'resourcing' are excluded — these represent internally-generated or automated position records rather than standard requisitions.
• Positions with a NULL system_id are excluded.
• Positions with a NULL status are excluded.
• position_type = 'TalentCommunity' is excluded — talent community entries are not standard open positions and are filtered out of all downstream metrics.
• Each of the three phases (Draft, Pending Approval, Open) requires both its start and end timestamps to be non-null, and the end timestamp to be strictly after the start timestamp. This guard prevents positions that were reprocessed — e.g., approved once, then edited and resubmitted — from producing a negative or misleadingly short duration for that phase. A position missing or failing this guard for one phase is simply excluded from that phase's row; it can still contribute rows for the other phases.
• Date range filtering maps to creation_ts, even though creation_ts is not part of every phase's own day-count calculation — it anchors which positions fall within the selected reporting window.

8. Schema