TV Series in a Production Window
Reported by candidates from Agoda's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
Agoda reported this one in November 2025, and it looks like an API problem until you read it twice. It's really a string-parsing SQL query. You strip an optional (I) or (II) prefix, pull out a start year and an end year, then filter against a single-row window table. The tricky part is the three runtime shapes and the -1 sentinel for still-running shows. If you blank on the parsing, StealthCoder is the backup that reads the problem on screen and hands you a working query during the live OA.
The problem
Filter TV-series records by their production years. The runtime_of_series value has one of four forms: (2020-2021): the first and last production years; (2020-): still in production; (2020): produced for one year; or one of those forms preceded by (I) or (II), which must be ignored. For a finite end_year, keep shows that started in start_year or later and ended in end_year or earlier. When end_year is -1, keep shows that started in start_year or later and are still in production. Return show names in alphabetical order. The original assessment fetched every page from a supplied HTTP endpoint. The practice runner supplies the combined API records and query window directly as tables. Tables tv_series: name (Text), runtime_of_series (Text), certificate (Text), runtime_of_episodes (Text), genre (Text), imdb_rating (Decimal), overview (Text), no_of_votes (Integer), id PK (Integer) query_window: start_year (Integer), end_year (Integer) Constraints The query_window table contains exactly one row. Every runtime string follows one of the four documented forms. Show names are compared case-sensitively for output ordering.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is normalizing runtime_of_series before you compare anything. Strip a leading (I) or (II) prefix, then strip the outer parentheses. Now you have 2020-2021, 2020-, or 2020. Start year is the first four characters. End year depends on shape: no hyphen means end equals start, a trailing hyphen means open-ended, otherwise take the four digits after the hyphen. Cast both to integers in a CTE, then cross join query_window, since it has exactly one row. Branch with CASE: if window end_year is -1, require start >= start_year and an open-ended show. Otherwise require start >= start_year and a finite end <= end_year. Pitfall: the spec says a finite window only keeps shows that ended, so open-ended shows must not slip through. Another pitfall is the prefix shifting your substring positions. Finish with ORDER BY name, which is case-sensitive. StealthCoder is your hedge if the string functions slip your mind live.
Drill it cold or hedge it with StealthCoder. Either way, don't walk into the OA hoping you remember the trick.
You can drill TV Series in a Production Window cold, or you can hedge it. StealthCoder runs invisibly during screen share and surfaces a working solution in under 2 seconds. The proctor sees the IDE. They don't see what's behind it. Made for the candidate who got the OA invite this morning and has 72 hours, not six months.
Get StealthCoderRelated leaked OAs
You've seen the question.
Make sure you actually pass Agoda's OA.
Agoda reuses patterns across OAs. Made for the candidate who got the OA invite this morning and has 72 hours, not six months. Works on HackerRank, CodeSignal, CoderPad, and Karat.
TV Series in a Production Window FAQ
How hard is the Agoda TV series SQL question really?+
Easy on logic, annoying on strings. The filter itself is two comparisons. The real work is extracting years from four text formats. If you're comfortable with SUBSTRING, REPLACE, and CASE, it's a ten minute query.
What's the trick to parsing runtime_of_series?+
Normalize first. Remove the (I) or (II) prefix, then strip the parentheses so only digits and an optional hyphen remain. After that, start is the first four characters, and end is derived from whether a hyphen exists and whether digits follow it.
How do I handle shows still in production?+
A runtime like 2020- has a hyphen with nothing after it, so treat its end as open. When the window end_year is -1, keep only those open shows that started in range. When end_year is finite, exclude them, since they haven't ended.
Do I need a join with query_window?+
Yes, but it's trivial. The table has exactly one row, so a cross join or a scalar subquery works. Put the parsed start and end in a CTE, then compare them against the window columns in the WHERE clause.
How do I prepare for this in 48 hours?+
Practice string extraction in your SQL dialect: SUBSTRING, LEFT, POSITION, REPLACE, CAST. Write one query with a CASE that handles all three shapes. Test edge cases like single-year shows, open-ended shows, and the (II) prefix, then check ordering by name.