Microsoft: Teams Channels Where Guests Post Most Messages
A Microsoft 365 compliance analyst reviewing external collaboration wants the Teams channels that are effectively run by guests from other organisations.
- Difficulty: Medium
- Topics: Joins, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #782
Problem statement
A Microsoft 365 compliance analyst reviewing external collaboration wants the Teams channels that are effectively run by guests from other organisations.
Counting only messages that are not deleted, return every channel where messages sent by Guest users are more than half of the channel's messages. Return team_name, channel_name, guest_messages and total_messages, in any order. A channel with no live messages is not in the answer.
A practice scenario set at Microsoft: the tables and data are invented, and this is not a question from, or endorsed by, Microsoft.
Tables
Table: TenantUser
| Column | Type |
|---|---|
| user_id | int |
| display_name | varchar |
| user_type | enum(Member, Guest) |
Primary key: user_id.
One row per account in the tenant; guests are invited from other organisations.
Table: TeamsChannel
| Column | Type |
|---|---|
| channel_id | int |
| team_name | varchar |
| channel_name | varchar |
Primary key: channel_id.
One row per channel.
Table: ChannelMessage
| Column | Type |
|---|---|
| message_id | int |
| channel_id | int |
| sender_id | int |
| sent_at | datetime |
| is_deleted | bool |
Primary key: message_id.
One row per channel post; sender_id is a TenantUser.user_id.
Examples
Example 1
TenantUser
| user_id | display_name | user_type |
|---|---|---|
| 1 | Priya | Member |
| 2 | Rahul | Member |
| 3 | Alex (Fabrikam) | Guest |
| 4 | Maria (Fabrikam) | Guest |
| 5 | John (Northwind) | Guest |
TeamsChannel
| channel_id | team_name | channel_name |
|---|---|---|
| 10 | Project Falcon | General |
| 11 | Project Falcon | Vendor Sync |
| 12 | Finance | Month-End Close |
| 13 | Partners | Onboarding |
ChannelMessage
| message_id | channel_id | sender_id | sent_at | is_deleted |
|---|---|---|---|---|
| 1 | 10 | 1 | 2025-06-02 09:00:00 | 0 |
| 2 | 10 | 3 | 2025-06-02 09:05:00 | 0 |
| 3 | 11 | 3 | 2025-06-02 10:00:00 | 0 |
| 4 | 11 | 4 | 2025-06-02 10:02:00 | 0 |
| 5 | 11 | 2 | 2025-06-02 10:10:00 | 0 |
| 6 | 11 | 5 | 2025-06-02 10:15:00 | 0 |
| 7 | 11 | 1 | 2025-06-02 10:20:00 | 1 |
| 8 | 12 | 1 | 2025-06-03 11:00:00 | 0 |
| 9 | 13 | 4 | 2025-06-03 12:00:00 | 0 |
| 10 | 13 | 5 | 2025-06-03 12:01:00 | 0 |
| 11 | 13 | 2 | 2025-06-03 12:05:00 | 0 |
| 12 | 13 | 1 | 2025-06-03 12:06:00 | 1 |
| 13 | 13 | 2 | 2025-06-03 12:07:00 | 1 |
Output
| team_name | channel_name | guest_messages | total_messages |
|---|---|---|---|
| Project Falcon | Vendor Sync | 3 | 4 |
| Partners | Onboarding | 2 | 3 |
How to solve Microsoft: Teams Channels Where Guests Post Most Messages
Each message needs one fact from another table — whether its sender is a guest — so join ChannelMessage to TenantUser, and to TeamsChannel for the names. Deleted messages are filtered out in WHERE before anything is counted, which changes answers: a channel with two guest posts and one live member post is guest-dominated even if the members also posted and then deleted two more.
Per channel, COUNT(*) is the live total and SUM(CASE WHEN user_type = 'Guest' THEN 1 ELSE 0 END) the guest part. The condition is on the group, so it goes in HAVING, written as guests * 2 > total — strictly more than half, so an even split is out, and no division is needed. Channels without live messages produce no group and are absent.
The alternative aggregates in a derived table, classifying senders with an IN subquery, then compares with total / 2 (decimal division, so 3 > 2.5 behaves). Both read the messages once.
Reference solution (MySQL)
SELECT c.team_name, c.channel_name,
SUM(CASE WHEN u.user_type = 'Guest' THEN 1 ELSE 0 END) AS guest_messages,
COUNT(*) AS total_messages
FROM ChannelMessage m
JOIN TeamsChannel c ON c.channel_id = m.channel_id
JOIN TenantUser u ON u.user_id = m.sender_id
WHERE m.is_deleted = 0
GROUP BY c.channel_id, c.team_name, c.channel_name
HAVING SUM(CASE WHEN u.user_type = 'Guest' THEN 1 ELSE 0 END) * 2 > COUNT(*)Another way
SELECT c.team_name, c.channel_name, t.guest_messages, t.total_messages
FROM TeamsChannel c
JOIN (
SELECT m.channel_id,
COUNT(CASE WHEN m.sender_id IN (SELECT user_id FROM TenantUser WHERE user_type = 'Guest') THEN 1 END) AS guest_messages,
COUNT(*) AS total_messages
FROM ChannelMessage m
WHERE NOT m.is_deleted
GROUP BY m.channel_id
) t ON t.channel_id = c.channel_id
WHERE t.guest_messages > t.total_messages / 2← Microsoft: Azure Q1 Cost Split by Service Family per Month · Microsoft: Xbox Players Who Unlocked Every Achievement in a Title →