Coding DatabaseIntermediate30 minSaves 30 minutes

Retrieve Top-3 Orders by Customer Revenue with Window Functions

Q: Can I use this approach if I need to retrieve the *bottom* N orders instead?

Yes, simply change the `ORDER BY revenue DESC` to `ORDER BY revenue ASC` within the `ROW_NUMBER()` window function. The rest of the query structure for partitioning and filtering remains the same, retrieving the lowest revenue orders.

Q: What if multiple orders have the exact same revenue for a customer?

`ROW_NUMBER()` assigns an arbitrary but consistent unique rank among tied rows. If you need tied rows to share a rank, `RANK()` or `DENSE_RANK()` would be more suitable alternatives, depending on the exact tie-breaking logic desired.

Q: Will this query perform well on a table with billions of rows?

Performance on very large tables depends heavily on proper indexing. Without an optimal index on `customer_id` and `revenue`, the sorting operation required for partitioning will become a significant bottleneck. With a suitable index, it can perform efficiently.

Q: Can I retrieve different top N values for different customers?

Not directly with a single `ROW_NUMBER()` call filtering `rn <= {{top_n_orders}}`. You would need a more complex query, potentially involving a join to a lookup table that specifies a custom N value for each customer, or a `CASE` statement within the filter.

Q: Is `ROW_NUMBER()` specific to PostgreSQL?

No, `ROW_NUMBER()` is a standard SQL window function. It is widely supported across most modern relational database systems, including MySQL (8.0+), SQL Server, Oracle, and SQLite (3.25+), ensuring broad portability.

Q: How does the `PARTITION BY` clause work here?

`PARTITION BY customer_id` divides the dataset into independent groups, one for each distinct customer. `ROW_NUMBER()` then restarts its count from 1 for each new customer group, allowing per-customer ranking.

Q: Why is `revenue` specified as `DECIMAL(10,2)`?

Using `DECIMAL(10,2)` ensures exact precision for financial values. This prevents potential rounding errors or inaccuracies that can occur with floating-point types (like `FLOAT` or `DOUBLE`) during comparisons, sorting, and aggregations, ensuring data integrity.

Backend engineers writing analytical SQL need to efficiently query top-N results per group. This prompt provides a tested SQL solution for ranking orders by revenue per customer using ROW_NUMBER for optimized performance on large datasets.

Develop an optimized SQL query to identify the top three orders per customer based on revenue using window functions. This solution includes DDL, the query, expected plan notes, and index recommendations, ensuring efficient performance on large datasets.

ChatGPT Gemini Claude

READY-TO-USE PROMPT

Copy Prompt

prompt.txt

Role: Database Engineer

Context: You are tasked with developing a performant SQL query for an analytical reporting system. The dataset is a `transactions` table containing customer order information, including `customer_id`, `order_id`, and `revenue`. The table is expected to grow to millions of rows.

Task: Generate a complete SQL solution to retrieve the top-{{top_n_orders}} orders for each customer, ranked by `revenue` in descending order. The solution must include:

1.  **DDL (Data Definition Language):** A `CREATE TABLE` statement for a `{{table_name}}` table with appropriate columns (`customer_id` INT, `order_id` INT, `revenue` DECIMAL(10,2)).
2.  **Test Data:** `INSERT` statements to populate the `{{table_name}}` table with at least 10-15 rows, representing at least 3 distinct customers, each with varying numbers of orders and revenues, ensuring some customers have more than `{{top_n_orders}}` orders.
3.  **SQL Query:** A query that uses a window function, specifically `ROW_NUMBER()` over a `PARTITION BY customer_id ORDER BY revenue DESC`, to assign ranks and then filter for ranks 1 through `{{top_n_orders}}`.
4.  **Expected Query Plan Notes:** Describe the typical execution plan for the provided query, specifically mentioning how the window function operates and the likely cost implications, especially concerning sorting and partitioning.
5.  **Index Recommendation:** Suggest an optimal index (or indexes) for the `{{table_name}}` table that would significantly improve the performance of the provided query. Explain why this index is beneficial.

Constraints:

*   The solution must use `ROW_NUMBER()`.
*   The output should be easily readable and executable.
*   Assume a PostgreSQL or compatible SQL environment.
*   The `transactions` table name should be `{{table_name}}`.
*   The number of top orders to retrieve per customer should be `{{top_n_orders}}`.

Output:
Provide the full SQL solution, including DDL, test data, the query, expected plan notes, and index recommendations, formatted clearly in a single markdown code block where appropriate, and explanatory text outside the code block for the notes and recommendations.

Estimated results

DifficultyIntermediate

Setup time30 min

Time saved30 minutes

Best modelsChatGPT, Gemini, Claude

Best audienceData Engineering, Business Intelligence

Editor's note

Why this prompt matters

