A common metric that lots of companies are interested in knowing is the monthly average of the count of users created per day. Previously, you would have needed to use a nested SELECT statement in SQL Mode. However, this is now possible with the Data Pipeline, specifically by adding a Group step.
To start with, create a chart with the count of users, and group them by the day of the created date. Your chart might look something like this chart below:
Next, we’ll want to create a column that shows the month and year of Created Date, so we can use it later to group by it. Click + Add Step, and choose Add Column from the list of available options.
We will use SQLite’s strftime() function to format the “Day of Created Date” to display only the month and year. This column will then be used to group the average number of new users.
We’ll next want to hide the “Day of Created Date” column since it is no longer needed, and reorder the columns so that “Month of Created Date” is the first column in the chart, and “Count of Users” is the second on the chart in the chart. Add a Hide Columns step, then add a Reorder Columns step to apply these changes.
And now for the most important part. Add a Group step in the Data Pipeline. We want to choose GROUP for “Month of Created Date”, because we will use this column to group the “Count of Users” by month. We want an average of the count of users, so choose AVG for “Count of Users”.
Click Ok, and then click Refresh Chart to update. This process negates the need to write a nested select statement. A full preview of all of the steps needed to create this chart is below.