Description
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.
Solutions
Solution 1
sql
# Write your MySQL query statement below WITH t AS ( SELECT *, RANK() OVER ( PARTITION BY gender ORDER BY user_id ) AS rk1, CASE WHEN gender = 'female' THEN 0 WHEN gender = 'other' THEN 1 ELSE 2 END AS rk2 FROM Genders ) SELECT user_id, gender FROM t ORDER BY rk1, rk2;(code-box)
Solution 2
sql
SELECT user_id, gender FROM Genders ORDER BY ( RANK() OVER ( PARTITION BY gender ORDER BY user_id ) ), 2;(code-box)