Retrieving specific ranked items within groups is a common challenge for backend and data engineers working with analytical databases. Whether it's the top three products sold per category or the highest-revenue orders for each customer, efficiently extracting this information from large transactional tables is critical for reporting and business intelligence. Suboptimal approaches can lead to lengthy query times, especially as datasets scale into millions of rows.

This workflow addresses the need for a performant and well-structured SQL solution to identify top-N records per group. It's particularly useful for engineers who require a reliable pattern for ranking data, understanding its performance implications, and implementing appropriate indexing strategies. Reaching for this pattern ensures that complex analytical queries execute efficiently, providing timely insights without taxing database resources unnecessarily.

Anatomy

Prompt engineering breakdown

Role

Act as a database engineer, generating a complete and performant SQL solution to retrieve top-N orders per customer using window functions, including DDL, test data, query plan analysis, and indexing strategies.

Context

Develop a performant SQL query for an analytical reporting system on a `transactions` table with customer order data. The table will scale to millions of rows.

Goal

Generate a complete SQL solution to retrieve the top-N orders for each customer, ranked by revenue, including DDL, test data, the query using `ROW_NUMBER()`, expected query plan notes, and index recommendations.

Constraints

The solution must use `ROW_NUMBER()`. Output should be readable and executable. Assume a PostgreSQL or compatible SQL environment. The `transactions` table name should be `{{table_name}}`. The number of top orders to retrieve per customer should be `{{top_n_orders}}`.

Output format

Provide the full SQL solution (DDL, test data, query) in a single markdown code block. Expected plan notes and index recommendations should be provided as explanatory text outside the code block.

Why this structure works

Role priming as a 'Database Engineer' ensures the response is technically sound and performance-aware. Explicit constraints on using ROW_NUMBER() and assuming PostgreSQL guide the model to the correct function and syntax. The detailed, structured output requirement ensures all necessary components—from DDL to index recommendations—are included and presented clearly for direct application.

Pick your version

Prompt variations

BeginnerWorks with any model

When you're new to SQL window functions or need a simplified explanation of the query components.

prompt.txt

Your task is to help a new SQL user understand how to find the top `{{num_items}}` items for each group. Imagine you have a table called `{{item_table}}` with columns: `group_id` (INT), `item_id` (INT), and `value` (DECIMAL(10,2)). You need to write a SQL query that shows the `{{num_items}}` items with the highest `value` for every distinct `group_id`. Please include the `CREATE TABLE` and some `INSERT` statements so I can test it. Explain simply how the query works, focusing on the ranking part, and suggest a good index to make the query faster. The output should be easy to read and understand.

ProfessionalBest with claude

When you require a detailed, production-ready SQL solution with performance considerations and plan analysis.

prompt.txt

As a Senior SQL Developer, your assignment is to craft an optimized query for identifying the top `{{top_count}}` orders by `revenue` for each `customer_id` from a `financial_transactions` table. This solution must consider scalability for millions of records. Provide a comprehensive package: DDL for `{{table_name}}` (`customer_id` INT, `order_id` INT, `revenue` DECIMAL(10,2)), sufficient `INSERT` statements to demonstrate functionality with at least 3 distinct customers and varying order counts, the `ROW_NUMBER()`-based query, a detailed analysis of the expected query plan focusing on partitioning and sorting costs, and an index strategy to enhance performance. Ensure the output is suitable for a PostgreSQL environment and adheres to best practices.

Short VersionBest with chatgpt

For quick, concise SQL generation when you already understand window functions and need the core query and DDL rapidly.

prompt.txt

Generate a complete SQL solution to find the top `{{n_val}}` orders per customer by `revenue` from a `sales_data` table. Include DDL for `{{table_name}}` (`customer_id` INT, `order_id` INT, `revenue` DECIMAL(10,2)), `INSERT` statements for testing (at least 3 customers, varying orders), and the `ROW_NUMBER()` query to rank and filter. Briefly describe the expected query plan and suggest an optimal index. The solution should be PostgreSQL compatible and use `ROW_NUMBER()` to achieve the ranking per `customer_id` with `revenue` in descending order.

EnterpriseBest with gemini

When deploying solutions in a regulated or high-stakes environment, requiring documentation for compliance, auditing, or stakeholder review.

prompt.txt

As a Principal Database Architect, develop a compliant and auditable SQL solution for identifying the top `{{k_value}}` orders per `customer_identifier` based on `transaction_value` from the `transaction_ledger` table. This solution must adhere to data governance standards and be suitable for production deployment in a high-volume analytical environment. Your deliverable must include: a `CREATE TABLE` statement for `{{ledger_name}}` with `customer_identifier` (INT), `order_reference` (INT), `transaction_value` (DECIMAL(10,2)); a dataset of `INSERT` statements demonstrating various customer profiles and order volumes for validation; the `ROW_NUMBER()`-based query; a detailed performance impact analysis, including potential resource consumption and an optimized index strategy for data integrity and query efficiency. Provide comprehensive documentation for stakeholder review and future auditing, confirming PostgreSQL compatibility.

