This is a common SQL (Aggregation) interview and practice question. Below is the full answer with a clear, step-by-step explanation.

Question

Problem You are given a table containing all customer orders. Orders order_id customer_id order_date amount 1 101 2024-01-10 120 2 101 2024-02-15 180 3 101 2024-04-05 250 4 102 2024-01-20 300 5 102 2024-03-18 400 6 103 2024-02-01 150 7 104 2024-01-11 220 8 104 2024-01-18 180 9 104 2024-02-22 300 10 104 2024-05-10 250 Task Customer Lifetime Value (CLV) is the total revenue generated by a customer. Write a SQL query to rank customers based on their lifetime value. If two customers have the same lifetime value, rank the customer with the earlier first purchase higher. Return Columns customer_id lifetime_value first_purchase customer_rank

Answer & Explanation

**What the question is asking**

You have a table of every order. For each customer you need three things:

1. **Lifetime value** – the sum of all amounts they have spent.
2. **First purchase date** – the earliest order_date for that customer.
3. **A rank** – customers with higher lifetime value get a better (lower) rank.
If two customers have exactly the same lifetime value, the one whose first purchase happened earlier should be ranked higher.

The result set must contain the columns

`customer_id , lifetime_value , first_purchase , customer_rank`.

---

**Step‑by‑step reasoning**

1. **Group the data by customer**
The raw table has one row per order. To get a single row per customer you must aggregate:
* `SUM(amount)` gives the total revenue (lifetime value).
* `MIN(order_date)` gives the earliest order date (first purchase).

The `GROUP BY customer_id` clause produces one row per customer with those two calculated columns.

2. **Create a derived table (or CTE) with the aggregated values**
It is easier to apply the ranking function to a clean set that already contains the two metrics you need.
Using a Common Table Expression (CTE) called `customer_summary` keeps the query readable.

3. **Apply a ranking window function**
* `DENSE_RANK()` is the right choice because it gives the same rank to ties but does not leave gaps in the sequence (1,2,2,3 …).
* The `ORDER BY` clause inside the window defines the ranking order:
- `lifetime_value DESC` – higher revenue first.
- `first_purchase ASC` – for equal revenue, the earlier date comes first.

The expression `DENSE_RANK() OVER (ORDER BY lifetime_value DESC, first_purchase ASC)` therefore produces exactly the ranking rule described.

4. **Select the final columns and order the output**
The outer SELECT pulls the four columns you need and orders the result by the rank (or any other column you prefer for presentation).

---

**The complete query**

```
WITH customer_summary AS (
SELECT
customer_id,
SUM(amount) AS lifetime_value,
MIN(order_date) AS first_purchase
FROM Orders
GROUP BY customer_id
)
SELECT
customer_id,
lifetime_value,
first_purchase,
DENSE_RANK() OVER (
ORDER BY lifetime_value DESC,
first_purchase ASC
) AS customer_rank
FROM customer_summary
ORDER BY customer_rank;
```

---

**Why this query is correct**

* **Aggregations are correct** – `SUM(amount)` adds every order for a customer, giving the total revenue. `MIN(order_date)` picks the earliest order, satisfying the “first purchase” requirement.
* **Ranking logic matches the specification** – The `ORDER BY` inside `DENSE_RANK()` first sorts by descending lifetime value, then by ascending first purchase date. That exactly implements “higher value first; if equal, earlier first purchase first.”
* **Tie handling** – `DENSE_RANK()` assigns the same rank to customers with identical lifetime value *and* identical first purchase date, and the next distinct customer gets the next integer rank (no gaps). This is the usual interpretation of “rank customers” in interview problems.
* **Performance** – The query scans the `Orders` table once to compute the aggregates (O(N)). The ranking step works on the much smaller set of distinct customers, so the overall cost is dominated by the aggregation, which is optimal for this problem.

---

**Common pitfalls to avoid**

1. **Using `ROW_NUMBER()` instead of a rank function** – `ROW_NUMBER()` would break ties arbitrarily, giving different ranks to customers who should be tied.
2. **Ordering the rank the wrong way** – `ORDER BY lifetime_value ASC` would give the lowest spenders the best rank, which is opposite of the requirement.
3. **Forgetting the second tie‑breaker** – If you only order by `lifetime_value DESC` and ignore `first_purchase`, customers with the same revenue would be ordered by an undefined rule, violating the “earlier first purchase higher” clause.
4. **Selecting raw rows instead of aggregated ones** – Trying to rank directly on the `Orders` table would produce a rank per order, not per customer.

---

**Takeaway for the interview**

* Identify the aggregation you need (sum and min).
* Use a CTE or sub‑query to isolate the per‑customer metrics.
* Choose the appropriate window function (`DENSE_RANK`, `RANK`, or `ROW_NUMBER`) based on how ties should be treated.
* Encode the tie‑breaker directly in the `ORDER BY` clause of the window function.

If you can explain each of those steps clearly, you’ll demonstrate both SQL syntax knowledge and the logical thinking interviewers look for.