Audit CRM Data Quality Flags

Task

You are given CRM data used by a large sales team. Write a PostgreSQL query that returns each active account with a data-quality status showing whether it has missing owner assignment, duplicate contacts, or unresolved email conflicts. Focus on surfacing records that need cleanup rather than fixing them.

Schema

Table	Column	Type	Description
`accounts`	`account_id`	`INT`	Primary key for the account
`accounts`	`account_name`	`VARCHAR(255)`	Account name
`accounts`	`owner_id`	`INT`	Assigned CRM owner
`accounts`	`is_active`	`BOOLEAN`	Whether the account is active
`contacts`	`contact_id`	`INT`	Primary key for the contact
`contacts`	`account_id`	`INT`	Related account
`contacts`	`email`	`VARCHAR(255)`	Contact email address
`contacts`	`is_primary`	`BOOLEAN`	Primary contact flag
`crm_users`	`user_id`	`INT`	Primary key for the CRM user
`crm_users`	`full_name`	`VARCHAR(255)`	Owner name

Sample data

accounts

account_id	account_name	owner_id	is_active
1	Northwind	10	true
2	Alpine Labs	null	true
3	Meridian Co	12	true
4	Orbit Retail	99	true
5	Vector Health	11	false

contacts

contact_id	account_id	email	is_primary
101	1	a@northwind.com	true
102	1	a@northwind.com	false
103	2	null	true
104	3	ops@meridian.co	true
105	3	sales@meridian.co	false
106	4	billing@orbit.com	true
107	4	billing@orbit.com	true
108	5	legacy@vectorhealth.com	true

crm_users

user_id	full_name
10	Priya Shah
11	Marco Diaz
12	Elena Chen

Expected output

account_id	account_name	owner_status	contact_status
1	Northwind	assigned	duplicate_contact_email
2	Alpine Labs	missing_owner	missing_contact_email
3	Meridian Co	assigned	clean
4	Orbit Retail	invalid_owner	duplicate_primary_contact

Problem

Task

Schema

Table	Column	Type	Description
`accounts`	`account_id`	`INT`	Primary key for the account
`accounts`	`account_name`	`VARCHAR(255)`	Account name
`accounts`	`owner_id`	`INT`	Assigned CRM owner
`accounts`	`is_active`	`BOOLEAN`	Whether the account is active
`contacts`	`contact_id`	`INT`	Primary key for the contact
`contacts`	`account_id`	`INT`	Related account
`contacts`	`email`	`VARCHAR(255)`	Contact email address
`contacts`	`is_primary`	`BOOLEAN`	Primary contact flag
`crm_users`	`user_id`	`INT`	Primary key for the CRM user
`crm_users`	`full_name`	`VARCHAR(255)`	Owner name

Sample data

accounts

account_id	account_name	owner_id	is_active
1	Northwind	10	true
2	Alpine Labs	null	true
3	Meridian Co	12	true
4	Orbit Retail	99	true
5	Vector Health	11	false

contacts

contact_id	account_id	email	is_primary
101	1	a@northwind.com	true
102	1	a@northwind.com	false
103	2	null	true
104	3	ops@meridian.co	true
105	3	sales@meridian.co	false
106	4	billing@orbit.com	true
107	4	billing@orbit.com	true
108	5	legacy@vectorhealth.com	true

crm_users

user_id	full_name
10	Priya Shah
11	Marco Diaz
12	Elena Chen

Expected output

account_id	account_name	owner_status	contact_status
1	Northwind	assigned	duplicate_contact_email
2	Alpine Labs	missing_owner	missing_contact_email
3	Meridian Co	assigned	clean
4	Orbit Retail	invalid_owner	duplicate_primary_contact

Problem

Task

Schema

Table	Column	Type	Description
`accounts`	`account_id`	`INT`	Primary key for the account
`accounts`	`account_name`	`VARCHAR(255)`	Account name
`accounts`	`owner_id`	`INT`	Assigned CRM owner
`accounts`	`is_active`	`BOOLEAN`	Whether the account is active
`contacts`	`contact_id`	`INT`	Primary key for the contact
`contacts`	`account_id`	`INT`	Related account
`contacts`	`email`	`VARCHAR(255)`	Contact email address
`contacts`	`is_primary`	`BOOLEAN`	Primary contact flag
`crm_users`	`user_id`	`INT`	Primary key for the CRM user
`crm_users`	`full_name`	`VARCHAR(255)`	Owner name

Sample data

accounts

account_id	account_name	owner_id	is_active
1	Northwind	10	true
2	Alpine Labs	null	true
3	Meridian Co	12	true
4	Orbit Retail	99	true
5	Vector Health	11	false

contacts

contact_id	account_id	email	is_primary
101	1	a@northwind.com	true
102	1	a@northwind.com	false
103	2	null	true
104	3	ops@meridian.co	true
105	3	sales@meridian.co	false
106	4	billing@orbit.com	true
107	4	billing@orbit.com	true
108	5	legacy@vectorhealth.com	true

crm_users

user_id	full_name
10	Priya Shah
11	Marco Diaz
12	Elena Chen

Expected output

account_id	account_name	owner_status	contact_status
1	Northwind	assigned	duplicate_contact_email
2	Alpine Labs	missing_owner	missing_contact_email
3	Meridian Co	assigned	clean
4	Orbit Retail	invalid_owner	duplicate_primary_contact

Problem

Task

Schema

Table	Column	Type	Description
`accounts`	`account_id`	`INT`	Primary key for the account
`accounts`	`account_name`	`VARCHAR(255)`	Account name
`accounts`	`owner_id`	`INT`	Assigned CRM owner
`accounts`	`is_active`	`BOOLEAN`	Whether the account is active
`contacts`	`contact_id`	`INT`	Primary key for the contact
`contacts`	`account_id`	`INT`	Related account
`contacts`	`email`	`VARCHAR(255)`	Contact email address
`contacts`	`is_primary`	`BOOLEAN`	Primary contact flag
`crm_users`	`user_id`	`INT`	Primary key for the CRM user
`crm_users`	`full_name`	`VARCHAR(255)`	Owner name

Sample data

accounts

account_id	account_name	owner_id	is_active
1	Northwind	10	true
2	Alpine Labs	null	true
3	Meridian Co	12	true
4	Orbit Retail	99	true
5	Vector Health	11	false

contacts

contact_id	account_id	email	is_primary
101	1	a@northwind.com	true
102	1	a@northwind.com	false
103	2	null	true
104	3	ops@meridian.co	true
105	3	sales@meridian.co	false
106	4	billing@orbit.com	true
107	4	billing@orbit.com	true
108	5	legacy@vectorhealth.com	true

crm_users

user_id	full_name
10	Priya Shah
11	Marco Diaz
12	Elena Chen

Expected output

account_id	account_name	owner_status	contact_status
1	Northwind	assigned	duplicate_contact_email
2	Alpine Labs	missing_owner	missing_contact_email
3	Meridian Co	assigned	clean
4	Orbit Retail	invalid_owner	duplicate_primary_contact

Interview Guides

Problem

Task

Schema

Sample data

Expected output

Problem

Task

Schema

Sample data

Expected output

Audit CRM Data Quality Flags

Problem

Task

Schema

Sample data

Expected output

Problem

Task

Schema

Sample data

Expected output