SQL: Character Count | Dataford Interview Questions

Discord analytics needs to validate how often a selected character appears in a message. Write a PostgreSQL query that counts the target character in the request with request_id = 1.

The comparison must be case-insensitive, so both uppercase and lowercase occurrences count. Assume target_character contains exactly one character.

Requirements

Return one column named character_count.
Count every case-insensitive occurrence of target_character in message_text.
Restrict the result to the row where request_id = 1.

Schema

character_count_requests

Column	Type	Description
request_idPK	INTEGER	Unique character-count request identifier
message_text	TEXT	Discord message whose characters are inspected
target_character	VARCHAR(1)	Single character to count case-insensitively

Your question is SQL: Character Count. Start with the requirements and the one table 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