SQL SELECT

-- Pick specific columns (preferred over SELECT *)
SELECT id, name, email
FROM users;

-- All columns (use sparingly in production code)
SELECT *
FROM users;

-- Filter with WHERE
SELECT id, name
FROM users
WHERE country = 'AU' AND active = TRUE;

-- Aliases for clarity
SELECT
  u.id        AS user_id,
  u.email     AS contact,
  o.total     AS amount
FROM users u
JOIN orders o ON o.user_id = u.id;

-- Sort + limit
SELECT id, name, signup_date
FROM users
ORDER BY signup_date DESC
LIMIT 10;

-- Group + aggregate
SELECT country, COUNT(*) AS users
FROM users
GROUP BY country
ORDER BY users DESC;

-- Subquery in WHERE
SELECT id, email
FROM users
WHERE id IN (
  SELECT DISTINCT user_id
  FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
);

-- CTE (common table expression) - cleaner than nested subqueries
WITH recent_buyers AS (
  SELECT user_id
  FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT u.id, u.email
FROM users u
JOIN recent_buyers rb ON rb.user_id = u.id;

-- Window function - rank without collapsing rows
SELECT
  id, total,
  RANK() OVER (PARTITION BY user_id ORDER BY total DESC) AS rk
FROM orders;

SELECT id, name, email
FROM customers
WHERE active = 1
ORDER BY name ASC;