Premier League Table Ranking - Solution & Explanation

Problem Overview: You are given match or standings data for Premier League teams and need to produce the final league table ranking. Teams must be ordered by competition rules (typically points first, then tie‑breakers such as goal difference or goals scored) and assigned a ranking position.

Approach 1: Window Function Ranking (SQL) (Time: O(n log n), Space: O(n))

The most direct solution uses a SQL window function such as RANK() or DENSE_RANK(). First sort teams by the required ordering criteria (for example points DESC, goal_difference DESC, goals_scored DESC). The window function then assigns a rank based on this ordering. Internally the database performs a sort before computing the window frame, giving roughly O(n log n) time complexity with O(n) space for intermediate results. This approach is concise and idiomatic for relational queries, which is why it appears frequently in database interview questions.

The key insight is that ranking logic should not be implemented with manual joins or self comparisons. A window function already provides row ordering and rank assignment in a single pass after sorting. For example:

RANK() OVER (ORDER BY points DESC, goal_diff DESC)

This computes the league position exactly as a scoreboard would. If multiple teams share the same statistics, RANK() gives them the same position while skipping the next rank number. When sequential ranks are required, DENSE_RANK() is the better option.

Approach 2: Pandas DataFrame Ranking (Time: O(n log n), Space: O(n))

In the Pandas version, the idea is identical but implemented with DataFrame operations. First call sort_values() on the ranking columns (points, goal difference, etc.). After sorting, assign the league position using rank() or by resetting the index. Sorting dominates the runtime, resulting in O(n log n) time complexity and O(n) extra memory for the reordered DataFrame.

Pandas is useful when the dataset originates from analytics pipelines rather than a relational database. You manipulate the table in memory and compute rankings with vectorized operations. The conceptual mapping between SQL window functions and Pandas ranking functions is almost one‑to‑one, making it easy to switch between environments.

Both approaches rely on the same core idea: order the table correctly, then assign a rank based on that order. This pattern appears frequently in analytics queries involving leaderboards, competition standings, or scoreboards. Understanding window functions such as RANK(), DENSE_RANK(), and ROW_NUMBER() is essential for solving ranking problems in SQL and advanced window function queries.

Recommended for interviews: The window function approach is what interviewers expect. It shows you understand modern SQL features and can avoid inefficient self‑joins or nested queries. Explaining how sorting defines ranking and why RANK() vs DENSE_RANK() matters demonstrates deeper database query knowledge.