HR & payroll: MQL recipes

Recipes

Age, seniority and next long-service award
Leave request and balance
Salary distribution and gender pay gap
Overtime pay by ISO week
Logins, emails and trial period end of new hires
Gender pay gap test with Mann-Whitney
Turnover rate and tenure of the leavers
Compa-ratio and range penetration
Bradford factor of short absences
Employee Net Promoter Score

executed on MentDB

Age, seniority and next long-service award

At the 31 December 2026 closing, HR needs the age and the seniority of an employee hired in 2011, and the date of the next long-service award (every 5 years of service).

Pipeline

01 · ageFull years since birthdate nb_day→02 · seniorityFull years since hiringmath floor→03 · awardAward datedate add

Script

admin

#1. Employee data and the reference date;
-> "[birth]" "1984-03-12";
-> "[hire]" "2011-09-01";
-> "[ref]" "2026-12-31";
#2. Age and seniority in full years;
-> "[age]" (math floor (/ (date nb_day [birth] [ref]) 365.25));
-> "[sen]" (math floor (/ (date nb_day [hire] [ref]) 365.25));
#3. Next long-service milestone, every 5 years;
-> "[next]" (* (+ (math floor (/ [sen] 5)) 1) 5);
concat "{\"age\": " [age] ", \"seniority\": " [sen] ", \"next_award\": " [next] ", \"award_date\": \"" (date add [hire] "YEAR" [next]) "\", \"days_left\": " (date nb_day [ref] (date add [hire] "YEAR" [next])) "}";

Result

mentdb

{"age": 42, "seniority": 15, "next_award": 20, "award_date": "2031-09-01", "days_left": 1705}

Go further

Age at today's date: date age with the birth date
Retirement date: date add of the legal age to the birth date, then date last_day_of_month

executed on MentDB

Leave request and balance

An employee asks for leave from 21 December 2026 to 8 January 2027. The script counts the business days of the request, removes the public holidays (25 December and 1 January), and compares with the leave accrued since 1 June at 2.08 days a month plus 4 days carried over.

Pipeline

01 · countBusiness days of the requestdate nb_business_days→02 · holidaysHolidays on a weekdaydate is_weekend→03 · accrualDays accruedmath calc→04 · balanceBalance after the leavemath round

Script

admin

#1. Leave request from 2026-12-21 to 2027-01-08 included;
-> "[days]" (date nb_business_days "2026-12-21" (date add "2027-01-08" "DAY" 1));
#2. Remove the public holidays falling on a business day;
-> "[hol]" (+ (- 1 (date is_weekend "2026-12-25")) (- 1 (date is_weekend "2027-01-01")));
-> "[taken]" (- [days] [hol]);
#3. Accrual: 2.08 days per month since June 1, plus 4 days carried over;
-> "[months]" (- (date month "2026-11-30") 5);
-> "[acq]" (math round (math calc "2.08*[months]+4") 2);
concat "Request: " [days] " business days, " [hol] " public holidays, " [taken] " days of leave. Accrued " [acq] " days, balance after the leave " (math round (- [acq] [taken]) 2) " days.";

Result

mentdb

Request: 15 business days, 2 public holidays, 13 days of leave. Accrued 16.48 days, balance after the leave 3.48 days.

Go further

Return-to-work date: date add_business_days of 1 from the last day of leave
Check that a holiday is in the request with date between

executed on MentDB

Salary distribution and gender pay gap

For the annual equal-pay report, the script takes the monthly gross salaries of a team, gives the mean, the quartiles and the D9/D1 ratio, and the gap between the mean salaries of men and women.

Pipeline

01 · quartilesQ1, median, Q3stat quartiles→02 · decilesD9 / D1 ratiostat percentile→03 · gapPay gap on the meansstat mean

Script

admin

