When approaching questions with complexity beyond basic aggregation, it can be beneficial to deconstruct the question into components. We can break down the approach into 2 sub-questions:
- How do I determine the number of users that have signed up?
- How do I segment that data such that I get a count per month?
Answers to questions
- Like the previous question, we will need to identify the count of unique User IDs
- To segment the results by month of Created Date, drag the Created Date field from the Users table into the Dimensions box of the chart creator. The default time aggregation is by day, but you can update to Month by simply clicking on the box and altering the Time Bucket to Month. From a SQL perspective, adding a field to the Dimensions box of the chart creator will result in that field being added to both the SELECT and the GROUP BY clauses.
- Aggregation Function: Count (Distinct user_id)
- GROUP BY: Month of Created Date
- ORDER BY: Month of Created Date ascending
- None required
Interactive Mode Configuration and Sample Answer:
SELECT TO_CHAR("us0"."created_date", 'YYYY-MM') AS "Month of Created Date" , COUNT(DISTINCT "us0"."user_id") AS "Count of Users" FROM "public"."users" AS "us0" GROUP BY "Month of Created Date" ORDER BY "Month of Created Date" ASC LIMIT 1000;