# User Age Ranking

> Age brackets, stacked from top to bottom.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

Assign each user a rank based on their age bucket in descending order (oldest bucket first). Show each user's ID and their assigned rank.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests basic window function usage with `ROW_NUMBER` or `RANK`. Interviewers verify that you can rank rows by a column value without grouping.

---

### Break down the requirements

#### Step 1: Rank by age bucket descending

`ROW_NUMBER() OVER (ORDER BY age_bucket DESC)` assigns a unique rank to each user based on their age bucket, oldest first.

#### Step 2: Return user ID and rank

Select `user_id` and the computed rank column.

---

### The solution

**Window function for row-level ranking**

```sql
SELECT user_id, ROW_NUMBER() OVER (ORDER BY age_bucket DESC) AS rank
FROM users
```

> **Cost Analysis**
>
> Full scan and sort of 10M rows by `age_bucket`. With only 7 distinct buckets, a sort-based approach groups them naturally. The window function assigns sequential numbers across the sorted result.

> **Interviewers Watch For**
>
> Whether you choose `ROW_NUMBER` (unique ranks), `RANK` (gaps after ties), or `DENSE_RANK` (no gaps). The prompt says "assign a rank" without specifying tie behavior, so ROW_NUMBER is the simplest choice.

> **Common Pitfall**
>
> Assuming `age_bucket` is numeric. If it stores string labels like '18-24', '25-34', string ordering may not match the intended age order. Verify the data format.

---

## Common follow-up questions

- What if age_bucket were stored as a string like '18-24' and you needed numeric ordering? _(Tests CAST or SUBSTR to extract the lower bound for sorting.)_
- How would you rank within each account_status group? _(Add PARTITION BY account_status to the window function.)_
- What if two users have the same age_bucket and you need deterministic ranking? _(Add user_id as a tiebreaker in the ORDER BY.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/user_age_ranking)
- [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.