# The Tiebreaker

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

Canonical URL: <https://datadriven.io/problems/multi_column_user_sort>

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The admin panel needs a sortable user directory alphabetical by username, with ties broken by age bucket in reverse. Return all user profile fields.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests whether you can translate a natural-language sort specification into a correct `ORDER BY` clause with mixed directions. Interviewers use it to confirm you understand that `ASC` and `DESC` can be combined in a single sort and that the column order in `ORDER BY` determines tie-breaking priority.

---

### Break down the requirements

#### Step 1: Select all profile fields

Use `SELECT *` or explicitly name all columns from the `users` table. The prompt says "return all user profile fields," so nothing should be omitted.

#### Step 2: Apply compound ORDER BY

Sort by `username ASC` first, then `age_bucket DESC` for the tiebreaker. The column order in `ORDER BY` defines the priority: primary sort on username, secondary on age bucket in reverse.

---

### The solution

**Compound sort with mixed direction**

```sql
SELECT *
FROM users
ORDER BY username ASC, age_bucket DESC
```

> **Cost Analysis**
>
> With `users` (12,000,000 rows), the full scan reads significant data. A composite index on the filter columns pushes the predicate into the index layer. Pre-aggregation in a materialized view is worth considering at this scale.

> **Interviewers Watch For**
>
> Interviewers watch whether you specify `ASC`/`DESC` explicitly for each column rather than relying on defaults. They also check whether you place the tiebreaker column second, not first.

> **Common Pitfall**
>
> Reversing the column order in `ORDER BY` (sorting by `age_bucket` first, then `username`) changes the entire output. The primary sort must match the prompt's "alphabetical by username" requirement.

---

## Common follow-up questions

- What happens if the primary filter column contains NULL values? _(Tests NULL awareness in WHERE predicates.)_
- How would you add pagination to this result set? _(Tests OFFSET/LIMIT mechanics and cursor-based alternatives.)_
- What index would you create to speed up this query? _(Tests basic indexing knowledge for the dominant filter/sort column.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/multi_column_user_sort)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.