Let’s say you want to get 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 the Group step.
To start with, create a chart with the count of users, grouped by day. Your chart might look something like this:
Next, we’ll want to create a column that shows the month and year of Created Date, so we can later group by it. Click + Add Step, and choose Add Column.
We’ll use SQLite’s strftime() function to format the “Day of Created Date” to display only the month and year.
We’ll next want to hide the “Day of Created Date” column, and reorder the columns so that “Month of Created Date” is first, and “Count of Users” is second. 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 want 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. No need to write a nested select statement. A full preview of all of the steps needed to create this chart is below.