Grouping by the Hour and alerting based on count of usage per hour.

DMJerry

Hello,

 

I am trying to do a group by using hour of day.  The date operator in Magic does not have that funcationality but curious how I can do it within magic or in SQL.  Here is my data where I want to count how many times a code was use in an hour for each day I want to use.

COUPON-CODE	updated_at	Order #	Goal(Count of Usage by Hour)
10%	17-Apr-20 10:04:53	1	2
10%	17-Apr-20 10:43:49	2	2
10%	17-Apr-20 5:43:25	3	1
10%	17-Apr-20 0:46:53	4	1
10%	17-Apr-20 17:10:14	5	1
20%	17-Apr-20 2:51:38	6	1
20%	17-Apr-20 6:42:59	7	1
20%	17-Apr-20 13:24:49	8	2
20%	17-Apr-20 13:53:14	9	2
50%	17-Apr-20 15:51:46	10	1
50%	17-Apr-20 16:02:26	11	3
50%	17-Apr-20 16:27:03	12	3
50%	17-Apr-20 16:27:03	13	3

jaeW_at_Onyx

In Magic:  create a copy of the column converted to TEXT and do a REGEX / STRING transform.

In MySQL: use date_part functions (like hour).

 

Best Solution:  split date and time (HH:mm) into separate columns during data ingestion.  THEN use a Fusion to join a Time Dimension table (where you have one row for every minute of the day).

 

Why is Fusion the best model?  

Because it minimizes the number of times you have to read and write the data. AND by using a Time dimension you can create custom groupings like ("Morning", "coffee-break", "evening") without using a complex beast mode in each of your cards.

 

Why does splitting date_time make sense?

Because you'll want to see a trend in behavior on Saturdays versus trend in behavior on Mondays.  By using a Time dimension you can avoid ugly beast modes.