Razorpay: Expense Claims Not Approved by the Claimant's Manager
RazorpayX Payroll pays approved expense reimbursements with the next salary run.
- Difficulty: Medium
- Topics: Joins, Conditional Logic
- Dialect: MySQL
- Problem: #911
Problem statement
RazorpayX Payroll pays approved expense reimbursements with the next salary run. The customer's policy is that a claim may be approved only by the claimant's direct manager; the internal auditor wants every approved claim that broke the rule before the run is paid.
Return each approved claim whose decided_by is not the claimant's manager_id — that includes a claim the claimant approved themselves, one approved by a manager further up, and every approved claim of an employee with no manager on file (manager_id NULL). Pending and rejected claims are ignored.
Return claim_id, employee_name (the claimant), manager_name (their direct manager, NULL when they have none), approver_name and amount, ordered by claim_id.
A practice scenario set at Razorpay: the tables and data are invented, and this is not a question from, or endorsed by, Razorpay.
Tables
Table: Employee
| Column | Type |
|---|---|
| employee_id | int |
| full_name | varchar |
| manager_id | int |
Primary key: employee_id.
One row per employee on the customer's payroll. manager_id is the employee_id of their direct manager, NULL at the top of the org chart.
Table: Claim
| Column | Type |
|---|---|
| claim_id | int |
| employee_id | int |
| amount | int |
| status | enum(pending, approved, rejected) |
| decided_by | int |
Primary key: claim_id.
One row per reimbursement claim, amount in rupees. decided_by is the employee_id who approved or rejected it, NULL while it is pending.
Examples
Example 1
Employee
| employee_id | full_name | manager_id |
|---|---|---|
| 1 | Meera Iyer | NULL |
| 2 | Rahul Verma | 1 |
| 3 | Ananya Nair | 1 |
| 4 | Kabir Khan | 2 |
| 5 | Sneha Rao | 2 |
| 6 | Dev Joshi | 3 |
Claim
| claim_id | employee_id | amount | status | decided_by |
|---|---|---|---|---|
| 101 | 4 | 2400 | approved | 2 |
| 102 | 5 | 1800 | approved | 1 |
| 103 | 6 | 5200 | approved | 6 |
| 104 | 2 | 900 | approved | 1 |
| 105 | 1 | 15000 | approved | 2 |
| 106 | 3 | 3000 | rejected | 2 |
| 107 | 4 | 700 | pending | NULL |
| 108 | 6 | 1200 | approved | 3 |
Output
| claim_id | employee_name | manager_name | approver_name | amount |
|---|---|---|---|---|
| 102 | Sneha Rao | Rahul Verma | Meera Iyer | 1800 |
| 103 | Dev Joshi | Ananya Nair | Dev Joshi | 5200 |
| 105 | Meera Iyer | NULL | Rahul Verma | 15000 |
How to solve Razorpay: Expense Claims Not Approved by the Claimant's Manager
The rule compares two people who are both rows of Employee: the claimant's direct manager and the approver. So the table is joined to itself — once as the claimant e (from the claim's employee_id), once as the manager m (from e.manager_id) and once as the approver a (from decided_by). Only approved claims are checked, and they always have a decided_by, so the approver join can be inner.
The manager join must be a LEFT JOIN: the person at the top of the org chart has no manager, and an inner join would drop their claims — exactly the ones the policy cannot allow. The comparison has the same NULL trap: decided_by <> manager_id is unknown when manager_id is NULL, and WHERE drops unknown rows, so the condition adds e.manager_id IS NULL OR …. Self-approval (Dev Joshi's claim 103) and skip-level approval (claim 102, approved by the CFO) both fail the equality and are reported.
The first alternative uses MySQL's NULL-safe <=>, which is true only for equal values, so NOT (decided_by <=> manager_id) is true for a NULL manager, and reads the two names with scalar subqueries. The second states the rule as an anti join: report the claim unless the claimant's row names the approver as manager. All three do one primary-key lookup per joined row.
Reference solution (MySQL)
SELECT c.claim_id, e.full_name AS employee_name, m.full_name AS manager_name,
a.full_name AS approver_name, c.amount
FROM Claim c
JOIN Employee e ON e.employee_id = c.employee_id
LEFT JOIN Employee m ON m.employee_id = e.manager_id
JOIN Employee a ON a.employee_id = c.decided_by
WHERE c.status = 'approved'
AND (e.manager_id IS NULL OR c.decided_by <> e.manager_id)
ORDER BY c.claim_idAnother way
SELECT c.claim_id, e.full_name AS employee_name,
(SELECT m.full_name FROM Employee m WHERE m.employee_id = e.manager_id) AS manager_name,
(SELECT a.full_name FROM Employee a WHERE a.employee_id = c.decided_by) AS approver_name,
c.amount
FROM Claim c
JOIN Employee e ON e.employee_id = c.employee_id
WHERE c.status = 'approved' AND NOT (c.decided_by <=> e.manager_id)
ORDER BY c.claim_idAnother way
SELECT c.claim_id, e.full_name AS employee_name, m.full_name AS manager_name,
a.full_name AS approver_name, c.amount
FROM Claim c
JOIN Employee e ON e.employee_id = c.employee_id
JOIN Employee a ON a.employee_id = c.decided_by
LEFT JOIN Employee m ON m.employee_id = e.manager_id
WHERE c.status = 'approved'
AND NOT EXISTS (SELECT 1 FROM Employee x WHERE x.employee_id = c.employee_id AND x.manager_id = c.decided_by)
ORDER BY c.claim_id← Razorpay: Leading Card Network in Each Merchant Category · Razorpay: UPI Ticket Sizes in ₹1,000 Bands, Empty Bands Included →