SQL MIN and MAX

-- Simplest case
SELECT MIN(price) AS cheapest, MAX(price) AS most_expensive
FROM products;

-- With GROUP BY
SELECT category, MIN(price), MAX(price)
FROM products
GROUP BY category;

-- Strings - alphabetical order
SELECT MIN(name), MAX(name)
FROM users;

-- Dates
SELECT MIN(signup_date) AS first_user_at, MAX(signup_date) AS last_user_at
FROM users;

-- NULL is ignored by MIN/MAX (and COUNT(col))
SELECT MIN(price), MAX(price)
FROM products
WHERE price IS NOT NULL;  -- the IS NOT NULL is redundant for MIN/MAX

-- Combined with subqueries - 'most recent X per user'
SELECT user_id, MAX(created_at) AS last_order_at
FROM orders
GROUP BY user_id;

-- Get the row, not just the value (window function)
SELECT *
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders o
) latest
WHERE rn = 1;

-- Date arithmetic
SELECT
  user_id,
  MIN(created_at) AS first_at,
  MAX(created_at) AS last_at,
  MAX(created_at) - MIN(created_at) AS tenure
FROM orders
GROUP BY user_id;

-- HAVING filters AFTER aggregation
SELECT user_id, MAX(total) AS biggest_order
FROM orders
GROUP BY user_id
HAVING MAX(total) > 1000;       -- biggest order over $1000

-- GREATEST / LEAST work across columns of a single row (Postgres)
SELECT GREATEST(price, sale_price) AS effective_price FROM products;

SELECT MIN(price) AS cheapest,
       MAX(price) AS dearest
FROM products;