-- Created on savesnippets.com · https://savesnippets.com/8DPKPt0lqfNLmc
-- Every user, with how many orders they have (zero if none)
SELECT u.id,
       u.name,
       COUNT(o.id) AS order_count    -- COUNT(o.id) ignores NULLs from no-match
FROM   users u
LEFT   JOIN orders o ON o.user_id = u.id
GROUP  BY u.id, u.name
ORDER  BY order_count DESC;

-- Anti-join: users who have NEVER placed an order
SELECT u.*
FROM   users u
LEFT   JOIN orders o ON o.user_id = u.id
WHERE  o.id IS NULL;     -- rows where the LEFT JOIN found nothing

-- The same anti-join, written more readably as NOT EXISTS
SELECT u.* FROM users u
WHERE  NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- COALESCE the NULLs from missing right-side rows to a default
SELECT u.id, u.name, COALESCE(o.amount, 0) AS last_order_amount
FROM   users u
LEFT   JOIN LATERAL (
  SELECT amount FROM orders WHERE user_id = u.id
  ORDER BY created_at DESC LIMIT 1
) o ON TRUE;