Microsoft: Teams Channels Where Guests Post Most Messages

Medium
782.

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.

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

ColumnTypeValues
user_idint
display_namevarchar
user_typeenumMember, Guest

Primary key: user_id.

One row per account in the tenant; guests are invited from other organisations.

Table: TeamsChannel

ColumnType
channel_idint
team_namevarchar
channel_namevarchar

Primary key: channel_id.

One row per channel.

Table: ChannelMessage

ColumnType
message_idint
channel_idint
sender_idint
sent_atdatetime
is_deletedbool

Primary key: message_id.

One row per channel post; sender_id is a TenantUser.user_id.

Example

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

TenantUser

user_iddisplay_nameuser_type
1PriyaMember
2RahulMember
3Alex (Fabrikam)Guest
4Maria (Fabrikam)Guest
5John (Northwind)Guest

TeamsChannel

channel_idteam_namechannel_name
10Project FalconGeneral
11Project FalconVendor Sync
12FinanceMonth-End Close
13PartnersOnboarding

ChannelMessage

message_idchannel_idsender_idsent_atis_deleted
11012025-06-02 09:00:000
21032025-06-02 09:05:000
31132025-06-02 10:00:000
41142025-06-02 10:02:000
51122025-06-02 10:10:000
61152025-06-02 10:15:000
71112025-06-02 10:20:001
81212025-06-03 11:00:000
91342025-06-03 12:00:000
101352025-06-03 12:01:000
3 more rows

Output

team_namechannel_nameguest_messagestotal_messages
Project FalconVendor Sync34
PartnersOnboarding23
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.

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

ColumnType
user_idint
display_namevarchar
user_typeenum(Member, Guest)

Primary key: user_id.

One row per account in the tenant; guests are invited from other organisations.

Table: TeamsChannel

ColumnType
channel_idint
team_namevarchar
channel_namevarchar

Primary key: channel_id.

One row per channel.

Table: ChannelMessage

ColumnType
message_idint
channel_idint
sender_idint
sent_atdatetime
is_deletedbool

Primary key: message_id.

One row per channel post; sender_id is a TenantUser.user_id.

Examples

Example 1

TenantUser

user_iddisplay_nameuser_type
1PriyaMember
2RahulMember
3Alex (Fabrikam)Guest
4Maria (Fabrikam)Guest
5John (Northwind)Guest

TeamsChannel

channel_idteam_namechannel_name
10Project FalconGeneral
11Project FalconVendor Sync
12FinanceMonth-End Close
13PartnersOnboarding

ChannelMessage

message_idchannel_idsender_idsent_atis_deleted
11012025-06-02 09:00:000
21032025-06-02 09:05:000
31132025-06-02 10:00:000
41142025-06-02 10:02:000
51122025-06-02 10:10:000
61152025-06-02 10:15:000
71112025-06-02 10:20:001
81212025-06-03 11:00:000
91342025-06-03 12:00:000
101352025-06-03 12:01:000
111322025-06-03 12:05:000
121312025-06-03 12:06:001
131322025-06-03 12:07:001

Output

team_namechannel_nameguest_messagestotal_messages
Project FalconVendor Sync34
PartnersOnboarding23

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 →