Company: Slack | Difficulty: medium
Slack's security team wants to review active channels that include people from outside the organization.
For every non-archived channel, assign an exposure label based on its number of external guests:
High Exposure
Review
Internal Only
A NULL external_guest_count should be treated as zero. Treat a NULL is_archived value as not archived. Return only non-archived channels.
NULL
external_guest_count
is_archived
Return the columns in this exact order: channel_id, channel_name, external_guest_count, and exposure_label.
channel_id
channel_name
exposure_label
Sort by external_guest_count descending, then channel_id descending.
workspace_channels
member_count
created_at
customer-launch is labeled High Exposure because it has 12 external guests. vendor-sync is labeled Review because it has 4 external guests. engineering and design-review are labeled Internal Only; the NULL value for design-review is treated as zero. old-partners is excluded because it is archived.
customer-launch
vendor-sync
engineering
design-review
old-partners
DROP TABLE IF EXISTS workspace_channels; CREATE TABLE workspace_channels ( channel_id INT, channel_name VARCHAR(80), external_guest_count INT, member_count INT, is_archived BOOLEAN, created_at TIMESTAMP ); INSERT INTO workspace_channels VALUES (1,'customer-launch',12,40,FALSE,'2025-01-01 09:00:00'), (2,'vendor-sync',4,15,FALSE,'2025-01-02 09:00:00'), (3,'engineering',0,80,FALSE,'2025-01-03 09:00:00'), (4,'old-partners',20,30,TRUE,'2025-01-04 09:00:00'), (5,'design-review',NULL,25,FALSE,'2025-01-05 09:00:00');
← All SQL Challenges · Slack Questions · Learn SQL Free