Distinct Companies Applied To | Dataford Interview Questions

FNZ's candidate reporting workflow needs an overview of application reach. Write a PostgreSQL query that reports how many distinct companies each candidate has applied to.

Requirements

Return every candidate, including candidates with no matching report rows.
Count each company at most once per candidate, even when duplicate application reports exist.
Exclude reports whose company name is NULL from the distinct count.
Sort by the count descending, then by candidate_id ascending to make ties deterministic.

Schema

candidates

Column	Type	Description
candidate_idPK	INT	Unique candidate identifier
candidate_name	VARCHAR(100)	Candidate's full name
email	VARCHAR(255)	Candidate email address

reports

Column	Type	Description
report_idPK	INT	Unique application report identifier
candidate_id	INT	Candidate associated with the report
company_name	VARCHAR(150)	Company applied to, when known

Your question is Distinct Companies Applied To. Start with the requirements and the two 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