SQL

-- Basic select
SELECT * FROM users;
SELECT id, name, email FROM users;

-- Filter
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE name LIKE 'A%';
SELECT * FROM users WHERE status IN ('active', 'trial');
SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- Sort & limit
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
SELECT * FROM users ORDER BY name ASC NULLS LAST;

-- Distinct
SELECT DISTINCT country FROM users;

-- Insert
INSERT INTO users (name, email, age) VALUES ('Alice', '[email protected]', 30);

-- Insert multiple rows
INSERT INTO users (name, email) VALUES
  ('Bob', '[email protected]'),
  ('Carol', '[email protected]');

-- Update
UPDATE users SET status = 'inactive' WHERE last_login < NOW() - INTERVAL '90 days';

-- Delete
DELETE FROM users WHERE id = 42;

-- Upsert (PostgreSQL)
INSERT INTO users (email, name) VALUES ('[email protected]', 'Alice')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

-- INNER JOIN: rows matching in both tables
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN: all left rows, NULLs for unmatched right
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Multiple joins
SELECT u.name, p.name AS product, o.created_at
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN products p ON p.id = o.product_id;

-- Aggregate functions
SELECT
  COUNT(*)                AS total,
  COUNT(DISTINCT user_id) AS unique_users,
  SUM(amount)             AS revenue,
  AVG(amount)             AS avg_order,
  MAX(amount)             AS largest,
  MIN(created_at)         AS first_order
FROM orders
WHERE status = 'completed';

-- GROUP BY
SELECT country, COUNT(*) AS users
FROM users
GROUP BY country
HAVING COUNT(*) > 100
ORDER BY users DESC;

-- ROW_NUMBER, RANK
SELECT
  name,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank,
  RANK()       OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank
FROM employees;

-- Running total
SELECT
  created_at::date AS day,
  SUM(amount) AS daily,
  SUM(SUM(amount)) OVER (ORDER BY created_at::date) AS running_total
FROM orders
GROUP BY day;

-- LAG / LEAD
SELECT date, revenue,
  LAG(revenue) OVER (ORDER BY date) AS prev_day
FROM daily_revenue;