Saltar al contenido principal

Fix the Zero-Activity Account Count

Use LEFT JOIN, ON filters, and COUNT(right.id) to count optional activity correctly.

sql-basics5 min read

List every active account and the number of logins in the last 14 days, including accounts with zero logins. LEFT JOIN preserves accounts; ON filters qualifying activity; COUNT(activity.id) counts matches. Putting the login date filter in WHERE removes accounts whose login columns are NULL after the LEFT JOIN. Using COUNT(*) then misstates no-login accounts as one row. Before FROM accounts a LEFT JOIN logins l ON l.account_id = a.id WHERE l.logged_at >= CURRENT_DATE - INTERVAL '14 days' GROUP BY a.id; -- no-login accounts vanish. After FROM accounts a LEFT JOIN logins l ON l.account_id = a.id AND l.logged_at >= CURRENT_DATE…

Read the full lesson

Sign up free: one personalized lesson every day, matched to your role and goals.

← Back to library

Contáctanos