#1. Monthly gross salaries of the team, women then men;
-> "[w]" "[2350, 2780, 3100, 3420, 4150, 2600]";
-> "[m]" "[2480, 2950, 3300, 3650, 4600, 5200]";
-> "[all]" "[2350, 2780, 3100, 3420, 4150, 2600, 2480, 2950, 3300, 3650, 4600, 5200]";
#2. Distribution of the salaries;
-> "[q]" (stat quartiles [all]);
-> "[d]" (/ (stat percentile [all] 90) (stat percentile [all] 10));
#3. Gender pay gap on the mean salary;
-> "[gap]" (math calc (concat "(" (stat mean [m]) "-" (stat mean [w]) ")/" (stat mean [m]) "*100"));
concat "{\"mean\": " (math round (stat mean [all]) 2) ", \"quartiles\": " [q] ", \"d9_d1\": " (math round [d] 2) ", \"pay_gap_pct\": " (math round [gap] 1) "}";

Result

mentdb

{"mean": 3381.67, "quartiles": [2735,3200,3775], "d9_d1": 1.83, "pay_gap_pct": 17}

Go further

Is the gap significant? stat mann_whitney on the two series
Annual salary mass with a 2.5% raise: stat sum then math percent

executed on MentDB

Overtime pay by ISO week

The payroll officer computes the overtime of a warehouse employee from the clocked hours. Under French rules hours beyond 35 a week are paid +25 percent up to 43 hours and +50 percent beyond. The script sums the hours by ISO week, splits the overtime, prices it at 13.20 EUR an hour and lists the Saturdays worked.

Pipeline

01 · weekISO week of each daydate week_of_year→02 · saturdaySaturdays workeddate day_of_week→03 · sumHours per weekjson uobject→04 · split25 and 50 percent bandsmath min→05 · payOvertime paymath round

Script

admin

#1. Clocked hours of a warehouse employee over two weeks, French rules: 35 h base, +25 pct up to 43 h, +50 pct beyond;
json load "rx_ts" "{\"2026-10-19\": 8, \"2026-10-20\": 9.5, \"2026-10-21\": 8, \"2026-10-22\": 10, \"2026-10-23\": 7.5, \"2026-10-24\": 4, \"2026-10-26\": 7, \"2026-10-27\": 7, \"2026-10-28\": 8, \"2026-10-29\": 7.5, \"2026-10-30\": 6}";
-> "[rate]" 13.20;
json load "rx_w" "{}";
#2. Sum the hours by ISO week, keep the Saturdays worked;
-> "[sat]" "";
json parse_obj "rx_ts" "/" "[day]" "[h]" {
	-> "[wk]" (concat "W" (date week_of_year [day]));
	if (string contains (json fields "rx_w" "/") [wk]) {json uobject "rx_w" "/" [wk] (+ (json select "rx_w" (concat "/" [wk])) [h]) NUM;} {json iobject "rx_w" "/" [wk] [h] NUM;};
	if (== (date day_of_week [day]) 6) {-> "[sat]" (concat [sat] [day] " ");};
};
#3. Overtime split and pay of each week;
-> "[pay]" 0; -> "[detail]" "";
json parse_obj "rx_w" "/" "[wk]" "[tot]" {
	-> "[ot25]" (math min (math max (- [tot] 35) 0) 8);
	-> "[ot50]" (math max (- [tot] 43) 0);
	-> "[amt]" (math round (+ (* (* [ot25] [rate]) 1.25) (* (* [ot50] [rate]) 1.5)) 2);
	-> "[pay]" (+ [pay] [amt]);
	-> "[detail]" (concat [detail] [wk] ": " [tot] " h, +25%: " [ot25] " h, +50%: " [ot50] " h, " [amt] " EUR; ");
};
json unload "rx_ts"; json unload "rx_w";
concat [detail] "overtime pay " (math round [pay] 2) " EUR | Saturdays: " (string trim [sat])

Result

mentdb

W44: 35.5 h, +25%: 0.5 h, +50%: 0 h, 8.25 EUR; W43: 47.0 h, +25%: 8 h, +50%: 4 h, 211.2 EUR; overtime pay 219.45 EUR | Saturdays: 2026-10-24

Go further

A collective agreement may set other bands: put 35, 43, 1.25 and 1.5 in parameters
date dayname returns the day name in the language of the server

executed on MentDB

Logins, emails and trial period end of new hires

IT and HR prepare the accounts of the November hires. The script builds a login and a company email without accents, spaces or apostrophes, checks the email, and computes the end of the trial period: 4 months for a cadre, 2 months for an employee, 1 month for a fixed-term contract.

Pipeline

