Implement scalable word count locally
Company: Adobe
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Write a function that reads a very large text file and outputs the frequency of each word. Define your tokenization and normalization rules (case folding, punctuation, Unicode handling), and explain how to process inputs larger than RAM using streaming, chunking, or external sorting. Discuss producing the top‑K most frequent words efficiently and analyze time and space complexity.
Overview: This question evaluates understanding of scalable word-count processing, including tokenization and normalization rules, streaming or external-memory techniques for inputs larger than RAM, and efficient top‑K frequency computation.
Read the full Adobe Software Engineer interview experience this question came from
Word frequency count with normalization and tokenization
You are given a table that stores chunks of text from a very large file (the file has already been split into rows so it can be processed in a streaming/chunked manner).
Write a SQL query (PostgreSQL) that outputs the frequency of each word across all rows using the following rules:
1) Case folding: convert all text to lowercase.
2) Normalization: replace any sequence of characters that is NOT an ASCII letter (a-z) or digit (0-9) with a single space.
3) Tokenization: split on whitespace into words.
4) Ignore empty tokens.
Return one row per word with columns: word, frequency. Order results by word ascending.
Tables
text_chunks(chunk_id INT, content TEXT)
Hints
- Use regexp_replace to convert punctuation/symbols into spaces before splitting.
- In PostgreSQL, regexp_split_to_table is a convenient way to turn a string into multiple rows.
Top-K most frequent words
Using the same tokenization/normalization rules as in Question 1 (lowercase, non-[a-z0-9] -> space, split on whitespace, ignore empty tokens), write a SQL query (PostgreSQL) that returns the top 3 most frequent words.
Output columns: word, frequency.
Sort by frequency descending, then word ascending to break ties.
Tables
text_chunks(chunk_id INT, content TEXT)
Hints
- Compute word counts first, then apply ORDER BY and LIMIT for top-K.
- Include a deterministic tie-breaker (e.g., word ascending).