[Solved] Given the table sales with columns region and amount, what will this query return?SELECT region, amount, FIRST_VALUE(amount) OVER (PARTITION BY region ORDER BY amount DESC) AS max_amount FROM sales; | SQL

SQL - Advanced Window Functions

Given the table sales with columns region and amount, what will this query return?

SELECT region, amount, FIRST_VALUE(amount) OVER (PARTITION BY region ORDER BY amount DESC) AS max_amount FROM sales;

AThe first amount in the whole table regardless of region

BThe lowest amount per region repeated on each row of that region

CThe highest amount per region repeated on each row of that region

DSyntax error due to missing frame clause

Step-by-Step Solution

Solution:

Step 1: Analyze PARTITION BY and ORDER BY
The window partitions rows by region and orders amounts descending within each region.
Step 2: Understand FIRST_VALUE with ordering
FIRST_VALUE picks the first row's amount in each partition ordered by amount DESC, which is the max amount.
Final Answer:
The highest amount per region repeated on each row of that region -> Option C
Quick Check:
FIRST_VALUE with DESC order gives max per partition [OK]

Quick Trick: Use ORDER BY DESC with FIRST_VALUE for max per group [OK]

Common Mistakes:

Master "Advanced Window Functions" in SQL

9 interactive learning modes - each teaches the same concept differently

More SQL Quizzes

Given the table sales with columns region and amount, what will this query return?