Word Frequency SQL Query | Dataford Interview Questions

Your question is Word Frequency SQL Query. Start with the requirements and the two tables on the right.

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

Problem

Intuit's TurboTax content pipeline stores uploaded text files as individual tokens for quality checks. Write a PostgreSQL query that reports how many times each word appears in turbotax-help.txt.

Requirements

Join the file metadata to its token rows using document_id.
Normalize each token by converting it to lowercase and removing punctuation.
Ignore NULL tokens and tokens that become empty after normalization.
Return each normalized word and its occurrence count, ordered by count descending and then word ascending.

Schema

documents

Column	Type	Description
document_idPK	INT	Unique file identifier
file_name	VARCHAR(255)	Uploaded file name
product_name	VARCHAR(100)	Intuit product associated with the file
uploaded_at	DATE	Date the file was uploaded

word_tokens

Column	Type	Description
token_idPK	INT	Unique token identifier
document_id	INT	References documents.document_id
token_position	INT	Token position within the document
token	VARCHAR(255)	Raw extracted token