The Tiebreaker

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

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.

One column wasn't enough. The second column settles it.

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

users: 10 of 200 rows
user_idusernameemailsignup_dateaccount_statusage_bucket
100alicealice@example.com2025-02-02inactive25-34
197aaron42aaron42@example.com2026-03-03suspended35-44
294 ameliaamelia@example.com2024-04-04pending_verification45-54
391arjun arjun@example.com2025-05-05active55-64
488ava99ava99@example.com2026-06-06inactive65+
585andrew_kandrew_k@example.com2024-07-07suspendedNULL
682anikaanika@example.com2025-08-08 16:34:00pending_verification18-24
779aidenaiden@example.com2026-09-09 14:53:00active25-34
876aria_baria_b@example.com2024-10-10inactive35-44
973brianbrian@example.com2025-11-11suspended45-54

Expected output

First 5 of 200 rows
user_idemailsignup_dateaccount_statusage_bucket
1167beatrice@example.com2024-01-13active65+
3883felix@example.com2025-05-13active65+
6599kira@example.com2026-06-13active65+
488ava99@example.com2026-06-06inactive65+
3204NULL2024-10-06inactive65+

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
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.

Common follow-up questions