Example: Get Avg/Month of Count/Day

Data Pipeline

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:

Create a chart

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.

Add a column

We’ll use SQLite’s strftime() function to format the “Day of Created Date” to display only the month and year.

Display Month of Created Date

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.

Hide and reorder columns

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”.

Group by month

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.

Full Pipeline view

Related Data Pipeline Help Articles

See more