1. Dataset Name

ai_interview

2. Description

Interview-level dataset that pivots AI Interviewer event activity from user_analytics into one row per interview session, enriched with position, application, feedback, and credit-plan details. Each row represents a single AI interview, with milestone timestamps (invited, call started, completed, feedback generated/reviewed), derived durations, and recruiter/sourcer/hiring-manager ownership denormalized onto it. Also includes fuller position context (business unit, department, location, ATS job ID), AI-assistance request detail (assistance_source), and — for modular interviews specifically — avatar usage classification and live-section timing parsed from the interview's guide config (modular_interview_type, has_avatar, live section counts/bounds). Designed to power adoption, speed/efficiency, candidate experience, credit consumption, and modular-interview (avatar vs. non-avatar) analyses for the AI Interviewer dashboard.

3. Source Tables

• customer_views.user_analytics (pivoted per interview_id into milestone timestamps and AI-assistance requests)
• customer_views.interview_session (interview-level record — status, credits_used, interview_survey_rating, data_json guide config for modular interviews)
• customer_views.positions (recruiter, hiring manager, business unit, department (job function), location, status, creation timestamp, ATS job ID)
• customer_views.applications (match score and offer/hire outcome)
• customer_views.profile_feedback (sourcer attribution)
• customer_views.ai_credit_purchased_data (pre-aggregated into a total_credits_purchased CTE, joined via group_id)
• customer_views.employee (joined twice, via a deduped emp CTE, to resolve recruiter and hiring manager display names)
• customer_views.ai_interview_event_log (section_started / section_completed events, used to bound live sections for modular interviews)

4. Grain

One row per interview_id. Built from a left-join chain starting at the pivoted event CTE, so an interview with no completion, feedback, or application match still appears as a row with NULLs in the corresponding fields. The pivoted event CTE only contains interviews with at least one of the seven tracked interview_session_* events — an interview with zero tracked events never appears in the dataset at all, even if the interview_session record itself exists. A final DISTINCT is applied in the _ef_dataset CTE, so duplicate joins (e.g. multiple application matches) are collapsed.

5. Dashboards Supported

• AI Interviewer (AII) Dashboard
• 360 Interview (Modular Interview) Dashboard

6. Metrics Supported

7. Known Exclusions / Filters Applied

• Only the seven interview_session_* events are pivoted from user_analytics; any other event types on the same entity_id are ignored.
• Interviews with out-of-order event sequences are dropped from the dataset entirely, not just nulled — an interview's pivoted row is removed if call_started_at exists without invited_at, completed_at exists without call_started_at, feedback_generated_at exists without completed_at, or feedback_reviewed_at exists without feedback_generated_at. This is a data-quality guard, not a business filter, and can silently remove otherwise-valid interviews if upstream tracking is incomplete.
• completed_at uses MIN(timestamp) of the completion event (first completion), while completed_at_max uses MAX(timestamp) — used specifically for feedback turnaround calculations to anchor off the latest completion signal.
• duration_minutes is only populated when completed_at > call_started_at and the difference is positive — invalid or out-of-order timestamps resolve to NULL rather than a negative duration.
• is_call_initiation_error = TRUE only when a call was initiated but never reached the in-progress state (call_initiated_at IS NOT NULL AND call_started_at IS NULL).
• total_credits_purchased is pre-aggregated as SUM(total_credits) from ai_credit_purchased_data grouped by group_id, then joined onto every interview row for that customer — the value is denormalized and repeats across rows, so MAX() (not SUM) must be used when aggregating it in downstream metrics.
• sourcer defaults to 'Unassigned' via COALESCE when no owning user can be resolved.
• recruiter and hiring_manager are both resolved to "First Last (email-prefix)" via a deduped emp CTE joined against customer_views.employee; when no employee match is found, they fall back to the raw email, then to 'Unassigned'.
• interview_type_label is derived through a CASE waterfall over interview_type (profile_screening → Screening, coding_interview → Coding, behavioural_interview → Functional/Behavioural, modular_interview → Modular); any unmapped value falls to 'Other'.
• The final SELECT applies DISTINCT in the _ef_dataset CTE, so duplicate joins (e.g. multiple application matches) are collapsed.
• modular_interview_type, has_avatar, and modular_interview_type_credits are populated only for interview_type = 'modular_interview'; null for all other interview types. Both are derived as a proxy off credits_used (1.5/4.5 = avatar, 1/3 = no avatar, everything else falls to null/'Unclassified') rather than an explicit flag — treat as an approximation, not ground truth.
• live_section_count, first_live_section_start, last_live_section_end_effective are derived from the interview's data_json guide config and ai_interview_event_log, effectively populated only for modular interviews; "live" sections explicitly exclude pre_screening and assessment section types.
• business_unit, department, location_country, ats_job_id are null if position_id doesn't join to a positions record.

8. Schema