Data Analytics HR Analytics · SQL · Succession Planning · Streamlit

Talent Match
Intelligence

SQL-powered succession planning system that ranks 2,010 employees against top performer benchmarks using a 17-CTE matching algorithm.

SQL PostgreSQL · 17 CTEs · PERCENTILE_CONT · MODE() Stack Supabase · Streamlit · Python · Plotly · OpenRouter Dataset 2,010 employees · 5 years · 6 tables · 180K+ rows Context Data Analyst Case Study 2025 · Paragon Corp
2,010
Employees
scored & ranked
17
SQL CTEs in
matching query
180K+
Rows across
6 dataset tables
5
TGV categories
in success formula
01 · Business Problem

Who should fill the next leadership vacancy? Without data, it's a guess.

Paragon Corp needs to fill vacancies by matching candidates to the profile of proven top performers. Without a systematic method, managers rely on subjective judgment — missing employees who look different on paper but share the same underlying success traits.

Without System
Manager opens vacancy
↓
Who should replace the leader?
↓
Manual judgment
↓
Bias & missed talent
With Talent Match
Select top performer benchmarks
↓
Success pattern discovery (EDA)
↓
17-CTE SQL matching algorithm
↓
All 2,010 employees ranked

The dataset spans 6 tables and 5 years of assessment data — psychometrics, competency scores, PAPI work preferences, CliftonStrengths themes, and performance ratings — covering 2,010 employees.

PostgreSQLHR Analytics Succession PlanningEDA StreamlitOpenRouter
02 · Success Pattern Discovery

What actually separates top performers from everyone else?

187 out of 1,975 employees hold Rating 5 (9.5%). The EDA asked: what do these 187 people share that the other 1,788 don't? Three findings shaped the entire algorithm.

Top competency differentiators

Counterintuitive finding: leadership style vs leadership results

PAPI Work Preference Scales — Rating-5 vs Others
Leadership Preference (G)
R5
4.15
Others
4.50
Planning & Organization (P)
R5
4.85
Others
4.50
Rating-5 employees scored lower on leadership preference, but higher on planning and collaboration. High performers drive results through discipline and teamwork — not positional authority.

Success formula — 5 weighted dimensions

03 · SQL Matching Engine

17 CTEs. One query. Every employee scored at runtime.

The matching algorithm is a single parameterized SQL query. Any manager selects 1–3 top performers as benchmark — the algorithm computes medians, compares all 2,010 employees, and returns a ranked list. No precomputation, no hardcoded IDs.

17 CTE query Runtime matching No precomputation PERCENTILE_CONT medians MODE() categorical baseline

Scoring formulas

-- Numeric TV (higher = better)
LEAST(100, ROUND(
  user_score / NULLIF(baseline_score, 0) * 100, 2
))

-- Inverse PAPI scales (lower = better)
LEAST(100, GREATEST(0, ROUND(
  (2 * baseline_score - user_score) /
  NULLIF(baseline_score, 0) * 100, 2
)))

-- Categorical (DISC, MBTI — exact match)
CASE WHEN user_val = baseline_val
  THEN 100.0 ELSE 0.0 END

-- CliftonStrengths overlap
ROUND(overlap_count::NUMERIC / 5 * 100, 2)
"The benchmark is never fixed. Any manager can define it at runtime by selecting 1–3 top performers as reference."

CTE architecture — 17 steps

CTE 1–2
benchmark_employees → unpack selected IDs; benchmark_psych → psychometric scores for those employees
CTE 3–6
baseline_psych, baseline_papi, baseline_competency → PERCENTILE_CONT(0.5) medians per TV across benchmarks
CTE 7–8
benchmark_disc_mode, benchmark_mbti_mode → MODE() WITHIN GROUP for categorical baselines
CTE 9
benchmark_strength_pool → pooled unique top-5 CliftonStrengths themes from all benchmarks
CTE 10–12
candidate_info, candidate_psych, candidate_papi → all 2,010 employees with org context and scores
CTE 13
candidate_strengths_overlap → top-5 theme overlap count per candidate vs benchmark pool
CTE 14
tv_match_rates_long → per-employee × per-TV match rate in long format (UNION ALL across all TVs)
CTE 15–17
tgv_match_rates → AVG within TGV; tgv_weights → hardcoded weights; final_match_rates → weighted total
04 · Sample Ranking Output