What you'll get

Expected output

-- DDL CREATE TABLE transactions ( customer_id INT NOT NULL, order_id INT NOT NULL UNIQUE, revenue DECIMAL(10,2) NOT NULL, PRIMARY KEY (order_id) );

-- Test Data INSERT INTO transactions (customer_id, order_id, revenue) VALUES (101, 1001, 150.75), (101, 1002, 200.00), (101, 1003, 75.20), (101, 1004, 300.50), (102, 2001, 50.00), (102, 2002, 120.99), (102, 2003, 80.10), (103, 3001, 400.00), (103, 3002, 100.00), (103, 3003, 500.00), (104, 4001, 25.00), (104, 4002, 35.00), (105, 5001, 99.99);

-- SQL Query WITH RankedOrders AS ( SELECT customer_id, order_id, revenue, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY revenue DESC) as rn FROM transactions ) SELECT customer_id, order_id, revenue FROM RankedOrders WHERE rn <= 3 ORDER BY customer_id, rn;

-- Expected Query Plan Notes The query plan will typically involve a WindowAgg operation. The PARTITION BY customer_id clause requires data to be grouped by customer_id. Following this, the ORDER BY revenue DESC within each partition necessitates a sort operation on revenue for each customer_id group. This sorting step is often the most resource-intensive part, especially on large datasets. After ranks are assigned, a Filter step prunes rows where rn exceeds 3. The final ORDER BY customer_id, rn may introduce another sort. Without an appropriate index, a full table scan followed by a large sort will occur before the window function.

-- Index Recommendation To optimize this query, an index on (customer_id, revenue DESC) is recommended.

CREATE INDEX idx_customer_revenue ON transactions (customer_id, revenue DESC);

This index significantly improves performance by:

Partitioning Support: customer_id in the index directly supports the PARTITION BY customer_id clause, allowing efficient grouping.
Pre-sorted Data: Including revenue DESC in the index means data within each customer_id group is already sorted as required by the window function's ORDER BY clause. This largely eliminates expensive sort operations during the WindowAgg phase, which is critical for large tables.

Under the hood

Why this prompt works

This prompt structure yields high-quality, executable solutions by employing several targeted techniques. Role priming establishes the model's persona as a "Database Engineer," ensuring the output reflects a deep understanding of database performance, query plans, and indexing strategies, rather than just a basic SQL syntax. This focus on an expert perspective elevates the technical accuracy and practical utility of the response.

Explicit constraints like "The solution must use ROW_NUMBER()" and "Assume a PostgreSQL or compatible SQL environment" precisely guide the model to the required technique and dialect. This prevents generic or incompatible solutions, ensuring the output is directly applicable to the stated problem and environment.

Furthermore, the structured output requirements are key. By explicitly requesting DDL, test data, the SQL query, expected plan notes, and index recommendations, the prompt mandates a comprehensive and organized solution. This detailed breakdown ensures that all aspects of implementing and optimizing the query are covered, providing a complete, ready-to-use package rather than just an isolated query. This structured approach forces the model to think through the entire lifecycle of a database solution, from schema definition to performance tuning.

Model fit

Best AI models for this prompt

Claude

Claude excels at understanding complex SQL requirements and generating well-structured, commented code. It generally produces correct window function syntax and provides reasonable explanations for query plans and indexing strategies. Its longer context window helps maintain coherence across DDL, data, query, and analysis. See the full Claude hub for deeper guidance.

ChatGPT

ChatGPT is effective for generating SQL queries, including those with window functions. It often provides solid DDL and test data, and its explanations for query plans are usually accurate. However, it sometimes requires refinement on index recommendations to ensure they are truly optimal for the specific query pattern. See the full ChatGPT hub for deeper guidance.

Gemini

Gemini can produce functional SQL for window functions, and its DDL and test data generation are generally reliable. It tends to focus on direct query generation. While it provides explanations, users might need to prompt for more detailed query plan insights or advanced indexing strategies. See the full Gemini hub for deeper guidance.

When to use

When needing to rank items within specific groups, such as the top N products per category or top N sales per region.
For analytical queries requiring a fixed number of highest or lowest values per entity.
When dealing with large datasets where window functions offer better performance than correlated subqueries for grouping and ranking.
When ensuring deterministic ranking, as ROW_NUMBER() assigns a unique rank even in the presence of ties.
For reporting dashboards where only the leading items are relevant for display or further analysis.

When not to use

