Arrange Table by Gender - Practice Coding Problems

Arrange Table by Gender - Problem

Database Medium

You're working with a Genders database table that contains user information including their gender identity. This table has an interesting property: it contains exactly equal numbers of users with genders 'female', 'male', and 'other'.

Your mission: Rearrange the table rows to create a specific alternating pattern where the genders cycle through 'female' → 'other' → 'male' repeatedly. Additionally, within each gender group, the user_id values should be sorted in ascending order.

Column Name	Type
user_id	int
gender	varchar

Example: If you have 6 users (2 female, 2 male, 2 other), your result should show: Female(smallest ID) → Other(smallest ID) → Male(smallest ID) → Female(largest ID) → Other(largest ID) → Male(largest ID)

This creates a beautiful, organized pattern that's both gender-balanced and ID-ordered! 🎯

Input & Output

example_1.sql — Basic Example

$ Input: Genders table: +--------+--------+ | user_id| gender | +--------+--------+ | 1 | female | | 2 | other | | 3 | male | | 4 | male | | 5 | other | | 6 | male | | 7 | female | | 8 | other | | 9 | female | +--------+--------+

› Output: +--------+--------+ | user_id| gender | +--------+--------+ | 1 | female | | 2 | other | | 3 | male | | 7 | female | | 5 | other | | 4 | male | | 9 | female | | 8 | other | | 6 | male | +--------+--------+

💡 Note: The pattern alternates female → other → male. Within each gender, user_ids are in ascending order: females (1,7,9), others (2,5,8), males (3,4,6). The result shows this perfect alternating pattern.

example_2.sql — Minimum Case

$ Input: Genders table: +--------+--------+ | user_id| gender | +--------+--------+ | 10 | male | | 5 | female | | 15 | other | +--------+--------+

› Output: +--------+--------+ | user_id| gender | +--------+--------+ | 5 | female | | 15 | other | | 10 | male | +--------+--------+

💡 Note: With exactly one user per gender, the result simply follows the female → other → male pattern with the single user from each group.

example_3.sql — Large IDs

$ Input: Genders table: +--------+--------+ | user_id| gender | +--------+--------+ | 1000 | other | | 999 | female | | 1001 | male | | 1002 | female | | 1003 | other | | 1004 | male | +--------+--------+

› Output: +--------+--------+ | user_id| gender | +--------+--------+ | 999 | female | | 1000 | other | | 1001 | male | | 1002 | female | | 1003 | other | | 1004 | male | +--------+--------+

💡 Note: Even with large user_ids, the algorithm works correctly. Females (999, 1002) and others (1000, 1003) and males (1001, 1004) are each sorted by ID, then alternated in the female → other → male pattern.

Visualization

Tap to expand

Understanding the Visualization

Group by Dance Style

Separate dancers into their respective groups: Ballet (female), Hip-hop (other), and Ballroom (male)

Rank by Experience

Within each dance style, rank dancers by their experience level (user_id) using ROW_NUMBER()

Calculate Position Formula

Assign final positions: Ballet gets (rank-1)*3+1, Hip-hop gets (rank-1)*3+2, Ballroom gets (rank-1)*3+3

Arrange Formation

Combine all groups and sort by calculated position to create the alternating Ballet → Hip-hop → Ballroom pattern

Key Takeaway

🎯 Key Insight: The ROW_NUMBER() window function combined with calculated positioning formulas creates an elegant single-query solution that perfectly alternates genders while maintaining sorted order within each group.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

ROW_NUMBER() requires sorting within each gender group, and final ORDER BY - overall O(n log n)

⚡ Linearithmic

Space Complexity

O(n)

Result set storage and temporary space for window function processing

⚡ Linearithmic Space

Constraints

The table contains equal numbers of 'female', 'male', and 'other' genders
1 ≤ user_id ≤ 10⁶
3 ≤ total rows ≤ 10⁴ (and divisible by 3)
Gender values are exactly 'female', 'male', 'other'
user_id is unique (primary key constraint)

Asked in

f Facebook 25 G Google 20 a Amazon 18 ⊞ Microsoft 15

The optimal solution uses a single SQL query with ROW_NUMBER() window function to assign sequential positions within each gender group, then creates calculated sort orders: (ROW_NUMBER() - 1) * 3 + position where position is 1 for female, 2 for other, 3 for male. The three subqueries are combined with UNION ALL and sorted by the calculated order to create the perfect alternating pattern.

Common Approaches

Approach	Time	Space	Notes
✓ Single Query with ROW_NUMBER (Optimal)	O(n log n)	O(n)	Use ROW_NUMBER() window function to assign positions and create alternating pattern
Brute Force (Multiple Queries)	O(n log n)	O(n)	Separate queries for each gender, then manually combine results

Single Query with ROW_NUMBER (Optimal) — Algorithm Steps

Use ROW_NUMBER() to assign positions within each gender group
Create three subqueries, one for each gender with calculated sort order
For females: sort_order = (row_number - 1) * 3 + 1
For others: sort_order = (row_number - 1) * 3 + 2
For males: sort_order = (row_number - 1) * 3 + 3
UNION ALL the three subqueries and ORDER BY the calculated sort_order

Visualization

Tap to expand

Step-by-Step Walkthrough

Assign Row Numbers

ROW_NUMBER() assigns sequential positions within each gender group

Calculate Sort Order

Each gender gets a formula: female=(rn-1)*3+1, other=(rn-1)*3+2, male=(rn-1)*3+3

Union All

UNION ALL combines the three subqueries into one result set

Final Sort

ORDER BY sort_order creates the perfect alternating pattern

Code -

solution.c — C

// Optimal SQL solution using ROW_NUMBER window function
#include <stdio.h>
#include <string.h>

char* solution() {
    static char query[] = 
        "SELECT user_id, gender "
        "FROM ( "
        "    SELECT user_id, gender, "
        "           (ROW_NUMBER() OVER (ORDER BY user_id) - 1) * 3 + 1 as sort_order "
        "    FROM Genders "
        "    WHERE gender = 'female' "
        "    UNION ALL "
        "    SELECT user_id, gender, "
        "           (ROW_NUMBER() OVER (ORDER BY user_id) - 1) * 3 + 2 as sort_order "
        "    FROM Genders "
        "    WHERE gender = 'other' "
        "    UNION ALL "
        "    SELECT user_id, gender, "
        "           (ROW_NUMBER() OVER (ORDER BY user_id) - 1) * 3 + 3 as sort_order "
        "    FROM Genders "
        "    WHERE gender = 'male' "
        ") arranged "
        "ORDER BY sort_order";
    
    return query;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

ROW_NUMBER() requires sorting within each gender group, and final ORDER BY - overall O(n log n)

⚡ Linearithmic

Space Complexity

O(n)

Result set storage and temporary space for window function processing

⚡ Linearithmic Space

Constraints

The table contains equal numbers of 'female', 'male', and 'other' genders
1 ≤ user_id ≤ 10⁶
3 ≤ total rows ≤ 10⁴ (and divisible by 3)
Gender values are exactly 'female', 'male', 'other'
user_id is unique (primary key constraint)

28.5K Views

Medium Frequency

~15 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Single Query with ROW_NUMBER (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler