Question

You want to find the first and last sale amounts per customer ordered by sale date from the sales table. Which query correctly returns both values on each row?

Accepted Answer

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

Answer

SELECT customer_id, sale_date, FIRST_VALUE(amount) OVER (ORDER BY sale_date) AS first_sale, LAST_VALUE(amount) OVER (ORDER BY sale_date) AS last_sale FROM sales;

Answer

SELECT customer_id, sale_date, FIRST_VALUE(amount) AS first_sale, LAST_VALUE(amount) AS last_sale FROM sales GROUP BY customer_id;

Answer

SELECT customer_id, sale_date, MIN(amount) OVER (PARTITION BY customer_id) AS first_sale, MAX(amount) OVER (PARTITION BY customer_id) AS last_sale FROM sales;

You want to find the first and last sale amounts per customer ordered by sale date from the sales table. Which query correctly returns both values on each row?

Step 1: Use PARTITION BY customer_id

Step 2: Use ORDER BY sale_date for chronological order

Step 3: Add frame clause for LAST_VALUE

Final Answer:

Quick Check:

Want More Practice?