2. What does the total count of user sign-ups look like over time, on a month-by-month basis?

Overview

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: 

  1. How do I determine the number of users that have signed up? 
  2. How do I segment that data such that I get a count per month? 

Answers to questions

  1. Like the previous question, we will need to identify the count of unique User IDs
  2. 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. 

Data Location:

Analysis Technique: 

SQL Concepts:

Data Transformations:

  • None required

Visualization Approach:

Interactive Mode Configuration and Sample Answer:

*Sample Answer not meant for exact comparison, results may change over time

SQL:

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;