You want to find the first and last sale amounts for each product ordered by sale date. Which query correctly uses FIRST_VALUE and LAST_VALUE to achieve this?
hard📝 Application Q8 of 15
PostgreSQL - Window Functions in PostgreSQL
You want to find the first and last sale amounts for each product ordered by sale date. Which query correctly uses FIRST_VALUE and LAST_VALUE to achieve this?
ASELECT product_id, FIRST_VALUE(amount) OVER (PARTITION BY product_id ORDER BY sale_date), LAST_VALUE(amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM sales;
BSELECT product_id, FIRST_VALUE(amount) OVER (ORDER BY sale_date), LAST_VALUE(amount) OVER (ORDER BY sale_date) FROM sales;
CSELECT product_id, FIRST_VALUE(amount), LAST_VALUE(amount) FROM sales GROUP BY product_id;
DSELECT product_id, MIN(amount), MAX(amount) FROM sales GROUP BY product_id;
Step-by-Step Solution
Solution:
Step 1: Use PARTITION BY product_id and ORDER BY sale_date
To get first and last sale amounts per product, partition by product and order by sale date.
Step 2: Use frame clause for LAST_VALUE
LAST_VALUE needs frame ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get last value in partition.
Step 3: Evaluate options
SELECT product_id, FIRST_VALUE(amount) OVER (PARTITION BY product_id ORDER BY sale_date), LAST_VALUE(amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM sales; correctly applies both functions with proper partition, order, and frame. Others miss partition or frame or use aggregates.
Final Answer:
SELECT product_id, FIRST_VALUE(amount) OVER (PARTITION BY product_id ORDER BY sale_date), LAST_VALUE(amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM sales; -> Option A
Quick Check:
LAST_VALUE needs full frame for last row [OK]
Quick Trick:Use full frame for LAST_VALUE to get true last value [OK]
Common Mistakes:
Omitting PARTITION BY
Not specifying frame for LAST_VALUE
Using aggregates instead of window functions
Master "Window Functions in PostgreSQL" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