When a simple aggregate (e.g., SUM, AVG) across the entire dataset or a basic GROUP BY is sufficient.
If you need all tied ranks to receive the same number; RANK() or DENSE_RANK() are more appropriate in that scenario.
For transactional updates or deletions based on rank, as window functions are typically read-only and not designed for DML operations.
If the dataset is very small and the overhead of a window function might slightly exceed a simpler, direct approach.

Get more from it

Pro tips

1
Always test with a representative data distribution, including customers with fewer than `{{top_n_orders}}` orders, to catch edge cases.
2
Verify index usage in the query plan to confirm the optimizer effectively utilizes the recommended index for sorting and partitioning, preventing full table scans.
3
Parameterize `{{top_n_orders}}` carefully; excessively high values can degrade performance due due to larger result sets and increased memory usage.
4
Consider `NTILE()` if you need to divide each customer's orders into a fixed number of equal-sized buckets rather than strict sequential ranking.
5
Pay attention to data types; `DECIMAL(10,2)` for revenue prevents floating-point inaccuracies during ranking and comparisons, ensuring precise results.
6
Review the `ORDER BY` clause within the window function; incorrect ordering (ASC vs DESC) can return unintended results for your top N selection.
7
For very large datasets, consider materializing the ranked results into a temporary table or CTE for subsequent queries to avoid repeated window function computations.
8
Be aware of the `PARTITION BY` columns; missing or incorrect partitions will lead to incorrect grouping and thus inaccurate ranking across the dataset.

Don't ship this

Common mistakes

✗ Omitting the `ORDER BY` clause within the `OVER()` for `ROW_NUMBER()`.
Fix — This results in arbitrary ranking. Always specify a deterministic `ORDER BY` for consistent, repeatable results based on a clear criterion.
✗ Forgetting to apply the outer `WHERE` clause for `rn <= {{top_n_orders}}`.
Fix — Without this filter, the query returns all ranked rows for every customer, not just the specified top N per group, increasing data transfer.
✗ Using `RANK()` or `DENSE_RANK()` when unique ranks are strictly required for tied rows.
Fix — `ROW_NUMBER()` ensures each row gets a distinct rank, which is often preferred for precise "top N" selections where tie-breaking matters.
✗ Not creating the recommended composite index before running the query on production-scale data.
Fix — A composite index on `(customer_id, revenue DESC)` is crucial for optimizing the window function's sort and partition steps, improving performance.
✗ Assuming the window function itself is the sole performance bottleneck for slow queries.
Fix — Often, the underlying sort operation for `PARTITION BY` and `ORDER BY` dominates; optimize this with correct indexing rather than just the window function.
✗ Incorrectly specifying the `DECIMAL` precision and scale for the `revenue` column.
Fix — Ensure `DECIMAL(10,2)` or a similar appropriate type matches your data to prevent truncation, overflow, or unexpected behavior during calculations.

Frequently asked questions

Q.Can I use this approach if I need to retrieve the *bottom* N orders instead?

Yes, simply change the ORDER BY revenue DESC to ORDER BY revenue ASC within the ROW_NUMBER() window function. The rest of the query structure for partitioning and filtering remains the same, retrieving the lowest revenue orders.

Q.What if multiple orders have the exact same revenue for a customer?

ROW_NUMBER() assigns an arbitrary but consistent unique rank among tied rows. If you need tied rows to share a rank, RANK() or DENSE_RANK() would be more suitable alternatives, depending on the exact tie-breaking logic desired.

Q.Will this query perform well on a table with billions of rows?

Performance on very large tables depends heavily on proper indexing. Without an optimal index on customer_id and revenue, the sorting operation required for partitioning will become a significant bottleneck. With a suitable index, it can perform efficiently.

Q.Can I retrieve different top N values for different customers?

Not directly with a single ROW_NUMBER() call filtering rn <= {{top_n_orders}}. You would need a more complex query, potentially involving a join to a lookup table that specifies a custom N value for each customer, or a CASE statement within the filter.

Q.Is `ROW_NUMBER()` specific to PostgreSQL?

No, ROW_NUMBER() is a standard SQL window function. It is widely supported across most modern relational database systems, including MySQL (8.0+), SQL Server, Oracle, and SQLite (3.25+), ensuring broad portability.

Q.How does the `PARTITION BY` clause work here?

PARTITION BY customer_id divides the dataset into independent groups, one for each distinct customer. ROW_NUMBER() then restarts its count from 1 for each new customer group, allowing per-customer ranking.

Q.Why is `revenue` specified as `DECIMAL(10,2)`?

Using DECIMAL(10,2) ensures exact precision for financial values. This prevents potential rounding errors or inaccuracies that can occur with floating-point types (like FLOAT or DOUBLE) during comparisons, sorting, and aggregations, ensuring data integrity.

Version 1.0Last reviewed July 20, 2026

Reviewed by PromptInFlow Editorial Team