Run the query. Get a ranked list in under 10 seconds.

For a sample vacancy benchmarked against a top-performing manager, the algorithm returns all 2,010 employees with component-level breakdowns. Below: top 3 candidates from one sample run.

#1 Top Match
Yoga Erlangga M.
92.5%
Final Match Rate
Cognitive92.2%
Competency98.0%
Work Prefs86.8%
#2
Prasetyo Halim
89.9%
Final Match Rate
Cognitive88.1%
Competency96.5%
Work Prefs84.2%
#3
Umar Wibowo
89.1%
Final Match Rate
Cognitive90.3%
Competency94.2%
Work Prefs83.1%
05 · Dashboard Showcase

Any manager can run a vacancy search. No SQL required.

The Streamlit app wraps the 17-CTE algorithm in a form-driven interface — define a vacancy, pick benchmark employees, get ranked results with AI-generated job profiles and interactive visualizations.

Vacancy creation screen: select benchmark employees, generate AI job profile
① Vacancy creation — select benchmarks, generate AI job profile
Match score distribution histogram across all 2,010 employees
② Match score distribution — all 2,010 employees
Top candidate ranking bar chart showing top 15 employees by match score
③ Top candidate ranking — top-15 bar chart
Candidate detail radar chart comparing individual vs benchmark across all TGV dimensions
④ Candidate detail — radar chart vs benchmark = 100%
06 · Technical Architecture

From database to dashboard in one cohesive stack.

Data model

  • Star schema — employees as fact table, 5 assessment tables as dimensions
  • 11 tables + FK constraints — includes talent_benchmarks and talent_vacancies for runtime vacancy management
  • 6 composite indexes — on (employee_id, year) joins for competency and PAPI tables
  • Paginated fetch — PostgREST caps at 1,000 rows; custom fetch_table() loops until all 2,010 are loaded
07 · Key Insights

Three findings that changed the entire algorithm design.

Psychometric signal
Stamina beats raw IQ
Pauli (mental arithmetic stamina) is the strongest cognitive differentiator. IQ shows no meaningful gap between R5 and others. Sustained effort predicts top performance at this grade level.
Pauli
+3.05
GTQ
+1.20
IQ
−0.59
PAPI finding
Planning beats authority
R5 employees score lower on PAPI Leadership (G) — the desire for authority — but significantly higher on Planning (P) and Need to Belong (W). Top performers lead through systems and collaboration, not title.
R5 vs Others delta
Leadership ↓ −0.35 Planning ↑ +0.35
CliftonStrengths
Future-oriented, self-driven
Top-5 themes in R5 cluster around vision and initiative — not authority. DISC type shows no dominant pattern: high performance is execution-dependent, not style-dependent.
Futuristic19.8%
Restorative19.8%
Intellection18.7%
Self-Assurance17.6%
Activator16.0%
08 · Lessons Learned

What I would tell myself before starting this project.

ERD vs reality

The case study brief described GTQ1–GTQ5 and Tiki1–Tiki4 as separate columns. The actual database stores single composite values (gtq, tiki). Iterating through the live schema revealed the real structure — documentation can lie.

Model availability

OpenRouter free-tier models deprecated without notice mid-development. Required live API querying to discover a working model. Never hardcode a free-tier model name.

Supabase pagination

PostgREST caps at 1,000 rows per request. Built a custom paginated fetch_table() to handle all 2,010 employees cleanly — the kind of edge case that only surfaces at scale.

EDA should challenge assumptions

The PAPI Leadership (G) finding — that R5 employees score lower — was the most analytically valuable result. The data pushed back against the obvious assumption. That's when EDA earns its keep.

Future improvements

Try it · See the code

The algorithm is live. Try it.

The Streamlit app is publicly deployed — enter a role, pick benchmark employees, get a ranked list of 2,010 candidates in under 10 seconds. Full SQL query and Python code on GitHub.

↗ Open Live App View on GitHub ← Back to portfolio