Salary Credits by Employer From Statement Narrations

Medium
61.

Salary Credits by Employer From Statement Narrations

The bank wants to know which employers pay salaries into its accounts, but the only trace is the statement narration. A salary credit's narration has the form <RAIL>/SALARY/<EMPLOYER>/<PERIOD>, for example NEFT/SALARY/INFOSYS/OCT24; all other credits use other words in the second segment (P2P, REFUND, SALARYADV, …).

For every employer, return employer, salaried_accounts (the number of distinct accounts it paid) and total_salary (the sum of its salary credits). Order the rows by total_salary descending, then by employer ascending.

Tables

Table: StatementLine

ColumnType
line_idint
account_idint
narrationvarchar
amountint
value_datedate

Primary key: line_id.

One row per credit on a statement. Narrations are always upper case with / between segments.

Example

The rows must come back in the order shown.

StatementLine

line_idaccount_idnarrationamountvalue_date
110NEFT/SALARY/INFOSYS/OCT24850002024-10-31
211NEFT/SALARY/INFOSYS/OCT24620002024-10-31
310UPI/P2P/RAHUL9/DINNER12002024-11-02
412IMPS/SALARY/ZOMATO/OCT24540002024-11-01
510NEFT/SALARY/INFOSYS/NOV24850002024-11-30
613IMPS/SALARYADV/KREDITBEE/LOAN150002024-11-05
714RTGS/SALARY/TCS/NOV241470002024-11-29
812NEFT/REFUND/AMAZON/ORD77123992024-11-12

Output

employersalaried_accountstotal_salary
INFOSYS2232000
TCS1147000
ZOMATO154000
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.

Salary Credits by Employer From Statement Narrations

The bank wants to know which employers pay salaries into its accounts, but the only trace is the statement narration.

  • Difficulty: Medium
  • Topics: Strings, Aggregation
  • Dialect: MySQL
  • Problem: #61

Problem statement

The bank wants to know which employers pay salaries into its accounts, but the only trace is the statement narration. A salary credit's narration has the form <RAIL>/SALARY/<EMPLOYER>/<PERIOD>, for example NEFT/SALARY/INFOSYS/OCT24; all other credits use other words in the second segment (P2P, REFUND, SALARYADV, …).

For every employer, return employer, salaried_accounts (the number of distinct accounts it paid) and total_salary (the sum of its salary credits). Order the rows by total_salary descending, then by employer ascending.

Tables

Table: StatementLine

ColumnType
line_idint
account_idint
narrationvarchar
amountint
value_datedate

Primary key: line_id.

One row per credit on a statement. Narrations are always upper case with / between segments.

Examples

Example 1

StatementLine

line_idaccount_idnarrationamountvalue_date
110NEFT/SALARY/INFOSYS/OCT24850002024-10-31
211NEFT/SALARY/INFOSYS/OCT24620002024-10-31
310UPI/P2P/RAHUL9/DINNER12002024-11-02
412IMPS/SALARY/ZOMATO/OCT24540002024-11-01
510NEFT/SALARY/INFOSYS/NOV24850002024-11-30
613IMPS/SALARYADV/KREDITBEE/LOAN150002024-11-05
714RTGS/SALARY/TCS/NOV241470002024-11-29
812NEFT/REFUND/AMAZON/ORD77123992024-11-12

Output

employersalaried_accountstotal_salary
INFOSYS2232000
TCS1147000
ZOMATO154000

How to solve Salary Credits by Employer From Statement Narrations

The data lives inside a string, so the job is parsing. SUBSTRING_INDEX(narration, '/', 3) keeps the first three segments (NEFT/SALARY/INFOSYS), and wrapping it in SUBSTRING_INDEX(…, '/', -1) keeps the last of those — the third segment, the employer. The same trick with 2 extracts the second segment, which must be exactly SALARY.

Testing the whole segment matters: narration LIKE '%SALARY%' would also catch SALARYADV, a salary-advance loan credit, and inflate the totals. LIKE '%/SALARY/%' is safe because the slashes pin the segment's ends — the alternative uses that, and then takes the text right after /SALARY/ up to the next slash.

Group by the extracted employer, count distinct accounts (an employee paid in October and November is one account) and sum the amounts. The order is by total with the employer name breaking ties. The cost is one scan with string functions per row; a real system would parse the narration once at load time into its own column.

Reference solution (MySQL)

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(narration, '/', 3), '/', -1) AS employer,
       COUNT(DISTINCT account_id) AS salaried_accounts,
       SUM(amount) AS total_salary
FROM StatementLine
WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(narration, '/', 2), '/', -1) = 'SALARY'
GROUP BY SUBSTRING_INDEX(SUBSTRING_INDEX(narration, '/', 3), '/', -1)
ORDER BY total_salary DESC, employer

Another way

SELECT employer, COUNT(DISTINCT account_id) AS salaried_accounts, SUM(amount) AS total_salary FROM (
  SELECT account_id, amount, SUBSTRING_INDEX(SUBSTRING_INDEX(narration, '/SALARY/', -1), '/', 1) AS employer
  FROM StatementLine WHERE narration LIKE '%/SALARY/%'
) s GROUP BY employer ORDER BY total_salary DESC, employer

← Credit Cards Over 30% Utilisation in September · Branch Deposits Against the Previous Calendar Month →