How to Write an SQL Query to Get Monthly Active Users (MAU)

Understanding user engagement is essential for any digital product or platform. One of the most common ways to measure this is through Monthly Active Users (MAU)—a key metric used by businesses to gauge how many unique users interact with a product each month. In this guide, we’ll walk you through writing an SQL query to calculate MAU, and show how AI2sql can help you generate accurate SQL with just natural language inputs.

What Are Monthly Active Users (MAU)?

Monthly Active Users, or MAU, represents the number of distinct users who engage with your application or website within a 30-day period. This metric is crucial for tracking growth, retention, and overall health of your product. Whether you’re a product manager, data analyst, or growth marketer, having quick access to MAU data can significantly improve your decision-making process.

Why Is Calculating MAU Important?

• Measure product health: Identify trends in user activity.

• Drive growth strategies: Analyze what features attract users.

• Monitor retention: Understand how many users return month over month.

How to Write an SQL Query for MAU

The goal is to count unique users who were active in each month. Suppose your user activity is logged in a table called user_activity with columns user_id and activity_date.

Sample SQL Query

SELECT
  DATE_TRUNC('month', activity_date) AS month,
  COUNT(DISTINCT user_id) AS monthly_active_users
FROM
  user_activity
GROUP BY
  DATE_TRUNC('month', activity_date)
ORDER BY
  month DESC;

This query does the following:

• Groups activity data by month

• Counts unique user_id entries for each month

• Orders the result to show the most recent month first

Generate SQL Queries Instantly with AI2sql

Not everyone is comfortable writing SQL from scratch. With AI2sql, you can simply ask your question in plain English and get the exact SQL you need:

Example: Text-to-SQL with AI2sql

Input: "Show me the number of unique users active for each month."
Output:

SELECT
  DATE_TRUNC('month', activity_date) AS month,
  COUNT(DISTINCT user_id) AS monthly_active_users
FROM
  user_activity
GROUP BY
  DATE_TRUNC('month', activity_date)
ORDER BY
  month DESC;

AI2sql streamlines analytics workflows, making it easier for both technical and non-technical team members to access the data they need quickly.

Tips for Accurate MAU Queries

• Ensure the user_id truly identifies unique users (avoid duplicates).

• Filter out test or inactive accounts if necessary.

• Customize the date field if your schema uses different column names (e.g., created_at instead of activity_date).

Conclusion

Calculating Monthly Active Users (MAU) with SQL is fundamental for product analytics. While writing such queries manually is straightforward for SQL users, tools like AI2sql let anyone generate accurate SQL with natural language, saving time and improving collaboration. Try AI2sql today, and make your data work for you—no matter your level of SQL expertise.