Core Entities
LeaveType: (leave_type_id, name, accrual_rate_days_per_month, max_balance_days, carry_over_max_days, is_paid, requires_documentation). Examples: Annual Leave (1.67 days/month, max 30 days), Sick Leave (1 day/month, no carry-over), Parental Leave (fixed 90 days, requires HR approval). LeaveBalance: (employee_id, leave_type_id, balance_days, accrued_ytd, used_ytd, carry_over_days). LeaveRequest: (request_id, employee_id, leave_type_id, start_date, end_date, days_requested, status, reason, document_url, approved_by, approved_at). LeaveTransaction: audit log of every balance change (accrual, request approval, cancellation, adjustment).
Accrual Engine
Accrual runs on a schedule (monthly or bi-weekly) and credits leave balances. Calculation: days_to_accrue = leave_type.accrual_rate * (days_in_period / 30). Cap at max_balance: new_balance = min(current_balance + days_to_accrue, leave_type.max_balance_days). Write both a balance update and a LeaveTransaction record (type=ACCRUAL, amount=days_accrued, date=NOW()). Proration: employees who join mid-month accrue proportionally for the days worked. Employees on unpaid leave may not accrue (depends on policy). Run accrual as an idempotent batch job: check if an ACCRUAL transaction already exists for this employee + period before crediting.
Leave Request and Approval Workflow
On request submission: validate balance (balance_days >= days_requested). Check for conflicts: are there other approved requests overlapping the same dates? Notify the manager. Manager actions: APPROVE or REJECT with a reason. Multi-level approval for long absences (> 10 days: requires HR approval after manager). On approval: deduct days from balance (balance_days -= days_requested), add DEDUCTION transaction. On rejection: no balance change. Cancellation: if approved leave is canceled before start_date: credit balance back (CREDIT transaction). If canceled mid-leave: credit remaining days. Cancellations require manager approval for completed days.
Balance Calculation
Balance must be accurate in real time. Compute from transactions (event sourcing approach): available_balance = SUM(transactions WHERE type IN (ACCRUAL, CARRY_OVER, ADJUSTMENT, CANCELLATION_CREDIT)) – SUM(transactions WHERE type IN (DEDUCTION)). This is always consistent even if a scheduled job misruns. Store a denormalized current balance for fast reads, but recompute from transactions for audits. Negative balance: some policies allow going negative (borrowing future accruals). Cap negative balance at -(3 months of accrual). Track separately from positive balance.
Year-End Carry-Over
At fiscal year end: for each employee, calculate carry_over = min(balance_days, leave_type.carry_over_max_days). Set new balance = carry_over. Record YEAR_END_RESET and CARRY_OVER transactions. Expiration: carry-over days must be used within Q1 of the new year or they expire (EXPIRY transaction). Send email reminders 30 days before expiration. Some policies: no carry-over at all (use-it-or-lose-it). Others: unlimited carry-over. Encode in LeaveType.carry_over_max_days (0 = no carry-over, NULL = unlimited).
Compliance and Reporting
Compliance: minimum leave mandates vary by jurisdiction (EU: 20 days/year minimum; US: no federal minimum). Store jurisdiction on the employee record and apply jurisdiction-specific rules. Mandatory leave: certain leave types cannot be denied (FMLA in the US, maternity/paternity in the EU). Reports: leave balance report (current balances by employee and type), leave utilization report (days taken per type, by department, by month), absenteeism report (sick leave trends), pending requests report (manager action required). All reports query the LeaveTransaction history for accuracy.
Interview Tips
- Model all balance changes as transactions, not direct balance updates. This provides a full audit trail and allows recomputing the balance from scratch if needed.
- Date arithmetic for leave: use business days (exclude weekends and public holidays) for most leave types. Store a PublicHoliday table per region. days_requested = count(business_days BETWEEN start_date AND end_date).
- State machine for requests: PENDING -> APPROVED -> TAKEN (after start_date). PENDING -> REJECTED. APPROVED -> CANCELED.
{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “How do you calculate leave days excluding weekends and public holidays?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Business days calculation: iterate from start_date to end_date, counting days that are weekdays (Monday-Friday) AND not in a public holiday table for the employee’s jurisdiction. For efficiency: precompute a set of holiday dates per region at the start of each year. Business day calculation for a range: days = sum(1 for d in daterange(start, end+1) if d.weekday() MANAGER_APPROVED -> HR_APPROVED -> APPROVED. PENDING -> REJECTED (by any approver). Approver chain: stored as a list of (approver_role, required) tuples on the LeaveType. Example: annual leave > 10 days requires manager + HR. Approval records: one row per approver (request_id, approver_id, level, action, comment, timestamp). Delegation: if a manager is on leave, route to their delegate (stored in an employee_delegation table). Escalation: if no action within 48 hours, auto-escalate to the next level and notify the manager’s manager. Email notifications at each state transition (submitted, approved at each level, finally approved or rejected).”
}
},
{
“@type”: “Question”,
“name”: “How do you handle leave requests that overlap with existing approved leaves?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Overlap detection: SELECT COUNT(*) FROM leave_requests WHERE employee_id=X AND status=APPROVED AND start_date = :new_start. If count > 0: the new request overlaps. Policy options: (1) Reject outright — employee must cancel the existing request first. (2) Allow partial overlap — reject only the overlapping days (requires splitting or adjusting dates). (3) Allow overlap for different leave types (e.g., sick leave can overlap with annual leave — rare but valid if sick during approved vacation). Implement the policy in a validation layer that runs before inserting the request. Team-level overlap: track how many team members are on leave per day. Alert manager if approving would leave the team understaffed (configurable threshold: max 2 team members simultaneously on leave).”
}
},
{
“@type”: “Question”,
“name”: “How do you handle accrual for employees who join mid-month?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Proration: an employee joining on day 15 of a 30-day month earns 50% of the monthly accrual. Calculation: days_worked_in_first_period = (month_end_date – hire_date).days + 1. Proration_factor = days_worked / days_in_month. First_accrual = accrual_rate * proration_factor. For subsequent full months: full accrual rate. Implementation: store hire_date on the employee record. The accrual job, when processing the first period for an employee, checks if hire_date falls within the period and applies proration. Idempotency: store accrual_period (YYYY-MM) on the transaction record. Before accruing: check if an ACCRUAL transaction already exists for this employee and period. If yes, skip (prevents double accrual on job reruns).”
}
},
{
“@type”: “Question”,
“name”: “How would you build leave balance forecasting for employees?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Leave balance forecast predicts the balance on a future date, accounting for scheduled accruals and pending requests. Algorithm: start with current_balance. Add expected accruals from today to target_date: accrual_per_month * months_remaining in year (accounting for partial months and carry-over cap). Subtract pending approved requests (status=APPROVED, start_date <= target_date). Result: projected_balance on target_date. Show this in the request form when an employee submits a request: "Your projected balance on your return date will be X days." Edge cases: accrual pauses (unpaid leave), year-end reset (carry-over applies), policy changes (new accrual rate mid-year applies from effective_date). Store policy changes with effective dates, not overwriting current rates."
}
}
]
}
Asked at: Atlassian Interview Guide
Asked at: LinkedIn Interview Guide
Asked at: Shopify Interview Guide
Asked at: Snap Interview Guide