SELECT Columns FROM

-- Select all columns (avoid in production!)
SELECT * FROM users;

-- Select specific columns (always preferred)
SELECT id, name, email, created_at FROM users;

-- Column aliases
SELECT
  id          AS user_id,
  full_name   AS name,
  email_addr  AS email
FROM users;

-- Expressions in SELECT
SELECT
  first_name || ' ' || last_name   AS full_name,
  salary * 12                       AS annual_salary,
  UPPER(email)                      AS email_upper,
  EXTRACT(YEAR FROM created_at)     AS join_year
FROM employees;

-- DISTINCT: remove duplicate rows
SELECT DISTINCT city FROM customers;
SELECT DISTINCT ON (user_id) user_id, amount FROM orders ORDER BY user_id, amount DESC;

SELECT
  u.id,
  u.username,
  u.email,
  u.created_at                                       AS member_since,
  EXTRACT(DAYS FROM NOW() - u.created_at)::INT       AS days_since_joined,
  COALESCE(u.full_name, u.username)                  AS display_name,
  COUNT(DISTINCT o.id)                               AS total_orders,
  COALESCE(SUM(o.total_amount), 0)                   AS lifetime_value,
  CASE
    WHEN SUM(o.total_amount) > 100000 THEN 'VIP'
    WHEN SUM(o.total_amount) > 10000  THEN 'Premium'
    ELSE 'Regular'
  END                                                AS customer_tier,
  MAX(o.created_at)                                  AS last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed'
WHERE u.deleted_at IS NULL
GROUP BY u.id, u.username, u.email, u.created_at, u.full_name
ORDER BY lifetime_value DESC;