Fetch and aggregate paginated team data via API
Company: Reevo
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Online Assessment
Implement a function getTopTeams(baseUrl, n) that calls a paginated HTTP API returning a JSON array of team objects on each page: [{"name": string, "wins": integer}]. Pagination is controlled by appending &page=<number> to baseUrl (pages start at 1 and continue until an empty or missing page is encountered). Retrieve and merge all pages, sort teams by wins descending then name ascending, and return the top n team names. Handle pagination, transient network errors (with retries/backoff), rate limits, and malformed records.
Overview: This question evaluates API integration, pagination handling, robust error handling (retries/backoff and rate-limit awareness), data aggregation, sorting and top‑N selection skills within the Data Manipulation (SQL/Python) domain.
You are given a table team_pages that stores the merged results of calling a paginated HTTP API. Each row represents a team returned on a particular page of the API response.
The table includes some malformed records:
- Rows where team_name IS NULL
- Rows where wins IS NULL
- Rows where wins is negative (wins < 0)
Using this table, write a SQL query that returns the top 3 team names based on these rules:
1. Consider only valid records (exclude malformed rows as defined above).
2. Sort teams by wins in descending order.
3. For teams with the same number of wins, sort by team_name in ascending (alphabetical) order.
4. Return only the top 3 team names.
Your output should be a single column named team_name containing the names of the top 3 teams in the correct order.
Tables
team_pages(page_number INT, team_name VARCHAR(100), wins INT)
Hints
- Filter out malformed rows using conditions on team_name and wins in the WHERE clause.
- Order by wins DESC and team_name ASC, then limit the number of rows to 3.