01 · cleanRemove accentsstring unaccent→02 · loginLetters onlystring regexp_replace→03 · emailCheck the addresstype is_email→04 · trialStart + months - 1 daydate add→05 · weekdayDay of the weekdate day_of_week

Script

admin

#1. Three hires of November from the HR form;
json load "rx_new" "[{\"first\": \"Hélène\", \"last\": \"Lebon-Técher\", \"start\": \"2026-11-02\", \"contract\": \"CDI\", \"status\": \"cadre\"}, {\"first\": \"Jean Noël\", \"last\": \"D'Abbadie\", \"start\": \"2026-11-09\", \"contract\": \"CDI\", \"status\": \"employe\"}, {\"first\": \"Zoé\", \"last\": \"Grondin\", \"start\": \"2026-11-16\", \"contract\": \"CDD\", \"status\": \"employe\"}]";
json load "rx_acc" "[]";
for (-> "[i]" 0) (< [i] (json count "rx_new" /)) (++ "[i]") {
	-> "[p]" (concat "/[" [i] "]");
	#2. Login = first letter of the first name + last name, no accents, no spaces or quotes;
	-> "[first]" (string lower (string unaccent (json select "rx_new" (concat [p] "/first"))));
	-> "[last]" (string regexp_replace (string lower (string unaccent (json select "rx_new" (concat [p] "/last")))) "[^a-z]" "");
	-> "[login]" (concat (string left [first] 1) [last]);
	-> "[mail]" (concat (string replace [first] " " "-") "." [last] "@acme-reunion.re");
	#3. End of the trial period: 4 months for a cadre, 2 months otherwise, 1 month for a CDD;
	-> "[start]" (json select "rx_new" (concat [p] "/start"));
	if (equal (json select "rx_new" (concat [p] "/contract")) "CDD") {-> "[m]" 1;} {if (equal (json select "rx_new" (concat [p] "/status")) "cadre") {-> "[m]" 4;} {-> "[m]" 2;};};
	-> "[trial]" (date diff (date add [start] "MONTH" [m]) "DAY" 1);
	json iarray "rx_acc" "/" (concat "{\"login\": \"" [login] "\", \"email\": \"" [mail] "\", \"email_ok\": " (type is_email [mail]) ", \"start\": \"" [start] "\", \"trial_end\": \"" [trial] "\", \"trial_end_weekday\": " (date day_of_week [trial]) "}") OBJ;
};
-> "[out]" (string regexp_replace (json doc "rx_acc") "\n *" "");
json unload "rx_new"; json unload "rx_acc";
[out]

Result

mentdb

[{"email_ok": 1,"trial_end_weekday": 1,"start": "2026-11-02","trial_end": "2027-03-01","login": "hlebontecher","email": "helene.lebontecher@acme-reunion.re"},{"email_ok": 1,"trial_end_weekday": 5,"start": "2026-11-09","trial_end": "2027-01-08","login": "jdabbadie","email": "jean-noel.dabbadie@acme-reunion.re"},{"email_ok": 1,"trial_end_weekday": 2,"start": "2026-11-16","trial_end": "2026-12-15","login": "zgrondin","email": "zoe.grondin@acme-reunion.re"}]

Go further

Check that the login is free in the directory and add a digit when it is taken
Plan a reminder to the manager 15 days before trial_end with date diff

executed on MentDB

Gender pay gap test with Mann-Whitney

For the pay equity report, HR compares the base salaries of women and men in the same grade (senior developers P3). The script computes the median gap and runs a Mann-Whitney U test, which needs no normality assumption on such small groups, then states whether the gap is significant.

Pipeline

01 · mediansMedian of each groupstat median→02 · gapGap in pctmath round→03 · testU and exact p-valuestat mann_whitney→04 · decisionReview or notif

Script

admin

