Retrospectively pulling number of users each calendar month

Data & reporting
7

Ok, I have another idea. I asked our AI helper.

SQL Query for Active Users by Month AI

HAWK:

Write me a data explorer query which returns a list of user accounts that are still active and were registered within a given month

DiscourseHelper:

Searching

Searching for: Discourse Data Explorer query to find active users registered within a specific month

Reading

Read: Active users for specific months

To create a Data Explorer query that returns a list of user accounts that are still active and were registered within a specific month, you can use the following SQL query. This query assumes that an active user is someone who has made a post or reply within a given timeframe.

--[params]
-- int :month = 5
-- int :year = 2023

SELECT u.id, u.username, u.created_at, u.last_seen_at
FROM users u
WHERE u.active = TRUE
  AND EXTRACT(MONTH FROM u.created_at) = :month
  AND EXTRACT(YEAR FROM u.created_at) = :year
  AND u.last_seen_at IS NOT NULL -- Assuming active means they accessed the site recently
ORDER BY u.created_at

Explanation of the Query:

  • :month and :year are parameters you can set to filter for the specific month and year.
  • u.active = TRUE ensures that the user account is still active.
  • EXTRACT(MONTH FROM u.created_at) = :month and EXTRACT(YEAR FROM u.created_at) = :year filter the registration date to the specified month and year.
  • u.last_seen_at IS NOT NULL considers the user active if they have logged in at any time.
  • The result provides user ID, username, registration date, and last-seen date.

Feel free to adjust the parameters and fields according to your needs. Let me know if you need any more assistance!

Feel free to give it further context if I’m missing something.

3 Likes