LeetCode Hard SQL | Dataford Interview Questions

Your question is LeetCode Hard SQL. 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

KPIT's engineering knowledge search surface stores indexed text and a required set of equal-length keywords. Write a PostgreSQL query that finds every zero-based character position where the text contains all required keywords exactly once, in any order and including duplicate keyword requirements.

Requirements

Return case_id and the zero-based start_position for every valid match.
Treat the required keywords as a multiset, so duplicate words must occur the required number of times.
Require the matching substring to contain exactly word_count * word_length characters.
Return results ordered by case_id and start_position.
Ignore cases with no usable keyword rows or a NULL search text.

All keywords within a case have the same length.

Schema

search_cases

Column	Type	Description
case_idPK	INT	Search case identifier
search_text	TEXT	Text scanned for concatenated keywords
case_label	VARCHAR(80)	Short description of the search case

required_words

Column	Type	Description
word_idPK	INT	Required-word identifier
case_id	INT	References search_cases.case_id
word_position	INT	Position in the input requirement list
word_text	VARCHAR(40)	Keyword that must occur in the candidate substring