Fix the Zero-Activity Account Count
Use LEFT JOIN, ON filters, and COUNT(right.id) to count optional activity correctly.
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…
Sign up free — one personalized lesson every day, matched to your role and goals.
Already have an account? Sign in