# Roll Call

> Every name on the books, in order.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

Support needs a clean roster of everyone in the customer table. List each customer's first name, last name, and country, sorted alphabetically by first name and then last name.

## Worked solution and explanation

### What this is really asking

Strip the costume and this is a single-table projection wearing a two-table schema. You are handed customers and orders and an easy-sounding ask, so the reflex is to reach for a join. But look at the keys: orders holds order_id, status, region, and profit, and there is no customer_id anywhere in it. There is nothing to join on. The skill being probed is discipline: read exactly what is asked, pull it from the one table that has it, and leave the distractor alone. Reach for orders and you either get an all-null column from a key that does not mean anything, or you fan every customer across every order.

> **The orders table is bait**
>
> Every column you were asked for (first_name, last_name, country) lives in customers. A quick test before you write anything: if a column you need is not physically in a table, that table is not part of the answer. Here, orders contributes nothing, so it never enters the query.

### Building it

#### Step 1: Start from the table that actually holds the data

All three requested fields (first_name, last_name, country) sit in customers. That single fact decides the FROM clause. No join, no subquery, no CTE.

#### Step 2: Project only the three columns asked for

Name the columns explicitly instead of SELECT star. The roster is a clean deliverable for support, so extra columns like customer_id are noise. Listing the columns also documents the exact contract of the output.

#### Step 3: Sort on two keys, in order

Sort by first_name first, then last_name. The second key is not decoration: when two people share a first name, it is the only thing that gives a stable, predictable order. Drop it and those rows come back in whatever order the engine happens to scan them.

**The roster**

```sql
SELECT first_name, last_name, country
FROM customers
ORDER BY first_name, last_name;
```

*One table, three columns, a two-key sort. Nothing more is needed.*

**The reflex (wrong)**

SELECT c.first_name, c.last_name, c.country, o.status FROM customers c LEFT JOIN orders o ON c.customer_id = o.order_id. The join key pairs a customer id against an order id, two unrelated number spaces, so status comes back null for every row and you have added a column nobody asked for.

**The answer**

SELECT first_name, last_name, country FROM customers ORDER BY first_name, last_name. Reads from the only table that carries the requested fields and returns exactly the roster that was asked for.

> **Forgetting the second sort key**
>
> Ordering by first_name alone looks correct on a tiny sample and then drifts on real data: every duplicate first name lands in an arbitrary order that can change between runs. The last_name tie-breaker is what makes the output deterministic.

> **The tell**
>
> A strong candidate looks at both tables, notices orders has no customer_id, and says so out loud before writing a line. Naming the missing key and then deliberately not joining signals someone who reads a schema instead of pattern-matching two tables into a join.

## Common follow-up questions

- If orders did carry a customer_id, how would you attach each customer's most recent order status while still listing customers who have never ordered? _(Tests whether they reach for a LEFT JOIN plus a per-customer latest-row pick, and understand why an inner join would silently drop order-less customers.)_
- Suppose country can be null and you want those customers listed last. How does the sort change? _(Tests awareness of null ordering semantics and how to force nulls to the end explicitly.)_

## Related

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