Identifying the top-N records within each group is one of the most common — and most misunderstood — SQL challenges. This lesson teaches two production-ready techniques using correlated subqueries and self-joins, so you can solve this problem confidently on any database without window functions.
Picture this: your manager asks for the top 3 best-selling products in each product category for last quarter. You know how to find the single best-seller across all categories — that's just ORDER BY revenue DESC LIMIT 3. But per category? That's a different problem entirely, and it's one of those queries that stumps even experienced SQL writers the first time they encounter it.
The "Top-N per group" problem appears constantly in real analytics work. Who are the top 5 customers by lifetime value in each region? What are the 3 most recent orders per customer? Which salesperson ranked first in each territory last month? These questions share a common structure: you need to rank or filter within partitions of your data, then keep only the top performers from each partition.
Modern SQL has a beautiful solution: window functions like ROW_NUMBER() and RANK(). But here's the practical reality — window functions aren't available in every database version, every team has legacy systems, and more importantly, understanding how to solve this problem without window functions will deepen your mastery of subqueries, joins, and aggregation more than almost any other exercise. By the end of this lesson, you'll have two clean techniques for finding top-N records per group using subqueries and self-joins, you'll understand exactly why they work, and you'll know when to reach for each approach.
What you'll learn:
You should be comfortable writing SELECT, WHERE, GROUP BY, and JOIN queries before diving in. You should also understand how subqueries work — particularly correlated subqueries where the inner query references the outer query's rows. If subqueries feel shaky, review Understanding SQL Subqueries: Filtering and Looking Up Data with Nested SELECT Statements before continuing. You don't need any experience with window functions.
Let's set up a realistic scenario we'll use throughout the lesson. You work at a retail company, and you have an orders table that looks like this:
-- orders table
-- order_id | customer_id | category | product_name | revenue | order_date
-- 1001 | 42 | Electronics| Wireless Headphones | 189.99 | 2024-01-15
-- 1002 | 17 | Electronics| USB-C Hub | 49.99 | 2024-01-16
-- 1003 | 88 | Electronics| Wireless Headphones | 189.99 | 2024-01-17
-- 1004 | 42 | Apparel | Running Shoes | 129.99 | 2024-01-18
-- 1005 | 55 | Apparel | Compression Socks | 24.99 | 2024-01-19
-- ...
You want the top 2 products by total revenue within each category. Your first instinct might be to reach for GROUP BY and HAVING:
-- This does NOT solve the problem
SELECT category, product_name, SUM(revenue) AS total_revenue
FROM orders
GROUP BY category, product_name
HAVING SUM(revenue) >= 500;
This fails for a simple reason: the HAVING threshold is arbitrary and category-agnostic. A threshold of $500 might give you 4 products in Electronics and 0 in Apparel. You'd need to know the cutoff per category in advance, and if the data changes, your query breaks.
The fundamental issue is that SQL's WHERE and HAVING clauses filter rows globally — they don't have a built-in concept of "the top N rows within each group." That's the gap we're filling.
Key insight
The Top-N per group problem requires relative ranking (this row is ranked 2nd within its category), but standard WHERE filters are absolute (this row meets a condition regardless of context). Solving it means simulating relative ranking using tools that SQL does have: subqueries and joins.
Let's create a clean working dataset. We'll use a product_sales table that aggregates revenue per product per category, which is a natural starting point for category rankings:
CREATE TABLE product_sales (
sale_id INT PRIMARY KEY,
category VARCHAR(50),
product_name VARCHAR(100),
total_revenue DECIMAL(10,2),
units_sold INT
);
INSERT INTO product_sales VALUES
(1, 'Electronics', 'Wireless Headphones', 4250.00, 22),
(2, 'Electronics', 'Bluetooth Speaker', 3890.00, 19),
(3, 'Electronics', 'USB-C Hub', 2100.00, 42),
(4, 'Electronics', 'Portable Charger', 1750.00, 35),
(5, 'Electronics', 'Smart Watch Band', 980.00, 49),
(6, 'Apparel', 'Running Shoes', 5100.00, 34),
(7, 'Apparel', 'Compression Socks', 1200.00, 96),
(8, 'Apparel', 'Performance T-Shirt', 2750.00, 55),
(9, 'Apparel', 'Insulated Jacket', 4800.00, 24),
(10, 'Apparel', 'Trail Shorts', 875.00, 35),
(11, 'Home', 'Air Purifier', 3600.00, 12),
(12, 'Home', 'Robot Vacuum', 6200.00, 10),
(13, 'Home', 'Smart Thermostat', 2900.00, 18),
(14, 'Home', 'Weighted Blanket', 1450.00, 29),
(15, 'Home', 'Ceramic Knife Set', 960.00, 32);
We have three categories (Electronics, Apparel, Home), each with 5 products. Our goal: retrieve the top 3 products by revenue within each category.
The expected result should be:
Now let's build two different approaches to get there.
This is the most conceptually elegant solution. The idea: for each row, count how many other rows in the same category have higher revenue. If that count is less than N, this row is in the top N.
Think about it for a moment. If Wireless Headphones ($4,250) is the best-seller in Electronics, then zero other Electronics products have higher revenue. Its count is 0, which is less than 3 — so it qualifies for top 3. Bluetooth Speaker ($3,890) has one product ahead of it (Wireless Headphones), so its count is 1 — still less than 3, it qualifies. USB-C Hub ($2,100) has two products ahead of it, count is 2 — qualifies. Portable Charger ($1,750) has three ahead of it, count is 3 — that's NOT less than 3, so it's excluded.
Here's that logic in SQL:
SELECT
ps_outer.category,
ps_outer.product_name,
ps_outer.total_revenue,
ps_outer.units_sold
FROM product_sales ps_outer
WHERE (
SELECT COUNT(*)
FROM product_sales ps_inner
WHERE ps_inner.category = ps_outer.category
AND ps_inner.total_revenue > ps_outer.total_revenue
) < 3
ORDER BY ps_outer.category, ps_outer.total_revenue DESC;
Let's trace through what's happening:
product_sales (aliased as ps_outer)ps_inner.category = ps_outer.category) have a strictly higher revenue (ps_inner.total_revenue > ps_outer.total_revenue)WHERE condition keeps only rows where that count is less than 3Run this and you get exactly the 9 rows we expected — 3 per category, sorted by revenue descending within each category.
Note
The subquery is called "correlated" because it references a column from the outer query (ps_outer.category and ps_outer.total_revenue). It re-executes for every row the outer query processes. For large tables, this can be slow — we'll cover performance implications in a dedicated section below.
Notice that the subquery uses strict greater-than (>), not >=. This has important implications for ties.
Imagine two products in Electronics both have revenue of $2,100. Both would count the same number of rows ahead of them, so both would qualify for top 3. If your top-3 slots are already filled by two genuinely higher-ranked products, you could end up with 4 results for Electronics — not 3.
This is actually the correct behavior in many business contexts. If two products tie for 3rd place, you probably want both included. This is called "dense ranking" — you show all tied competitors for a given slot.
But if you need exactly N rows per group regardless of ties, you need to add a tiebreaker. The cleanest approach is to add a secondary sort column to the subquery:
SELECT
ps_outer.category,
ps_outer.product_name,
ps_outer.total_revenue,
ps_outer.units_sold
FROM product_sales ps_outer
WHERE (
SELECT COUNT(*)
FROM product_sales ps_inner
WHERE ps_inner.category = ps_outer.category
AND (
ps_inner.total_revenue > ps_outer.total_revenue
OR (ps_inner.total_revenue = ps_outer.total_revenue
AND ps_inner.sale_id < ps_outer.sale_id)
)
) < 3
ORDER BY ps_outer.category, ps_outer.total_revenue DESC;
This says: "count rows that are ahead of me, where ahead means either higher revenue, or equal revenue but a lower sale_id." With a unique tiebreaker like sale_id, you always get exactly N rows per group.
Warning
If you're working with nullable columns in your ranking logic, > comparisons involving NULL always evaluate to FALSE in SQL — NULL > 100 returns NULL, not TRUE or FALSE. This means rows with NULL revenue will always count zero rows ahead of them and appear to qualify for any top-N cutoff. Always use COALESCE or NULLIF to handle nulls explicitly before ranking.
The second technique achieves the same result through a completely different mechanism: joining the table to itself and counting competitors directly in the GROUP BY.
This approach is worth understanding because it expresses the ranking logic through joins rather than nested subqueries, which can be easier to optimize on certain database engines and sometimes more readable to SQL practitioners who think in terms of relational algebra.
The idea: join every product to all products in the same category that outrank it. Then group by product and count how many outrankers it has. Filter where that count is below your threshold.
SELECT
ps1.category,
ps1.product_name,
ps1.total_revenue,
ps1.units_sold,
COUNT(ps2.sale_id) AS products_ranked_higher
FROM product_sales ps1
LEFT JOIN product_sales ps2
ON ps1.category = ps2.category
AND ps2.total_revenue > ps1.total_revenue
GROUP BY
ps1.category,
ps1.product_name,
ps1.total_revenue,
ps1.units_sold
HAVING COUNT(ps2.sale_id) < 3
ORDER BY ps1.category, ps1.total_revenue DESC;
Let's trace through the join logic step by step.
For Wireless Headphones (the top Electronics product at $4,250): when we join ps2 to find Electronics products with revenue greater than $4,250, nothing matches. The LEFT JOIN keeps the row but ps2.sale_id is NULL, so COUNT(ps2.sale_id) returns 0. The HAVING clause keeps rows where that count is less than 3, so Wireless Headphones passes.
For Portable Charger ($1,750): three Electronics products beat it — Wireless Headphones, Bluetooth Speaker, and USB-C Hub. The join produces 3 matching ps2 rows, COUNT(ps2.sale_id) is 3, and HAVING COUNT(ps2.sale_id) < 3 is FALSE, so Portable Charger is excluded.
Key insight
The LEFT JOIN here is critical, not an INNER JOIN. If you use INNER JOIN, any row with zero competitors would be dropped entirely — meaning your #1 product in each category would disappear from the results. Always use LEFT JOIN when you're counting competitors that may not exist. For a deeper dive on this distinction, see Choosing the Right JOIN Type: When to Use INNER, LEFT, and FULL OUTER JOIN for Clean Aggregation Results.
Both approaches return the same results for our dataset. But they have different strengths:
| Aspect | Correlated Subquery | Self-Join + GROUP BY |
|---|---|---|
| Readability | Very clear logic | Slightly more JOIN/GROUP BY complexity |
| Performance | Re-executes per row | Single pass join, then group |
| Ties handling | Straightforward to adjust | Requires careful COUNT/HAVING logic |
| Index usage | Benefits from index on (category, total_revenue) |
Benefits from same index |
| Optimizer behavior | Varies widely by database | Generally more predictable |
For small-to-medium tables (under ~100,000 rows), both work fine. For large datasets, the self-join approach often gives the query optimizer more options to work with, though this varies significantly by database engine and index structure.
Real-world Top-N problems rarely involve a single pre-aggregated table. More often, you're working with normalized data across multiple tables. Let's make this more realistic.
Suppose you have a proper orders schema:
-- customers table
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
region VARCHAR(50)
);
-- orders table
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
total_amount DECIMAL(10,2)
);
Your goal: find the top 2 customers by total spending within each region.
This requires two steps:
The cleanest way to handle multi-step problems like this is with a derived table (an inline subquery in the FROM clause) for the aggregation step, then the correlated subquery approach for ranking. If you want to learn more about structuring queries this way, Writing SQL FROM Scratch: Structuring Multi-Step Analytical Queries with Derived Tables and Inline Views covers this pattern in depth.
Here's the full query:
SELECT
customer_totals.region,
customer_totals.customer_name,
customer_totals.lifetime_spend
FROM (
-- Step 1: aggregate orders per customer, join to get region and name
SELECT
c.customer_id,
c.customer_name,
c.region,
SUM(o.total_amount) AS lifetime_spend
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.region
) AS customer_totals
WHERE (
-- Step 2: correlated subquery counts higher-spending customers in same region
SELECT COUNT(*)
FROM (
SELECT
c2.customer_id,
c2.region,
SUM(o2.total_amount) AS lifetime_spend
FROM customers c2
JOIN orders o2 ON c2.customer_id = o2.customer_id
GROUP BY c2.customer_id, c2.region
) AS inner_totals
WHERE inner_totals.region = customer_totals.region
AND inner_totals.lifetime_spend > customer_totals.lifetime_spend
) < 2
ORDER BY customer_totals.region, customer_totals.lifetime_spend DESC;
This query has three layers of nesting, and that's perfectly normal for this class of problem. Notice that the aggregation subquery is duplicated — once in the outer FROM and once inside the correlated WHERE. This duplication is the main cost of the correlated subquery approach on complex data; the aggregation runs twice per outer row.
Tip
If your database supports Common Table Expressions (CTEs), you can define the aggregation once as a CTE and reference it in both the outer query and the correlated subquery. This improves readability significantly and, in databases that materialize CTEs, can also improve performance. Check out Advanced Subqueries and CTEs: Mastering Complex SQL Query Architecture for the CTE-based pattern.
Sometimes your ranking column doesn't exist directly in the table — it's computed. For example, you want the top 2 products per category by revenue per unit (a calculated efficiency metric):
SELECT
ps_outer.category,
ps_outer.product_name,
ps_outer.total_revenue,
ps_outer.units_sold,
ROUND(ps_outer.total_revenue / ps_outer.units_sold, 2) AS revenue_per_unit
FROM product_sales ps_outer
WHERE (
SELECT COUNT(*)
FROM product_sales ps_inner
WHERE ps_inner.category = ps_outer.category
AND (ps_inner.total_revenue / ps_inner.units_sold)
> (ps_outer.total_revenue / ps_outer.units_sold)
) < 2
ORDER BY ps_outer.category,
(ps_outer.total_revenue / ps_outer.units_sold) DESC;
Note that the computed expression total_revenue / units_sold must appear in the subquery's WHERE condition — you can't reference a column alias defined in the outer SELECT. This is one of the frustrations of working without window functions; the calculation needs to be repeated in every context where it's used. The lesson on writing reusable SQL column logic discusses strategies for minimizing this kind of repetition.
To find the bottom N records per group instead of the top N, flip the comparison operator in the correlated subquery from > to <:
-- Bottom 2 products by revenue per category
SELECT
ps_outer.category,
ps_outer.product_name,
ps_outer.total_revenue
FROM product_sales ps_outer
WHERE (
SELECT COUNT(*)
FROM product_sales ps_inner
WHERE ps_inner.category = ps_outer.category
AND ps_inner.total_revenue < ps_outer.total_revenue -- NOTE: < instead of >
) < 2
ORDER BY ps_outer.category, ps_outer.total_revenue ASC;
This counts how many products in the same category have lower revenue. If that count is less than 2, this product is in the bottom 2.
Another very common pattern is "the N most recent transactions per customer." The ranking column is a date, not a revenue figure:
-- Most recent 3 orders per customer
SELECT
o_outer.customer_id,
o_outer.order_id,
o_outer.order_date,
o_outer.total_amount
FROM orders o_outer
WHERE (
SELECT COUNT(*)
FROM orders o_inner
WHERE o_inner.customer_id = o_outer.customer_id
AND o_inner.order_date > o_outer.order_date
) < 3
ORDER BY o_outer.customer_id, o_outer.order_date DESC;
The same pattern, with order_date as the ranking column. The subquery counts how many orders for the same customer have a later date. Any order with fewer than 3 later orders is in the top 3 most recent.
Warning
If two orders for the same customer share the same order_date, both will count identical numbers of rows ahead of them, and you may get more than N results for that customer. Add order_id as a tiebreaker (earlier order IDs lose the tiebreak) the same way we handled tied revenues earlier.
Let's be honest about the cost of these approaches. The correlated subquery re-executes for every row in the outer query. On a table with 10,000 products across 200 categories, the subquery might run 10,000 times, each time scanning up to 50 rows. That's potentially 500,000 row comparisons.
The self-join approach avoids repeated re-execution by doing a single join, but a join between 10,000 rows and itself still produces up to 100,000,000 row combinations before filtering — though the database optimizer can use indexes to prune this heavily.
The most important performance lever: indexing the grouping and ranking columns together.
For our product_sales example:
-- Composite index on the two columns used for grouping and ranking
CREATE INDEX idx_product_sales_category_revenue
ON product_sales (category, total_revenue DESC);
With this index, the database can seek directly to all rows in a given category and scan them in revenue order — the exact access pattern both approaches need. Without this index, every subquery execution or join partition requires a full table scan. For a deeper understanding of how index structure affects query performance, SQL Indexes Explained: How They Work and When to Create Them is the right follow-up.
When is the performance cost acceptable?
Work through this exercise to cement the techniques. Use the product_sales table we created earlier.
Exercise 1: Top-2 by Units Sold Per Category
Write a query using the correlated subquery method to find the top 2 products by units_sold (not revenue) within each category. Handle ties by using sale_id as a tiebreaker (lower sale_id wins in a tie).
Expected results:
Exercise 2: Bottom-1 Revenue Product Per Category
Using the self-join aggregation method, find the single worst-performing product by revenue in each category.
Expected results:
Exercise 3: Multi-Metric Ranking
Write a query that returns the top 2 products per category, ranked by a composite score of (total_revenue * 0.7) + (units_sold * 10 * 0.3). This simulates a weighted business priority score.
For this one, you'll need to write the composite expression in both the outer SELECT and the inner correlated subquery's WHERE condition.
Bonus challenge: Extend Exercise 3 to include a column showing each product's rank within its category (1, 2, 3, etc.) without using any window functions. Hint: the rank equals the count from the correlated subquery plus 1.
-- Bonus hint structure:
SELECT
ps_outer.category,
ps_outer.product_name,
ps_outer.total_revenue,
(
SELECT COUNT(*) + 1
FROM product_sales ps_inner
WHERE ps_inner.category = ps_outer.category
AND ps_inner.total_revenue > ps_outer.total_revenue
) AS rank_in_category
FROM product_sales ps_outer
-- Add your WHERE clause here to filter top-N
ORDER BY ps_outer.category, rank_in_category;
This is the key insight for the bonus: the count-of-rows-ahead-of-me, plus 1, is the rank. You can include it as a SELECT column at the same time you use it as a WHERE filter. If you want to explore how to build these kinds of multi-step analytical patterns further, Writing Multi-Step Analytical Queries: Chaining Subqueries, JOINs, and GROUP BY to Answer Real Business Questions has more exercises like this.
Symptom: Your top-ranked item in each group is missing from results.
Why it happens: The number-one product in each group has zero competitors with higher revenue. An INNER JOIN requires at least one matching row in ps2, so when no competitors exist, the row is dropped entirely.
Fix: Always use LEFT JOIN when joining to count competitors that may not exist.
Symptom: Your query returns only the global top-N across all groups, not the top-N per group.
Broken query:
-- WRONG: missing the category filter in the subquery
WHERE (
SELECT COUNT(*)
FROM product_sales ps_inner
WHERE ps_inner.total_revenue > ps_outer.total_revenue
-- Missing: AND ps_inner.category = ps_outer.category
) < 3
Without AND ps_inner.category = ps_outer.category, the subquery counts all products globally with higher revenue. A product ranked 3rd in Electronics would be counted against products from all other categories and might be excluded even though it's legitimately top-3 in its own group.
Fix: Always include the group-equality condition in the correlated subquery.
Symptom: You get top-4 instead of top-3, or top-2 instead of top-3.
Why it happens: Confusing < N vs <= N. If you want the top 3, you keep rows where the count of higher-ranked rows is strictly less than 3 (meaning 0, 1, or 2 rows ahead). Using <= 3 would keep rows where up to 3 other rows are ahead, giving you 4 results per group.
Count of rows with higher revenue = 0 → Rank 1 → keep if threshold is < N (N ≥ 1)
Count of rows with higher revenue = 1 → Rank 2 → keep if threshold is < N (N ≥ 2)
Count of rows with higher revenue = 2 → Rank 3 → keep if threshold is < N (N ≥ 3)
Count of rows with higher revenue = 3 → Rank 4 → excluded when threshold is < 3
Rule: To get top-N, use COUNT(*) < N.
Symptom: Database error like "column must appear in GROUP BY or aggregate function."
Fix: Every column from ps1 that appears in your SELECT must appear in your GROUP BY. Since you're not aggregating ps1 columns (only counting ps2 matches), you need to list all of them:
GROUP BY
ps1.sale_id, -- include even the PK
ps1.category,
ps1.product_name,
ps1.total_revenue,
ps1.units_sold
Symptom: Products with NULL revenue appear in top-N results even though they shouldn't, or products you expect to see are missing.
Why it happens: NULL > 4250 evaluates to NULL (not FALSE), so rows with NULL revenue count zero competitors ahead of them and qualify for every top-N cutoff.
Fix: Filter out NULLs explicitly, or use COALESCE to substitute a sentinel value:
WHERE (
SELECT COUNT(*)
FROM product_sales ps_inner
WHERE ps_inner.category = ps_outer.category
AND COALESCE(ps_inner.total_revenue, 0) > COALESCE(ps_outer.total_revenue, 0)
) < 3
AND ps_outer.total_revenue IS NOT NULL
Tip
If you're debugging unexpected results from your Top-N query, start by running just the correlated subquery in isolation for a few specific outer rows. Replace the outer table reference with a hard-coded value and check what the subquery returns. This step-by-step decomposition is covered in Debugging SQL Queries: How to Read Error Messages, Trace Wrong Results, and Fix Broken Joins Step by Step.
Let's put everything together in a single realistic deliverable: a category performance report that shows the top 3 products per category, including a computed rank column and a comparison to the category average.
SELECT
ranked.category,
ranked.rank_in_category,
ranked.product_name,
ranked.total_revenue,
ranked.units_sold,
ROUND(ranked.total_revenue / ranked.units_sold, 2) AS revenue_per_unit,
ROUND(cat_avg.avg_category_revenue, 2) AS category_avg_revenue,
ROUND(
(ranked.total_revenue - cat_avg.avg_category_revenue)
/ cat_avg.avg_category_revenue * 100,
1
) AS pct_above_category_avg
FROM (
-- Outer query: select products with their rank, filtered to top 3
SELECT
ps_outer.sale_id,
ps_outer.category,
ps_outer.product_name,
ps_outer.total_revenue,
ps_outer.units_sold,
(
SELECT COUNT(*) + 1
FROM product_sales ps_inner
WHERE ps_inner.category = ps_outer.category
AND ps_inner.total_revenue > ps_outer.total_revenue
) AS rank_in_category
FROM product_sales ps_outer
WHERE (
SELECT COUNT(*)
FROM product_sales ps_inner
WHERE ps_inner.category = ps_outer.category
AND ps_inner.total_revenue > ps_outer.total_revenue
) < 3
) AS ranked
JOIN (
-- Per-category average revenue
SELECT
category,
AVG(total_revenue) AS avg_category_revenue
FROM product_sales
GROUP BY category
) AS cat_avg ON ranked.category = cat_avg.category
ORDER BY ranked.category, ranked.rank_in_category;
This query does several things at once:
The result is a complete, business-ready report showing not just who the winners are, but how dominant they are relative to their peers.
For how to structure queries with this level of complexity into readable layers, Building a Complete Analytical Query from Scratch: Combining SELECT, JOIN, GROUP BY, and Subqueries to Answer a Multi-Part Business Question walks through the same layering process in detail.
You now have two solid, production-ready techniques for solving the Top-N per group problem without window functions.
The correlated subquery approach works by counting how many rows in the same group outrank the current row. If that count is less than N, the row qualifies. It's clean to read, easy to extend with tiebreakers, and the ranking count doubles as an explicit rank column when you add 1 to it.
The self-join aggregation approach achieves the same goal by joining the table to itself on the group key with a ranking inequality, then grouping and filtering with HAVING. It's slightly more verbose but can give the query optimizer more flexibility on large datasets.
Both approaches share the same core insight: simulate a relative rank by counting competitors within the same group. Everything else — computed ranking columns, temporal rankings, bottom-N, multi-metric composite scores — is a variation on this foundation.
Key things to remember:
COUNT(*) < N (not <= N) to get exactly N results when there are no tiesps_inner.category = ps_outer.category)LEFT JOIN (not INNER JOIN) in the self-join method so top-ranked rows aren't dropped(group_column, ranking_column) for meaningful performance gainsWhere to go next: Once you're comfortable with these subquery-based ranking techniques, the natural next step is learning window functions — specifically ROW_NUMBER(), RANK(), and DENSE_RANK(). They solve the exact same problem with dramatically cleaner syntax and often better performance. But now that you understand the mechanics underneath, window functions will make much more sense because you'll know what they're abstracting away.
In the meantime, if you want to push your subquery skills further, explore the Mastering SQL EXISTS and NOT EXISTS: Correlated Subquery Patterns for Filtering with Related Data lesson — EXISTS is another powerful correlated subquery pattern that pairs naturally with the ranking techniques you just learned.