DB Explorer
recruitment_db
Schemas
public
candidates
interviews
missions
hr_clients
placements
consultants
fees
sectors_ref
Views
v_kpi_missions_report
v_recruitment_pipeline
Procedures
calc_commission
close_mission
1-- =============================================
2-- NX Recruitment — Mission KPI Analysis 2023-2025
3-- Author: A. Boujaddi | Engagement: Recruitment data audit
4-- Objective: Placement rate, time-to-fill, fees by sector
5-- =============================================
6
7WITH mission_stats AS (
8 SELECT
9 m.sector,
10 m.job_level,
11 DATE_PART('year', m.open_date) AS year,
12 COUNT(m.id) AS missions_count,
13 COUNT(p.id) AS placements_count,
14 ROUND(
15 COUNT(p.id)::NUMERIC / NULLIF(COUNT(m.id), 0) * 100, 1
16 ) AS placement_rate_pct,
17 AVG(
18 p.acceptance_date - m.open_date
19 )::INTEGER AS avg_delay_days,
20 SUM(h.amount_excl_tax) AS fees_total,
21 AVG(h.amount_excl_tax) AS fees_avg
22 FROM missions m
23 LEFT JOIN placements p ON p.mission_id = m.id
24 LEFT JOIN fees h ON h.placement_id = p.id
25 WHERE m.open_date BETWEEN '2023-01-01' AND '2025-12-31'
26 GROUP BY m.sector, m.job_level, DATE_PART('year', m.open_date)
27),
28
29-- 2nd CTE: sector ranking
30ranked AS (
31 SELECT *,
32 RANK() OVER (
33 PARTITION BY year
34 ORDER BY placement_rate_pct DESC
35 ) AS sector_rank
36 FROM mission_stats
37)
38SELECT
39 year,
40 sector,
41 job_level,
42 missions_count,
43 placements_count,
44 placement_rate_pct,
45 avg_delay_days,
46 ROUND(fees_total) AS fees_total,
47 ROUND(fees_avg) AS fees_avg,
48 sector_rank
49FROM ranked
50WHERE sector_rank <= 10
51ORDER BY year, sector_rank;
Results
Output
Plan
✓ 36 rows · 0.34s · PostgreSQL 16.2
| year | sector | job_level | missions_count | placements_count | placement_rate_pct | avg_delay_days | fees_total | fees_avg | sector_rank |
|---|---|---|---|---|---|---|---|---|---|
| 2023 | Tech / IT | Senior Mgr | 87 | 74 | 85.1% | 38 | 412 800 | 5 578 | 1 |
| 2023 | Finance / CFO | Manager | 64 | 52 | 81.3% | 44 | 374 400 | 7 200 | 2 |
| 2023 | HR / HRBP | Manager | 52 | 39 | 75.0% | 51 | 218 400 | 5 600 | 3 |
| 2023 | Supply Chain | Manager | 41 | 29 | 70.7% | 62 | 159 500 | 5 500 | 4 |
| 2024 | Sales | Manager | 68 | 44 | 64.7% | 58 | 211 200 | 4 800 | 5 |
| 2024 | Tech / IT | Senior Mgr | 112 | 98 | 87.5% | 35 | 548 800 | 5 600 | 1 |
| 2024 | Finance / CFO | Senior Mgr | 78 | 65 | 83.3% | 42 | 546 000 | 8 400 | 2 |
| 2024 | Data / BI | Manager | 94 | 77 | 81.9% | 40 | 415 800 | 5 400 | 3 |
| 2025 | Tech / IT | Senior Mgr | 128 | 114 | 89.1% | 32 | 638 400 | 5 600 | 1 |
| 2025 | Data / BI | Senior Mgr | 107 | 94 | 87.9% | 34 | 611 000 | 6 500 | 2 |
| 2025 | Sales | Manager | 89 | 54 | 60.7% ⚠ | 72 ⚠ | 259 200 | 4 800 | 8 |