Workspaces With Every Add-on

A B2B project-management tool sells optional add-ons to customer workspaces. Marketing wants to thank the workspaces that own the complete current add-on catalog.

Return every workspace that has purchased each add-on that is not retired (is_retired = 0) at least once. Purchases of retired add-ons neither help nor hurt, and buying the same add-on several times still counts once. The catalog always contains at least one add-on that is not retired.

Output columns: workspace_id, company. Any row order. Return no rows if no workspace qualifies.

Tables

workspaces — One row per customer workspace.

add_ons — The add-on catalog, including retired items.

addon_purchases — One row per purchase; a workspace can buy the same add-on more than once (renewals).

Example

workspaces

add_ons

addon_purchases

Expected output

Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Product Analytics · problem 64 of 70 · SQLite

The idea

This is relational division: find the workspaces related to all rows of another set.

Counting. Join purchases to the catalog, keep only current add-ons, group by workspace and compare COUNT(DISTINCT addon_id) with the number of current add-ons. DISTINCT matters because renewals repeat an add-on, and the retired filter matters because a retired purchase would otherwise pad the count. Because only current add-on ids are counted and each one is counted once, reaching the catalog size means none are missing.

Double negation. "Bought every current add-on" is the same as "there is no current add-on it did not buy". Two nested NOT EXISTS say exactly that and need no counting at all, so duplicates and retired items cannot interfere.

The two differ on an empty catalog: double NOT EXISTS would return every workspace (vacuously true), the count version only workspaces with purchases. The statement rules that case out, but say so in an interview.

Solution

SELECT w.workspace_id, w.company
FROM workspaces AS w
JOIN addon_purchases AS p ON p.workspace_id = w.workspace_id
JOIN add_ons AS a ON a.addon_id = p.addon_id
WHERE a.is_retired = 0
GROUP BY w.workspace_id, w.company
HAVING COUNT(DISTINCT p.addon_id) = (
  SELECT COUNT(*) FROM add_ons WHERE is_retired = 0
);

Another way

-- keep a workspace if no current add-on is missing from its purchases
SELECT w.workspace_id, w.company
FROM workspaces AS w
WHERE NOT EXISTS (
  SELECT 1
  FROM add_ons AS a
  WHERE a.is_retired = 0
    AND NOT EXISTS (
      SELECT 1
      FROM addon_purchases AS p
      WHERE p.workspace_id = w.workspace_id
        AND p.addon_id = a.addon_id
    )
);

Why common answers fail

Counts purchases instead of distinct add-ons, so renewals of one add-on can stand in for a missing one (and owning everything plus a renewal fails).

SELECT w.workspace_id, w.company
FROM workspaces w
JOIN addon_purchases p ON p.workspace_id = w.workspace_id
JOIN add_ons a ON a.addon_id = p.addon_id
WHERE a.is_retired = 0
GROUP BY w.workspace_id, w.company
HAVING COUNT(p.addon_id) = (SELECT COUNT(*) FROM add_ons WHERE is_retired = 0);

Does not exclude retired add-ons from the purchases, so a retired purchase can make up for a missing current one.

SELECT w.workspace_id, w.company
FROM workspaces w
JOIN addon_purchases p ON p.workspace_id = w.workspace_id
GROUP BY w.workspace_id, w.company
HAVING COUNT(DISTINCT p.addon_id) = (SELECT COUNT(*) FROM add_ons WHERE is_retired = 0);

Compares against the whole catalog including retired add-ons, so it demands items nobody can buy any more.

SELECT w.workspace_id, w.company
FROM workspaces w
JOIN addon_purchases p ON p.workspace_id = w.workspace_id
GROUP BY w.workspace_id, w.company
HAVING COUNT(DISTINCT p.addon_id) = (SELECT COUNT(*) FROM add_ons);

What the interviewer is checking

A classic test of set thinking: can you express "for all" in SQL, either by counting distinct matches or by double NOT EXISTS, without tripping on duplicates.

Mistakes to avoid

• COUNT instead of COUNT(DISTINCT), so repeat purchases inflate the count.
• Forgetting to drop retired add-ons on one side of the comparison.
• Writing IN (list of add-ons), which finds workspaces with any add-on, not all of them.

Follow-ups to expect

• Workspaces missing exactly one add-on, and which one (count = total - 1, then anti-join).
• Only count purchases made in the last 12 months (add a date filter inside the join).
• Per add-on, the share of workspaces that own it (GROUP BY addon, divide by workspace count).

In real work

"Customers who own every product in a bundle", "students who passed all required courses" and "stores that stock the full planogram" are the same division query.

Practice next