snippets

WITH active_users AS (
    SELECT id, email
    FROM users
    WHERE last_seen > NOW() - INTERVAL '30 days'
)
SELECT a.email, COUNT(o.id) AS orders
FROM active_users a
LEFT JOIN orders o ON o.user_id = a.id
GROUP BY a.email;