Question: Teams Power Users
"Using Teams activity data (messages sent, meetings joined), identify the top 5% most active users."

Step-by-Step Explanation:

You have a table called teams_activity with:

• user_id
• messages_sent
• meetings_joined

You need to:

1. Find the total activity for each user (messages + meetings).
2. Identify the top 5% most active users based on total activity.

Sample Data (teams_activity):

Step 1: Calculate Total Activity per User

Total Activity = messages_sent + meetings_joined

Example:

• User 1: 100 + 10 = 110
• User 2: 50 + 5 = 55
• User 3: 200 + 15 = 215

Step 2: Rank Users by Total Activity

We need to sort users by total activity in descending order.

Step 3: Calculate the Top 5%

• First, find total number of users.
• 5% of that number = how many users we need to pick.
• Example:
  • 10 users × 5% = 0.5 → round up → pick at least 1 user (most active user).

SQL Query:

WITH user_activity AS (
  SELECT
    user_id,
    (messages_sent + meetings_joined) AS total_activity
  FROM
    teams_activity
),

ranked_users AS (
  SELECT
    user_id,
    total_activity,
    ROW_NUMBER() OVER (ORDER BY total_activity DESC) AS activity_rank,
    COUNT(*) OVER () AS total_users
  FROM
    user_activity
)

SELECT
  user_id,
  total_activity
FROM
  ranked_users
WHERE
  activity_rank <= CEIL(total_users * 0.05);

Explanation of SQL:

• user_activity CTE:
  • Calculates total activity (messages + meetings) for each user.
• ranked_users CTE:
  • Ranks users by total_activity in descending order.
  • Calculates the total number of users.
• Final query:
  • Selects users whose rank is within the top 5% (CEIL(total_users * 0.05) ensures we round up).

Expected Output:

Explanation:

• User 10 had the highest total activity.
• In this sample (10 users), 5% of 10 = 0.5 → rounded up → 1 user selected.

Important Points:

• Always add messages_sent and meetings_joined to get a true total activity measure.
• Use ROW_NUMBER() with ORDER BY total_activity DESC to rank users.
• CEIL() is used to round up so that you include at least one user even if 5% is less than 1.
• If there are many users (like 10,000), 5% would be 500 users.