You have a table named 'PRODUCT SALES' with columns 'product id', 'sale date', 'region', and 'sales_amount'. You need to create a query that returns the top 3 products by total sales amount for each r

Question

Exhibit

Options

• AOption A
• BOption B
• COption C
• DOption D
• EOption E

• A
  19% (8)
• B
  7% (3)
• C
  5% (2)
• D
  2% (1)
• E
  67% (29)

Explanation

The actual SQL code for options A–E wasn't included in your message - only the letter labels were provided. Without seeing the query text, I can't explain specifically why each distractor fails.

That said, here's what you need to know to answer this type of question correctly on any exam:

What a correct answer must do:

1. Aggregate first - SUM(sales_amount) grouped by product_id and region
2. Rank within each region - using RANK() or ROW_NUMBER() with PARTITION BY region ORDER BY total_sales DESC
3. Filter to top 3 - wrap in a CTE or subquery, then WHERE rank <= 3 (you can't filter on a window function alias in the same WHERE clause)

WITH ranked AS (
  SELECT product_id, region,
         SUM(sales_amount) AS total_sales,
         RANK() OVER (PARTITION BY region ORDER BY SUM(sales_amount) DESC) AS rnk
  FROM "PRODUCT SALES"
  GROUP BY product_id, region
)
SELECT product_id, region, total_sales
FROM ranked
WHERE rnk <= 3;

Common distractor patterns to watch for:

• Missing PARTITION BY (ranks globally, not per region)
• Missing GROUP BY before the window function (no aggregation)
• Filtering WHERE rank <= 3 in the same query level where rank is defined (not allowed)
• Using NTILE(3) instead of RANK() (divides into thirds, doesn't return "top 3")

Memory tip: Think "CTE → aggregate → partition → filter" - you always need a wrapper query to filter on a window function result.

If you paste the actual query options, I can give you a precise breakdown of each.

No community discussion yet for this question.