#1. Annual base salaries in kEUR of the senior developers, grade P3, by gender;
-> "[women]" "[52.0, 49.5, 55.2, 50.8, 47.9, 53.1, 51.4]";
-> "[men]" "[54.5, 58.2, 51.9, 56.0, 60.3, 55.1, 57.4, 53.8]";
#2. Median gap, the usual indicator of the pay equity index;
-> "[mw]" (stat median [women]); -> "[mm]" (stat median [men]);
-> "[gap]" (* (/ (- [mm] [mw]) [mm]) 100);
#3. Non-parametric Mann-Whitney test: no normality needed on 15 salaries;
json load "rx_mw" (stat mann_whitney [women] [men]);
-> "[out]" (concat "median women " [mw] " k, men " [mm] " k, gap " (math round [gap] 2) " pct | U " (json select "rx_mw" "/u") ", p-value " (math round (json select "rx_mw" "/p_value") 5) ", method " (json select "rx_mw" "/method") " | " (if (< (json select "rx_mw" "/p_value") 0.05) {"significant gap: review the P3 salaries";} {"no significant gap";}));
json unload "rx_mw";
[out]

Result

mentdb

median women 51.4 k, men 55.55 k, gap 7.47 pct | U 6, p-value 0.00932, method exact | significant gap: review the P3 salaries

Go further

With 7 and 8 values and no tie the test uses the exact distribution, as SciPy does
Control the seniority before concluding: compare the residuals of a salary regression on tenure

executed on MentDB

Turnover rate and tenure of the leavers

At year end HR computes the staff turnover on the average headcount, the voluntary turnover (resignations only), the share of early leavers (less than one year, including failed trial periods) and the mean and median tenure of the leavers.

Pipeline

01 · tenureDays of servicedate nb_day→02 · averageAverage headcountmath avg→03 · ratesTurnover ratesmath round→04 · medianMedian tenurestat median

Script

admin

#1. Leavers of the year with hire and exit dates, headcount on 1 January and 31 December;
json load "rx_out" "[{\"id\": \"E102\", \"hired\": \"2019-03-04\", \"left\": \"2026-02-27\", \"reason\": \"resignation\"}, {\"id\": \"E187\", \"hired\": \"2025-09-01\", \"left\": \"2026-03-31\", \"reason\": \"trial\"}, {\"id\": \"E075\", \"hired\": \"2014-06-16\", \"left\": \"2026-06-30\", \"reason\": \"retirement\"}, {\"id\": \"E163\", \"hired\": \"2023-01-09\", \"left\": \"2026-08-14\", \"reason\": \"resignation\"}, {\"id\": \"E191\", \"hired\": \"2026-01-05\", \"left\": \"2026-04-03\", \"reason\": \"trial\"}, {\"id\": \"E140\", \"hired\": \"2021-10-11\", \"left\": \"2026-10-09\", \"reason\": \"resignation\"}]";
-> "[hc_start]" 84; -> "[hc_end]" 91;
#2. Tenure in years of each leaver, early leavers stayed less than one year;
json load "rx_ten" "[]"; -> "[early]" 0; -> "[resign]" 0;
json parse_array "rx_out" "/" "[e]" {
	json load "rx_e" [e];
	-> "[days]" (date nb_day (json select "rx_e" "/hired") (json select "rx_e" "/left"));
	json iarray "rx_ten" "/" (math round (/ [days] 365.25) 2) NUM;
	if (< [days] 365) {-> "[early]" (+ [early] 1);};
	if (equal (json select "rx_e" "/reason") "resignation") {-> "[resign]" (+ [resign] 1);};
};
#3. Turnover rate on the average headcount, voluntary turnover and early turnover;
-> "[avg_hc]" (math avg (concat "[" [hc_start] ", " [hc_end] "]"));
-> "[n]" (json count "rx_out" "/");
-> "[out]" (concat "average headcount " [avg_hc] ", turnover " (math round (* (/ [n] [avg_hc]) 100) 2) " pct, voluntary " (math round (* (/ [resign] [avg_hc]) 100) 2) " pct, early leavers " (math round (* (/ [early] [n]) 100) 1) " pct of leavers, tenures " (string regexp_replace (json doc "rx_ten") "\s" "") ", mean tenure " (math round (math avg (json doc "rx_ten")) 2) " years, median " (stat median (json doc "rx_ten")));
json unload "rx_out"; json unload "rx_e"; json unload "rx_ten";
[out]

Result

mentdb

average headcount 87.5, turnover 6.86 pct, voluntary 3.43 pct, early leavers 33.3 pct of leavers, tenures [6.99,0.58,12.04,3.59,0.24,4.99], mean tenure 4.74 years, median 4.29

Go further

Two of six leavers left in their first year: look at the onboarding of the team that hired them
Compute it by department with a json parse_obj on a JSON grouped by service

