# The Tiebreaker > One column wasn't enough. The second column settles it. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 · Asked in: Oracle ## Problem Our admin directory shows every account in `users` by its user id, email, signup date, account status and age bracket, with the status groups in alphabetical order. Within each status the oldest bracket comes first and accounts with no recorded bracket close out the group. ## Tables ### users - user_id INTEGER - username TEXT - email TEXT - signup_date TEXT - account_status TEXT - age_bucket TEXT Sample rows (10 of 200): | user_id | username | email | signup_date | account_status | age_bucket | | --- | --- | --- | --- | --- | --- | | 100 | alice | alice@example.com | 2025-02-02 | inactive | 25-34 | | 197 | aaron42 | aaron42@example.com | 2026-03-03 | suspended | 35-44 | | 294 | amelia | amelia@example.com | 2024-04-04 | pending_verification | 45-54 | | 391 | arjun | arjun@example.com | 2025-05-05 | active | 55-64 | | 488 | ava99 | ava99@example.com | 2026-06-06 | inactive | 65+ | | 585 | andrew_k | andrew_k@example.com | 2024-07-07 | suspended | NULL | | 682 | anika | anika@example.com | 2025-08-08 16:34:00 | pending_verification | 18-24 | | 779 | aiden | aiden@example.com | 2026-09-09 14:53:00 | active | 25-34 | | 876 | aria_b | aria_b@example.com | 2024-10-10 | inactive | 35-44 | | 973 | brian | brian@example.com | 2025-11-11 | suspended | 45-54 | ## Expected output First 5 of 200 rows: | user_id | email | signup_date | account_status | age_bucket | | --- | --- | --- | --- | --- | | 1167 | beatrice@example.com | 2024-01-13 | active | 65+ | | 3883 | felix@example.com | 2025-05-13 | active | 65+ | | 6599 | kira@example.com | 2026-06-13 | active | 65+ | | 488 | ava99@example.com | 2026-06-06 | inactive | 65+ | | 3204 | NULL | 2024-10-06 | inactive | 65+ | ## Worked solution and explanation ### What this one is really asking This is a multi-level ordering dressed up as an admin directory, and the levels do not share a direction. Anyone can sort by `account_status`. The catch is that four statuses cover the whole table, so status alone leaves every group in whatever order the engine returns. Write one direction for every key and you flip a level: the directory reads youngest first, or the status groups run backwards. #### Step 1: Return the directory fields The directory shows five fields, so the query names `user_id`, `email`, `signup_date`, `account_status` and `age_bucket` with no filter. Every account appears, including the 28 with no `age_bucket`. #### Step 2: Make `account_status` the outer level The groups run alphabetically, so the first key is `account_status ASC`. Position in the clause sets priority: this key decides first, and later keys only matter where it ties. #### Step 3: Give `age_bucket` its own direction Inside a group the oldest bracket leads, so the second key is `age_bucket DESC`. A direction keyword binds only to the column right before it, which is why one clause can mix `ASC` and `DESC`. `NULLS LAST` pins the unknown brackets to the end of their group. #### Step 4: Settle the last ties with `user_id` A status and a bracket together still cover several accounts: 'active' with no bracket covers six. `user_id ASC` makes the sequence a function of the data, so two correct answers can never disagree on order. **One direction per level** ```sql SELECT user_id, email, signup_date, account_status, age_bucket FROM users ORDER BY account_status ASC, age_bucket DESC NULLS LAST ``` *Groups A to Z, brackets oldest to youngest inside each, unknown brackets last, then lowest `user_id`.* **One direction everywhere** `ORDER BY account_status, age_bucket, user_id` opens every group with the unknown brackets and '18-24'. Making every key `DESC` fixes the brackets but puts 'suspended' ahead of 'active'. **One direction per key** `account_status ASC, age_bucket DESC NULLS LAST, user_id ASC` gives each key the direction the directory needs, so the groups and the sequence inside them are both right. > **Text brackets happen to sort by age** > > `age_bucket` is text, so `DESC` compares it character by character. '65+' beats '55-64', which beats '18-24', because the leading digit decides. The shortcut breaks the day a '5-17' bracket appears; then you map brackets to a number. > **Where missing ages land depends on the engine** > > SQLite and Spark put `NULL` last under `DESC`; Postgres puts it first. Writing `NULLS LAST` states what the directory needs instead of inheriting a default that changes when the query moves. > **A full sort at production scale** > > With no filter, 12 million rows mean a scan plus a sort of roughly 3 GB. An index on (`account_status`, `age_bucket` DESC, `user_id`) lets the engine read rows already in directory order and skip the sort, which matters most once the panel pages through results. ## Common follow-up questions - How would you page through this directory efficiently? _(Because the order ends on `user_id`, it is total, so keyset pagination on the three keys works instead of a growing `OFFSET`.)_ - What changes if a '5-17' bracket is added to `age_bucket`? _(Text ordering no longer matches age order. Checks whether you would map brackets to a number.)_ - What would this query return on Postgres without `NULLS LAST`? _(Tests knowing each engine's default placement of `NULL` under `DESC`.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_tiebreaker) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.