Question

You want to find the first and last sale amount for each customer ordered by sale date. Which query correctly returns both values per customer in PostgreSQL?

Accepted Answer

SELECT customer_id, sale_date, sale_amount, FIRST_VALUE(sale_amount) OVER (PARTITION BY customer_id ORDER BY sale_date), LAST_VALUE(sale_amount) OVER (PARTITION BY customer_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM sales;

Answer

SELECT customer_id, sale_date, sale_amount, FIRST_VALUE(sale_amount), LAST_VALUE(sale_amount) FROM sales GROUP BY customer_id;

Answer

SELECT customer_id, sale_date, sale_amount, FIRST_VALUE(sale_amount) OVER (ORDER BY sale_date), LAST_VALUE(sale_amount) OVER (ORDER BY sale_date) FROM sales;

Answer

SELECT customer_id, sale_date, sale_amount, MIN(sale_amount) OVER (PARTITION BY customer_id), MAX(sale_amount) OVER (PARTITION BY customer_id) FROM sales;

You want to find the first and last sale amount for each customer ordered by sale date. Which query correctly returns both values per customer in PostgreSQL?

Step 1: Use FIRST_VALUE with PARTITION and ORDER BY

Step 2: Use LAST_VALUE with full window frame

Step 3: Check other options

Final Answer:

Quick Check:

Want More Practice?