Implement scalable word count locally

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.

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.

Table text_chunks

column	type	constraints
chunk_id	INT	PRIMARY KEY, NOT NULL
content	TEXT	NOT NULL

Sample data:

chunk_id	content
1	Hello, hello! This is a test.
2	This test: is only a TEST.
3	Numbers 123 and 123; mixed-case HELLO.
4	Edge—cases: punctuation... and tabs and newlines.

Expected output

One row per normalized word with its total frequency across all text chunks, ordered by word ascending.

frequency	word
2	123
2	a
3	and
1	case
1	cases
1	edge
3	hello
2	is
1	mixed
1	newlines
1	numbers
1	only
1	punctuation
1	tabs
3	test
2	this

Hints

1. Use regexp_replace to convert punctuation/symbols into spaces before splitting.
2. 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.

Table text_chunks

column	type	constraints
chunk_id	INT	PRIMARY KEY, NOT NULL
content	TEXT	NOT NULL

Sample data:

chunk_id	content
1	Hello, hello! This is a test.
2	This test: is only a TEST.
3	Numbers 123 and 123; mixed-case HELLO.
4	Edge—cases: punctuation... and tabs and newlines.

Expected output

Top 3 words by frequency (descending), breaking ties by word ascending.

frequency	word
3	and
3	hello
3	test

Hints

1. Compute word counts first, then apply ORDER BY and LIMIT for top-K.
2. Include a deterministic tie-breaker (e.g., word ascending).