Razorpay: Expense Claims Not Approved by the Claimant's Manager

Medium
911.

Razorpay: Expense Claims Not Approved by the Claimant's Manager

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

ColumnType
employee_idint
full_namevarchar
manager_idint

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

ColumnTypeValues
claim_idint
employee_idint
amountint
statusenumpending, approved, rejected
decided_byint

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.

Example

The rows must come back in the order shown.

Employee

employee_idfull_namemanager_id
1Meera IyerNULL
2Rahul Verma1
3Ananya Nair1
4Kabir Khan2
5Sneha Rao2
6Dev Joshi3

Claim

claim_idemployee_idamountstatusdecided_by
10142400approved2
10251800approved1
10365200approved6
1042900approved1
105115000approved2
10633000rejected2
1074700pendingNULL
10861200approved3

Output

claim_idemployee_namemanager_nameapprover_nameamount
102Sneha RaoRahul VermaMeera Iyer1800
103Dev JoshiAnanya NairDev Joshi5200
105Meera IyerNULLRahul Verma15000
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
employee_idint
full_namevarchar
manager_idint

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

ColumnType
claim_idint
employee_idint
amountint
statusenum(pending, approved, rejected)
decided_byint

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_idfull_namemanager_id
1Meera IyerNULL
2Rahul Verma1
3Ananya Nair1
4Kabir Khan2
5Sneha Rao2
6Dev Joshi3

Claim

claim_idemployee_idamountstatusdecided_by
10142400approved2
10251800approved1
10365200approved6
1042900approved1
105115000approved2
10633000rejected2
1074700pendingNULL
10861200approved3

Output

claim_idemployee_namemanager_nameapprover_nameamount
102Sneha RaoRahul VermaMeera Iyer1800
103Dev JoshiAnanya NairDev Joshi5200
105Meera IyerNULLRahul Verma15000

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_id

Another 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_id

Another 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 →