executed on MentDB

Compa-ratio and range penetration

Before the salary review, the compensation analyst places each employee in the range of the grade: compa-ratio (salary / midpoint) and range penetration ((salary - min) / (max - min)), flags the salaries below the range and the red circles above it, and sorts the compa-ratios.

Pipeline

01 · rangeGrade rangejson select→02 · compaSalary / midpointmath round→03 · flagBelow or above rangeif→04 · sortSorted ratiosmath order_double→05 · averageAveragemath avg

Script

admin

#1. Salary ranges of the grades and the employees of the finance department;
json load "rx_rg" "{\"G4\": {\"min\": 38000, \"mid\": 44000, \"max\": 50000}, \"G5\": {\"min\": 46000, \"mid\": 53000, \"max\": 60000}}";
json load "rx_emp" "[{\"n\": \"Payet\", \"g\": \"G4\", \"s\": 41200}, {\"n\": \"Hoarau\", \"g\": \"G4\", \"s\": 47800}, {\"n\": \"Grondin\", \"g\": \"G5\", \"s\": 61500}, {\"n\": \"Técher\", \"g\": \"G5\", \"s\": 50900}, {\"n\": \"Lebon\", \"g\": \"G4\", \"s\": 36900}]";
json load "rx_cr" "[]"; -> "[lines]" "";
#2. Compa-ratio = salary / midpoint, range penetration = salary - min / max - min;
json parse_array "rx_emp" "/" "[e]" {
	json load "rx_e" [e];
	-> "[g]" (concat "/" (json select "rx_e" "/g"));
	-> "[s]" (json select "rx_e" "/s");
	-> "[cr]" (math round (/ [s] (json select "rx_rg" (concat [g] "/mid"))) 3);
	-> "[pen]" (math round (* 100 (/ (- [s] (json select "rx_rg" (concat [g] "/min"))) (- (json select "rx_rg" (concat [g] "/max")) (json select "rx_rg" (concat [g] "/min"))))) 0);
	json iarray "rx_cr" "/" [cr] NUM;
	-> "[flag]" (if (< [pen] 0) {"below range";} {if (> [pen] 100) {"red circle";} {"in range";}});
	-> "[lines]" (concat [lines] (json select "rx_e" "/n") " " [cr] " " [pen] " pct " [flag] "; ");
};
#3. Sorted compa-ratios and the department average;
-> "[out]" (concat [lines] "sorted " (math order_double (json doc "rx_cr")) ", average compa-ratio " (math round (math avg (json doc "rx_cr")) 3));
json unload "rx_rg"; json unload "rx_emp"; json unload "rx_e"; json unload "rx_cr";
[out]

Result

mentdb

Payet 0.936 27 pct in range; Hoarau 1.086 82 pct in range; Grondin 1.16 111 pct red circle; Técher 0.96 35 pct in range; Lebon 0.839 -9 pct below range; sorted [0.839,0.936,0.96,1.086,1.16], average compa-ratio 0.996

Go further

A red circle gets a one-time bonus instead of a raise, a salary below the range is raised first
Use the compa-ratio in a merit matrix: higher raise for a good rating and a low compa-ratio

executed on MentDB

Bradford factor of short absences

The HR officer reviews sickness absence with the Bradford factor B = S^2 x D (S spells, D days over 52 weeks), which weighs frequent short absences more than one long absence. The script computes B for each employee, applies the trigger points 50 and 200 and ranks the scores.

Pipeline

01 · spellsNumber of spellsjson count→02 · daysTotal daysstat sum→03 · scoreS squared x Dmath power→04 · rankSorted scoresmath order_int

Script

admin

