# Find Strictly Decreasing Forecast Windows for Equatorial Cities You have a PostgreSQL `cities` table with a city name, latitude, and longitude, plus a weather API that returns hourly forecasts. Produce a report for every city whose latitude is strictly between -1 and 1 degrees. Fetch the next three days of hourly forecasts and list every contiguous eight-hour window in which each temperature is strictly lower than the preceding temperature. Overlapping windows count. If a city has no qualifying window, report `None Found` for that city. ### Constraints & Assumptions - Preserve forecast hours in chronological order, including transitions between forecast days. - Treat equal adjacent temperatures as a break in a decreasing run. - Assume the API can fail, time out, return malformed data, or omit some hours; explain how the report should handle those cases. - Do not interpolate missing hours silently. A reported window must represent eight consecutive hourly timestamps. - Database and API credentials must not be embedded in source code. ### Clarifying Questions to Ask - Is the latitude interval open or closed at -1 and 1? - Should a failure for one city abort the report or be recorded while processing continues? - Which temperature unit and timezone should the report use? - What rate limits and retry policy does the weather API impose? ### Part 1: Retrieve and Normalize the Data Describe the SQL query, API request flow, validation, and normalized in-memory representation you would use. #### What This Part Should Cover - A narrow query that selects only the required city fields and latitude range. - Safe credential handling, request timeouts, status checking, and bounded retries. - Chronological flattening of hourly records with explicit timestamps and temperatures. - Detection of duplicate, missing, or out-of-order hours. ### Part 2: Find and Report the Windows Give an algorithm for finding all qualifying windows and explain its time and space complexity. #### What This Part Should Cover - Correct strict comparisons across all seven adjacent pairs in each eight-hour window. - Support for overlapping windows and day-boundary windows. - A report entry for every selected city, including `None Found` when appropriate. - Complexity in terms of cities and hourly readings. ### Part 3: Make the Job Operationally Safe Explain how you would test and operate this as a repeatable reporting job. #### What This Part Should Cover - Unit tests for boundary latitudes, equal values, short input, gaps, and overlapping windows. - Integration tests with deterministic API fixtures and database rows. - Rate limiting, observability, partial-failure reporting, and idempotent output. ### What a Strong Answer Covers - A clean separation between database access, API access, normalization, analysis, and presentation. - Correctness around timestamp continuity and strictly decreasing comparisons. - Parameterized SQL, protected secrets, defensive HTTP handling, and actionable failure records. - An `O(C * H)` analysis for `C` cities and `H` hourly records per city, without unnecessary rescanning. ### Follow-up Questions - How would you reduce weather API calls if many cities share nearby coordinates? - How would you stream results if the city table were too large to load at once? - How would the algorithm change if the window length were configurable?