Group Data with Group By

First, you want to ‘group’ the data by a column you want to use to group the data for.

Here, I’m trying to group the data by Day so I’m selecting ‘timestamp’ column and selecting ‘Group By’ from the column header menu.

Once it’s grouped, you will see a green button at the top that indicates the data is grouped and a green color bar at the top of the grouping column. Also the table is now partitioned by color to indicate different groups.

My Data is houly, not daily!

In that case, you can round the date to the level you want first, then group the data by this rounded date.

For example, here I’m rounding the date/time data to Day level.

This will populate ‘floor_date’ function.

As you see in the picture below, all the date/time data has become day level by leaving hour/minute/second portions to be zero.

I use ‘Floor’ option to round the data because I wanted any hour of the day to be the same day. This means that 1AM, 9AM, 1PM, and 10PM of September 1st, 2018 are all becoming September 1st, 2018.

Anyway, once the date/time is rounded, then use this column to group by. Note that the grouping operation should be done after the date/time rounding operation.

Aggregate Data with Summarize

Once the data is grouped, then you can simply use aggregation functions like ‘sum’, ‘mean’, etc. with Summarize step to aggregate the data.

Here, I’m counting number of rows.

If I want to add another aggregation column to count unique number of users for each day, then I can click on this Plus button.

The key here is, to add another column of the aggregation you need to do the operation in the same Summarize step.

Anyway, here I’m selecting ‘userid’ column and ‘Unique Count’ as Aggregate Function to count only the unique number of users for each day.

Here is the final result.