#1. Sickness absence spells of the last 52 weeks: employee and days of each spell;
json load "rx_abs" "{\"A. Payet\": [1, 1, 2, 1, 1, 1], \"M. Hoarau\": [12], \"L. Grondin\": [2, 3, 1], \"S. Técher\": [1, 2], \"J. Lebon\": [4, 1, 1, 2]}";
json load "rx_bf" "[]"; -> "[lines]" "";
#2. Bradford factor B = S squared x D, S spells, D total days;
json parse_obj "rx_abs" "/" "[name]" "[spells]" {
	json load "rx_s" [spells];
	-> "[S]" (json count "rx_s" "/");
	-> "[D]" (stat sum [spells]);
	-> "[B]" (* (math power [S] 2) [D]);
	json iarray "rx_bf" "/" [B] INT;
	-> "[lvl]" (if (>= [B] 200) {"formal review";} {if (>= [B] 50) {"informal talk";} {"no action";}});
	-> "[lines]" (concat [lines] [name] ": S " [S] ", D " [D] ", B " [B] " " [lvl] "; ");
};
#3. Ranking of the scores, the long single absence is not penalised;
-> "[out]" (concat [lines] "scores sorted " (math order_int (json doc "rx_bf")));
json unload "rx_abs"; json unload "rx_s"; json unload "rx_bf";
[out]

Result

mentdb

L. Grondin: S 3, D 6, B 54 informal talk; M. Hoarau: S 1, D 12, B 12 no action; J. Lebon: S 4, D 8, B 128 informal talk; S. Técher: S 2, D 3, B 12 no action; A. Payet: S 6, D 7, B 252 formal review; scores sorted [12,12,54,128,252]

Go further

Six one-day absences score 252, one absence of 12 days scores 12: that is the purpose of the factor
Exclude absences linked to a disability or a pregnancy before applying the triggers

executed on MentDB

Employee Net Promoter Score

After the annual survey, HR computes the eNPS of the question 'how likely are you to recommend us as an employer'. The script builds the frequency table of the 0-10 answers, sums the shares of promoters (9-10) and detractors (0-6), and gives the eNPS, the most frequent answer, the median and the mean.

Pipeline

01 · tableFrequency tablestat frequency→02 · groupsPromoters and detractorsjson select→03 · enpspct P - pct Dmath round→04 · modeMost frequent answerstat mode

Script

admin

#1. Answers of 30 employees to: how likely are you to recommend us as an employer, 0 to 10;
-> "[sc]" "[9, 7, 10, 8, 6, 9, 3, 8, 10, 7, 9, 5, 8, 9, 10, 6, 7, 9, 4, 8, 9, 10, 7, 2, 9, 8, 6, 10, 9, 7]";
#2. Frequency table, promoters 9-10, passives 7-8, detractors 0-6;
json load "rx_f" (stat frequency [sc]);
-> "[pro]" 0; -> "[det]" 0;
json parse_array "rx_f" "/" "[row]" {
	json load "rx_r" [row];
	if (>= (json select "rx_r" "/value") 9) {-> "[pro]" (+ [pro] (json select "rx_r" "/frequency"));};
	if (<= (json select "rx_r" "/value") 6) {-> "[det]" (+ [det] (json select "rx_r" "/frequency"));};
};
#3. eNPS = pct promoters - pct detractors, the most frequent answer and the median;
-> "[out]" (concat "promoters " (math round (* [pro] 100) 1) " pct, detractors " (math round (* [det] 100) 1) " pct, eNPS " (math round (* (- [pro] [det]) 100) 0) " | mode " (stat mode [sc]) ", median " (stat median [sc]) ", mean " (math round (math avg [sc]) 2));
json unload "rx_f"; json unload "rx_r";
[out]

Result

mentdb

promoters 43.3 pct, detractors 23.3 pct, eNPS 20 | mode [9], median 8, mean 7.63

Go further

An eNPS of +20 is good, but a fifth of detractors: read their comments first
Compare two years with stat mann_whitney on the raw answers

Also in Business & IT

Accountant & manager

Invoices with VAT, margins, break-even, depreciation and payment due dates.

10 recipes

Financial analyst

Loan schedules, NPV and IRR, portfolio optimisation: solve and minimise in one line.

10 recipes

Actuary & risk analyst

Frequency and severity laws, simulated losses, quantiles and risk capital.

10 recipes

Logistics & supply chain

Order quantities, safety stock, reorder points and delivery dates.

10 recipes

Sales & marketing analyst

Growth trends, seasonality, A/B tests and forecasts.

10 recipes

Web & API developer

Build JSON responses, encode URLs and headers, parse logs with regex.

10 recipes

Security engineer

Password hashing, webhook signatures, password strength and entropy.

10 recipes

Text & language processing

Count words, match misspellings, convert names between case conventions.

10 recipes