Meta: Verified WhatsApp Businesses With No Live Catalog Items

Easy
749.

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.

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

ColumnType
business_idint
business_namevarchar
cityvarchar
verifiedbool

Primary key: business_id.

One row per WhatsApp Business account. verified is 1 once the business passed verification.

Table: CatalogItem

ColumnType
item_idint
business_idint
item_namevarchar
is_activebool

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.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

WaBusiness

business_idbusiness_namecityverified
1Chai Point ExpressBengaluru1
2Rangoli SareesJaipur1
3Mumbai Mobile FixMumbai0
4Green Leaf OrganicsPune1
5Kochi Spice TradersKochi1

CatalogItem

item_idbusiness_iditem_nameis_active
1011Masala Chai Flask1
1022Bandhani Saree0
1032Leheriya Dupatta0
1044Cold-Pressed Oil1
1053Screen Guard1
1064Millet Muesli0

Output

business_idbusiness_name
2Rangoli Sarees
5Kochi Spice Traders
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
business_idint
business_namevarchar
cityvarchar
verifiedbool

Primary key: business_id.

One row per WhatsApp Business account. verified is 1 once the business passed verification.

Table: CatalogItem

ColumnType
item_idint
business_idint
item_namevarchar
is_activebool

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_idbusiness_namecityverified
1Chai Point ExpressBengaluru1
2Rangoli SareesJaipur1
3Mumbai Mobile FixMumbai0
4Green Leaf OrganicsPune1
5Kochi Spice TradersKochi1

CatalogItem

item_idbusiness_iditem_nameis_active
1011Masala Chai Flask1
1022Bandhani Saree0
1032Leheriya Dupatta0
1044Cold-Pressed Oil1
1053Screen Guard1
1064Millet Muesli0

Output

business_idbusiness_name
2Rangoli Sarees
5Kochi 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 NULL

Another 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 →