Meta: Verified WhatsApp Businesses With No Live Catalog Items
The WhatsApp Business partnerships team wants to nudge verified businesses that are not yet selling through their catalog.
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #749
Problem statement
The WhatsApp Business partnerships team wants to nudge verified businesses that are not yet selling through their catalog.
Return every business that is verified and has no active catalog item — a business whose items are all hidden (is_active = 0) counts as having none. Unverified businesses are never in the answer. Return the columns business_id and business_name, in any order.
A practice scenario set at Meta: the tables and data are invented, and this is not a question from, or endorsed by, Meta.
Tables
Table: WaBusiness
| Column | Type |
|---|---|
| business_id | int |
| business_name | varchar |
| city | varchar |
| verified | bool |
Primary key: business_id.
One row per WhatsApp Business account. verified is 1 once the business passed verification.
Table: CatalogItem
| Column | Type |
|---|---|
| item_id | int |
| business_id | int |
| item_name | varchar |
| is_active | bool |
Primary key: item_id.
One row per product a business added to its catalog. is_active is 0 when the business hid the item; business_id always names a business.
Examples
Example 1
WaBusiness
| business_id | business_name | city | verified |
|---|---|---|---|
| 1 | Chai Point Express | Bengaluru | 1 |
| 2 | Rangoli Sarees | Jaipur | 1 |
| 3 | Mumbai Mobile Fix | Mumbai | 0 |
| 4 | Green Leaf Organics | Pune | 1 |
| 5 | Kochi Spice Traders | Kochi | 1 |
CatalogItem
| item_id | business_id | item_name | is_active |
|---|---|---|---|
| 101 | 1 | Masala Chai Flask | 1 |
| 102 | 2 | Bandhani Saree | 0 |
| 103 | 2 | Leheriya Dupatta | 0 |
| 104 | 4 | Cold-Pressed Oil | 1 |
| 105 | 3 | Screen Guard | 1 |
| 106 | 4 | Millet Muesli | 0 |
Output
| business_id | business_name |
|---|---|
| 2 | Rangoli Sarees |
| 5 | Kochi Spice Traders |
How to solve Meta: Verified WhatsApp Businesses With No Live Catalog Items
The question is about absence: a verified business with no active item. The standard tool is an anti join. With a LEFT JOIN, every business keeps at least one row; when nothing on the right matches, the item columns come back NULL, and c.item_id IS NULL keeps exactly those businesses.
Where the is_active = 1 condition sits is the whole problem. In the ON clause it limits which items may match, so a business whose only items are hidden finds no match and is kept. Moved into WHERE, it would run after the join and throw away the NULL rows too, so the businesses with no items at all would vanish — the opposite of what is asked.
NOT EXISTS with the same condition inside the subquery says it most directly, and NOT IN works here because CatalogItem.business_id is never NULL (a NULL in a NOT IN list would make every test unknown). All three read each table once with an index on CatalogItem.business_id.
Reference solution (MySQL)
SELECT b.business_id, b.business_name
FROM WaBusiness b
LEFT JOIN CatalogItem c ON c.business_id = b.business_id AND c.is_active = 1
WHERE b.verified = 1
AND c.item_id IS NULLAnother way
SELECT b.business_id, b.business_name FROM WaBusiness b WHERE b.verified = 1 AND NOT EXISTS (SELECT 1 FROM CatalogItem c WHERE c.business_id = b.business_id AND c.is_active = 1)Another way
SELECT business_id, business_name FROM WaBusiness WHERE verified = 1 AND business_id NOT IN (SELECT business_id FROM CatalogItem WHERE is_active = 1)← Meta: Marketplace Listings Sold Within a Day · Meta: Going and Interested Counts per Facebook Event →