Drill 7 of 10 in Banking questions
0

Interview Question Bank · REGEXP_REPLACE · TRIM · UPPER · RANK

7. Clean merchant names, then rank by spend

MediumBankingQuestion #267

Concept: REGEXP_REPLACE · TRIM · UPPER · RANKshow

Raw text keys need cleaning before you GROUP BY them, or one entity splits into many. Strip the noise with regexp_replace(text, pattern, '', 'g'), collapse repeated spaces with regexp_replace(text, ' +', ' ', 'g'), then TRIM the ends and UPPER the case. Do the cleaning in a CTE so the grouped column and the ranking both use the same cleaned value.

Example (different data):

WITH c AS (
  SELECT UPPER(TRIM(regexp_replace(regexp_replace(city, '[0-9]+', '', 'g'), ' +', ' ', 'g'))) AS city, sales
  FROM store_reports
)
SELECT city, SUM(sales) AS sales, RANK() OVER (ORDER BY SUM(sales) DESC) AS rnk
FROM c
GROUP BY city;

Your task

Ashgrove Federal's card feed stores merchant names exactly as each terminal sends them: mixed case, stray spaces, and store numbers like '#0412'. Analysts want spend per merchant, not per checkout terminal.

Clean each raw_merchant: remove every '#' with the digits after it, collapse runs of spaces to one space, trim both ends and upper-case it. Return merchant (the cleaned name), txn_count, total_spend and spend_rank (RANK by total_spend, highest first; tied merchants share a rank and the next rank is skipped). Sort by spend_rank, then merchant.

Starting Postgres in your browser…

Checking your access…