Accounts With Zero Contacts | Dataford Interview Questions

Write a SQL query that returns the account name and the number of contacts associated with it, and include the names of accounts with no contacts.

Use the provided account, contact, and account-contact association tables. Count each contact once per account, even if duplicate association records exist.

Output

One row per account, with account_name and contact_count.
Include accounts with zero associated contacts.
Sort by account_name ascending, with account_id as the tie-breaker.

Schema

accounts

Column	Type	Description
account_idPK	INT	Unique account identifier
account_name	VARCHAR(150)	Account name

contacts

Column	Type	Description
contact_idPK	INT	Unique contact identifier
first_name	VARCHAR(80)	Contact first name
last_name	VARCHAR(80)	Contact last name
email	VARCHAR(255)	Contact email address

account_contacts

Column	Type	Description
account_contact_idPK	INT	Unique association identifier
account_id	INT	Associated account identifier
contact_id	INT	Associated contact identifier

Your question is Accounts With Zero Contacts. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem