String Character Occurrences | Dataford Interview Questions

FIS PayDirect operations teams inspect message strings when validating payment integration requests. Write a PostgreSQL query that analyzes the text associated with request 101.

Requirements

Join fis_scan_requests to fis_scan_policies and analyze only requests whose policy allows whitespace inspection.
Split the selected string into individual characters, preserving case and counting spaces as characters.
Return one row per distinct character with its occurrence count.
Order results by occurrence count descending, then by the character in ascending order for deterministic output.

Schema

fis_scan_requests

Column	Type	Description
request_idPK	INTEGER	Unique message scan request identifier
policy_id	INTEGER	Policy controlling how the message is scanned
input_text	TEXT	Message string submitted for character analysis

fis_scan_policies

Column	Type	Description
policy_idPK	INTEGER	Unique scan policy identifier
policy_name	VARCHAR(80)	Human-readable policy name
include_whitespace	BOOLEAN	Indicates whether spaces and other whitespace are included

Your question is String Character Occurrences. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem