User Age Ranking

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
Airbnb

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.

Age brackets, stacked from top to bottom.

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_idrank
4881
11671
3912
2943
1974

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

Prose asks: rank each user by their age bucket, oldest bucket first, output user_id and rank. A 'rank based on age bucket' means users sharing a bucket share a rank, so a ranking function over age_bucket (not a per-row ROW_NUMBER) is required.

Step 2: Return user ID and rank

The users.age_bucket values are text labels '18-24','25-34','35-44','45-54','55-64','65+' (plus some NULL). Their first digit increases monotonically with age, so plain lexical ordering equals age ordering; ORDER BY age_bucket DESC puts '65+' first and '18-24' last, exactly 'oldest bucket first'.

The solution

Window function for row-level ranking
SELECT user_id,
       DENSE_RANK() OVER (ORDER BY age_bucket DESC) AS rank
FROM users
ORDER BY rank, user_id;

Common follow-up questions