Microsoft: Teams Daily Stickiness (DAU/MAU) in March 2025
The Microsoft Teams engagement team tracks stickiness: how many of the people who used Teams in the last month also used it today.
- Difficulty: Medium
- Topics: Aggregation, Dates, Joins
- Dialect: MySQL
- Problem: #789
Problem statement
The Microsoft Teams engagement team tracks stickiness: how many of the people who used Teams in the last month also used it today. A high DAU/MAU ratio means Teams is a daily habit, not an occasional tool.
For every day of March 2025 with any activity, return activity_date, dau (distinct users active that day), mau (distinct users active in the 30 days ending that day — the day itself and the 29 before it, including days in February) and stickiness_pct = 100 × dau ÷ mau, rounded to 2 decimals. A user with several actions counts once in each figure. Order by activity_date.
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: TeamsActivity
| Column | Type |
|---|---|
| activity_id | int |
| user_id | int |
| activity_date | date |
| activity_type | enum(chat, call, meeting, file) |
Primary key: activity_id.
One row per qualifying action in Teams; a user can have many rows a day.
Examples
Example 1
TeamsActivity
| activity_id | user_id | activity_date | activity_type |
|---|---|---|---|
| 1 | 1 | 2025-02-01 | chat |
| 2 | 2 | 2025-02-20 | call |
| 3 | 3 | 2025-03-02 | chat |
| 4 | 3 | 2025-03-02 | meeting |
| 5 | 3 | 2025-03-03 | file |
| 6 | 4 | 2025-03-03 | chat |
| 7 | 2 | 2025-03-31 | chat |
| 8 | 5 | 2025-03-31 | meeting |
| 9 | 1 | 2025-04-01 | chat |
Output
| activity_date | dau | mau | stickiness_pct |
|---|---|---|---|
| 2025-03-02 | 1 | 3 | 33.33 |
| 2025-03-03 | 2 | 3 | 66.67 |
| 2025-03-31 | 2 | 4 | 50 |
How to solve Microsoft: Teams Daily Stickiness (DAU/MAU) in March 2025
Stickiness divides two distinct counts over different windows: the users of one day by the users of the 30 days that end on it. DAU is easy, but MAU is a trailing distinct count, and that is the catch: COUNT(DISTINCT …) OVER (… ROWS 29 PRECEDING) is not allowed, and even a SUM window would count a person once for every day they were active. Distinct users need the raw rows.
So the window is built with a join. Take every March day that had activity, and join it to every activity dated from DATE_SUB(day, INTERVAL 29 DAY) to the day itself. Each day's group then holds exactly its trailing month of rows, including February rows for early March: user 1's activity on 1 February is in the 2 March window but not the 3 March one. COUNT(DISTINCT a.user_id) is MAU, and a conditional distinct count of the rows dated on the day is DAU. MAU is never zero, because the day's own users are in it.
The alternative computes DAU per day first and MAU with a correlated subquery using DATEDIFF(day, activity) BETWEEN 0 AND 29. Both cost a pass over the activity per reported day; a production table would precompute daily distinct users per person first.
Reference solution (MySQL)
WITH march_days AS (
SELECT DISTINCT activity_date AS on_day
FROM TeamsActivity
WHERE activity_date >= '2025-03-01' AND activity_date < '2025-04-01'
)
SELECT d.on_day AS activity_date,
COUNT(DISTINCT CASE WHEN a.activity_date = d.on_day THEN a.user_id END) AS dau,
COUNT(DISTINCT a.user_id) AS mau,
ROUND(100 * COUNT(DISTINCT CASE WHEN a.activity_date = d.on_day THEN a.user_id END) / COUNT(DISTINCT a.user_id), 2) AS stickiness_pct
FROM march_days d
JOIN TeamsActivity a ON a.activity_date BETWEEN DATE_SUB(d.on_day, INTERVAL 29 DAY) AND d.on_day
GROUP BY d.on_day
ORDER BY d.on_dayAnother way
SELECT activity_date, dau, mau, ROUND(100 * dau / mau, 2) AS stickiness_pct
FROM (
SELECT t.activity_date, t.dau,
(SELECT COUNT(DISTINCT a.user_id) FROM TeamsActivity a
WHERE DATEDIFF(t.activity_date, a.activity_date) BETWEEN 0 AND 29) AS mau
FROM (SELECT activity_date, COUNT(DISTINCT user_id) AS dau
FROM TeamsActivity
WHERE YEAR(activity_date) = 2025 AND MONTH(activity_date) = 3
GROUP BY activity_date) t
) x
ORDER BY activity_date← Microsoft: Customers Billed for Both Azure and Microsoft 365 · Microsoft: Privileged Azure Role Holders for the Access Review →