Skip to main content
Talentd

Arrange Table by Gender - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase4 min read
Practice this problem

Problem Statement

Table: Genders

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| gender      | varchar |
+-------------+---------+
user_id is the primary key (column with unique values) for this table.
gender is ENUM (category) of type 'female', 'male', or 'other'.
Each row in this table contains the ID of a user and their gender.
The table has an equal number of 'female', 'male', and 'other'.

 

Write a solution to rearrange the Genders table such that the rows alternate between 'female', 'other', and 'male' in order. The table should be rearranged such that the IDs of each gender are sorted in ascending order.

Return the result table in the mentioned order.

The result format is shown in the following example.

 

Example 1:

Input: 
Genders table:
+---------+--------+
| user_id | gender |
+---------+--------+
| 4       | male   |
| 7       | female |
| 2       | other  |
| 5       | male   |
| 3       | female |
| 8       | male   |
| 6       | other  |
| 1       | other  |
| 9       | female |
+---------+--------+
Output: 
+---------+--------+
| user_id | gender |
+---------+--------+
| 3       | female |
| 1       | other  |
| 4       | male   |
| 7       | female |
| 2       | other  |
| 5       | male   |
| 9       | female |
| 6       | other  |
| 8       | male   |
+---------+--------+
Explanation: 
Female gender: IDs 3, 7, and 9.
Other gender: IDs 1, 2, and 6.
Male gender: IDs 4, 5, and 8.
We arrange the table alternating between 'female', 'other', and 'male'.
Note that the IDs of each gender are sorted in ascending order.

Approach Overview

Problem Overview: You are given a table where each row contains a user_id and a gender. The goal is to rearrange the rows so genders appear alternately in the result set (typically female, male, female, male...). The output must preserve the relative ordering logic while ensuring the alternating pattern.

Approach 1: Window Function with Partitioned Row Numbers (O(n log n) time, O(n) space)

This approach assigns a sequence number to each gender group using the SQL window function ROW_NUMBER(). Partition the rows by gender and order them by user_id. Each gender group now has its own incremental index. The key insight: when you sort the final result by the generated row number first and then by gender, rows from different partitions interleave naturally. For example, the first female and first male both have row_number = 1, so they appear together before the next pair. The database performs a final sort to produce the alternating pattern. This method is concise, deterministic, and widely supported in modern SQL engines such as MySQL 8+.

This technique relies on database querying concepts and especially SQL window functions. The window function isolates ranking logic within each gender group, while the final ORDER BY merges them into a single alternating sequence.

Approach 2: Conditional Ordering with Calculated Position (O(n log n) time, O(n) space)

Another way to interleave rows is by calculating a synthetic ordering position. First compute a per‑gender index using ROW_NUMBER(). Then generate a final ordering value such as 2 * row_number for one gender and 2 * row_number - 1 for the other. Sorting by this calculated value places rows in alternating slots. This approach explicitly encodes the alternating pattern in the ordering formula rather than relying on secondary sorting by gender.

The technique still depends on window functions and sorting, but it gives you precise control over which gender appears first. It also makes the alternating pattern easier to reason about when additional categories are introduced.

Recommended for interviews: The window function partitioning approach is the most common and readable SQL solution. Interviewers expect you to recognize that assigning per‑group row numbers allows the database to interleave rows naturally when sorted. Understanding SQL window functions demonstrates strong practical database skills.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Window Function with Partitioned ROW_NUMBERO(n log n)O(n)Best general solution when the database supports window functions (MySQL 8+, PostgreSQL, SQL Server)
Calculated Alternating PositionO(n log n)O(n)Useful when you want explicit control over which gender appears first in the ordering

Video Solution

LeetCode Medium 2308 "Arrange Table by Gender" Interview SQL Question with Detailed Explanation • Everyday Data Science • 2,091 views views

Frequently Asked Questions

Is Arrange Table by Gender easy or hard?
The problem is rated Medium because it requires understanding SQL window functions and how partitioned ranking interacts with sorting. Developers familiar with ROW_NUMBER() and ORDER BY patterns can implement the solution quickly.
Arrange Table by Gender Python/Java solution
This problem is primarily a SQL database challenge rather than an algorithmic coding problem. On platforms like FleetCode or LeetCode, the expected solution is written in MySQL using ROW_NUMBER() with PARTITION BY and ORDER BY to interleave genders.
How to solve Arrange Table by Gender in O(n)?
Pure O(n) ordering is generally not achievable in SQL because producing a deterministic alternating output requires sorting or ranking operations. Using ROW_NUMBER() with ORDER BY gives an O(n log n) solution, which is considered optimal for relational databases.
What is the best approach for Arrange Table by Gender?
The most practical solution uses a SQL window function. Apply ROW_NUMBER() partitioned by gender to assign an index within each group, then sort by the row number and gender. This interleaves rows from each gender group and produces the required alternating order with O(n log n) time due to sorting.
Is Arrange Table by Gender asked at Google/Amazon/Meta?
Problems involving SQL window functions and custom ordering patterns appear frequently in database interviews at companies like Amazon, Meta, and fintech startups. Variations of this problem test familiarity with ROW_NUMBER(), PARTITION BY, and sorting logic.
What data structure is used in Arrange Table by Gender?
The solution relies on SQL window functions rather than traditional data structures. ROW_NUMBER() creates a logical ranking within each gender partition, and the database query engine uses sorting internally to produce the final ordering.
What is the time complexity of Arrange Table by Gender?
The typical SQL solution runs in O(n log n) time because the database must sort the result set after computing window functions. The ROW_NUMBER() calculation itself is linear, but the final ORDER BY operation dominates the complexity.

Ready to solve this problem?

Practice Arrange Table by Gender with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary