JOINs & Aggregates

-- Inner join with aggregate
SELECT u.username, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id
ORDER BY post_count DESC;

-- Filter groups with HAVING
SELECT user_id, AVG(published) AS publish_rate
FROM posts
GROUP BY user_id
HAVING publish_rate < 0.5;

-- Self join for hierarchies
CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  name TEXT,
  parent_id INTEGER REFERENCES categories(id)
);

SELECT c.name AS child, p.name AS parent
FROM categories c
LEFT JOIN categories p ON c.parent_id = p.id;