Pick the best contact channel for every customer

EasyNULL / NOT NULL HandlingZomatoEXL

The CRM team is preparing a re-engagement campaign. Each contact may have an email, a phone number, both, or neither, and some records also have no city captured.

From crm_contacts, return contact_id, full_name, city and a new column primary_contact for every contact whose city is known. primary_contact should be the email when present, otherwise the phone number, otherwise the text NO CONTACT DETAILS. Order by contact_id.

Sample data

Table: crm_contacts

12 row(s) · showing first 8

contact_idINT	full_nameTEXT	emailTEXT	phoneTEXT	cityTEXT
1	Aarav Mehta	aarav@example.in	9810011122	Delhi
2	Bhavna Rao	NULL	9820033344	Mumbai
3	Chirag Jain	chirag@example.in	NULL	Jaipur
4	Divya Nair	NULL	NULL	Kochi
5	Esha Kulkarni	esha@example.in	9845055566	NULL
6	Farhan Qureshi	NULL	9830077788	Kolkata
7	Gauri Desai	gauri@example.in	NULL	Pune
8	Harsh Vora	NULL	NULL	NULL

Step 1: `city IS NOT NULL` is the only correct way to test for missing values; `city <> NULL` never returns true.
Step 2: `COALESCE` returns its first non-NULL argument, so listing email, phone and the fallback text gives the required priority order.
Step 3: Contacts 5 and 8 are dropped because their city is NULL; contacts 4 and 12 survive and show the fallback label.

SELECT contact_id,
       full_name,
       city,
       COALESCE(email, phone, 'NO CONTACT DETAILS') AS primary_contact
FROM crm_contacts
WHERE city IS NOT NULL
ORDER BY contact_id;

More NULL / NOT NULL Handling questions

Asked at Zomato

Practising alone only goes so far — our live cohorts pair SQL depth with dashboards, agentic analytics projects and mock interviews.

Explore the program