Arrange Table by Gender - Problem

You are given a table Genders with the following structure:

Column NameType
user_idint
gendervarchar

The user_id is the primary key for this table. The gender column contains values 'female', 'male', or 'other'. The table has an equal number of rows for each gender type.

Task: Rearrange the table so that rows alternate between 'female', 'other', and 'male' in that exact order. Within each gender group, the user_id values should be sorted in ascending order.

The final result should show the pattern: female, other, male, female, other, male, and so on.

Table Schema

Genders
Column Name Type Description
user_id PK int Unique identifier for each user
gender varchar Gender type: 'female', 'male', or 'other'
Primary Key: user_id
Note: Contains equal number of rows for each gender type

Input & Output

Example 1 — Basic Alternating Pattern
Input Table:
user_id gender
1 male
2 other
3 female
4 female
5 other
6 male
Output:
user_id gender
3 female
2 other
1 male
4 female
5 other
6 male
💡 Note:

The table is rearranged to alternate between female, other, and male. Within each gender group, users are ordered by user_id ascending. The first occurrence of each gender (user_id 3=female, 2=other, 1=male) comes first, followed by the second occurrence of each gender.
Example 2 — Sequential User IDs
Input Table:
user_id gender
1 female
2 male
3 other
4 female
5 male
6 other
Output:
user_id gender
1 female
3 other
2 male
4 female
6 other
5 male
💡 Note:

Even with sequential user IDs, the alternating pattern is maintained. Female users (1,4), other users (3,6), and male users (2,5) are arranged in the repeating pattern of female-other-male.

Constraints

  • 1 ≤ user_id ≤ 1000
  • gender is one of 'female', 'male', or 'other'
  • The table contains an equal number of 'female', 'male', and 'other' rows

Visualization

Tap to expand
Arrange Table by Gender INPUT: Genders Table user_id gender 4 male 7 female 2 other 1 male 3 female 5 other female other male Unsorted, mixed genders Equal count per gender ALGORITHM STEPS 1 Separate by Gender Group rows into 3 lists: females, others, males 2 Sort Each Group Sort each list by user_id in ascending order 3 Assign Row Numbers ROW_NUMBER() for each gender partition 4 Interleave Results ORDER BY row_num, then gender priority (F,O,M) F: 3,7 O: 2,5 M: 1,4 Sorted queues ready to merge FINAL RESULT user_id gender 3 female 2 other 1 male 7 female 5 other 4 male Pattern: F-O-M-F-O-M Alternating genders Sorted by user_id within each gender group OK - Complete Key Insight: Use ROW_NUMBER() with PARTITION BY gender to assign sequence numbers within each gender group. Then ORDER BY this row number first, and a CASE expression for gender priority (female=1, other=2, male=3). This creates the perfect interleaving pattern while maintaining user_id order within each gender. TutorialsPoint - Arrange Table by Gender | Optimal Solution

Related Problems

Asked in
Amazon 15 Microsoft 12 Google 8
24.5K Views
Medium Frequency
~12 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen