Position AI Interviews
1. Dataset Name
positions_x_ai_interviews
2. Description
A position-level dataset that identifies which positions have ever used an AI interview and calculates a median time-to-fill for AI-enabled versus non-AI-enabled positions. Each row represents a single position that has received at least one application, including title, open/closed status, recruiter ownership, and the AI-adjusted time-to-fill figure. This dataset powers AI Interviewer adoption and speed/efficiency reporting on the AI Interviewer dashboard.
3. Source Tables
- customer_views.positions (base position record)
- customer_views.applications (used to determine positions that have received at least one application)
- customer_views.interview_session (used to flag positions with at least one AI interview conducted)
4. Grain
One row per position_id, restricted to positions that have received at least one application. A position is flagged as AI-enabled if any interview_session record exists against it, regardless of that interview's completion status.
5. Dashboards Supported
- AI Interviewer (AII) Dashboard
6. Metrics Supported
| Metric | Supported Breakdowns |
| Active Positions Using AI Interviewer | recruiter, job_function, business_unit |
| Median Time to Fill (AI vs. Non-AI) | recruiter, job_function, business_unit |
7. Known Exclusions / Filters Applied
- Positions with zero applications are excluded entirely — the applications join acts as an implicit filter, not just an enrichment.
- ai_enabled = TRUE is set purely on the existence of an interview_session row for the position_id; it does not require the interview to have reached completed status.
- Median time-to-fill is calculated using PERCENTILE_CONT(0.5) over closed_at - creation_ts, restricted to positions where closed_at IS NOT NULL — open positions are excluded from the time-to-fill calculation (though they still appear in the dataset for adoption counts).
- Time-to-fill comparisons (AI vs. non-AI) are computed as two separate PERCENTILE_CONT windows partitioned by ai_enabled, not a simple average, so single outlier positions have less influence on the reported figure.
8. Schema
| Column Name | Data Type | Notes |
| position_id | bigint | Primary grain key for this dataset; sourced from positions |
| title | varchar | Position title; sourced from positions |
| status | varchar | Raw position status; sourced from positions |
| is_open | bigint | Flag (0/1); 1 if position is currently open; sourced from positions |
| creation_ts | datetime | Position creation timestamp; sourced from positions |
| closed_at | datetime | Position close timestamp; sourced from positions. NULL for open positions. |
| recruiter | varchar | Recruiter owning the position; sourced from positions |
| job_function | varchar | Job function/category of the position; sourced from positions |
| business_unit | varchar | Business unit of the position; sourced from positions |
| location_country | varchar | Country of the position; sourced from positions |
| has_application | bigint | Derived: 1 if at least one row exists for this position_id in applications (implicit filter — only positions with has_application = 1 appear in the dataset) |
| ai_enabled | bigint | Derived: 1 if at least one interview_session row exists for this position_id, regardless of interview completion status |
| time_to_fill_days | bigint | Derived: DATE_DIFF('DAY', creation_ts, closed_at), only calculated when closed_at IS NOT NULL |
| median_time_to_fill_ai | double | Derived: PERCENTILE_CONT(0.5) OVER (PARTITION BY ai_enabled = TRUE) of time_to_fill_days, restricted to closed positions |
| median_time_to_fill_non_ai | double | Derived: PERCENTILE_CONT(0.5) OVER (PARTITION BY ai_enabled = FALSE) of time_to_fill_days, restricted to closed positions |