Got it, let's fix this up properly. Your original query has a couple of critical issues (like a typo costomer instead of customer, plus incorrect grouping that mixes customers across areas) on top of being overly nested and hard to read. Here's a clean, efficient approach that gets the exact result you need:

Simplified Query #

WITH customer_area_totals AS (
    -- Calculate total cost per customer per area first
    SELECT
        area,
        customer,
        SUM(cost) AS total_cost
    FROM your_table
    GROUP BY area, customer
),
ranked_customers AS (
    -- Rank customers within each area by their total cost (descending)
    SELECT
        area,
        customer,
        total_cost,
        ROW_NUMBER() OVER(PARTITION BY area ORDER BY total_cost DESC) AS rank
    FROM customer_area_totals
)
-- Join back to original table to get all properties for top 2 customers per area
SELECT
    t.area,
    t.customer,
    t.property,
    rc.total_cost AS cost
FROM your_table t
INNER JOIN ranked_customers rc
    ON t.area = rc.area
    AND t.customer = rc.customer
WHERE rc.rank <= 2
ORDER BY t.area, rc.rank, t.property;

Key Improvements & Explanations #

• Fixes Logical Errors: The original query grouped only by customer (with a typo!) instead of area, customer—this would merge costs for the same customer across different areas, which breaks the "per area" requirement. Our first CTE explicitly calculates totals per area + customer pair.
• Better Readability: Using Common Table Expressions (CTEs) breaks the logic into clear, reusable steps instead of nested subqueries. Anyone reading this can immediately follow what each part does.
• Efficient Window Function: The ROW_NUMBER() window function ranks customers within each area in one pass, avoiding the messy nested subquery chain from your original code.
• Preserves All Attributes: By joining back to the original table, we retain every property entry for the top 2 customers, which is exactly what you need.

Notes #

• Replace your_table with the actual name of your table.
• If you need to handle ties (e.g., multiple customers with the same total cost for 2nd place), swap ROW_NUMBER() with RANK() or DENSE_RANK() depending on your desired behavior.

内容的提问来源于stack exchange，提问作者Sean.H