Count and nodes for each day of week and each hour

Neo4j Graph Platform Cypher
2

It depends on how much data you have, but you could consider using list comprehensions to do your filtering. Something like this: (You will have to adjust)

UNWIND [{ "name": "Mon", idx: 0 }, { name: "Tue", idx: 1 } /* ... */ ] AS WeekDay
MATCH (o:Order)
WHERE datetime(o.createdAt).dayOfWeek = WeekDay.idx
WITH WeekDay, collect(datetime(o.created_at)) as OrderTimestamps
RETURN 
     WeekDay.name as DayName,
    size([ts IN OrderTimestamps WHERE ts.hour = 0]) as zeroHour,
    size([ts IN OrderTimestamps WHERE ts.hour = 1]) as oneHour,
 /* ...  */

The way this query works (will need some adjustment) is that it starts with the day names, and collects up all orders that happened on that day into a list of timestamps (OrderTimestamps)

Then, to output how many are in zero hour, 1st hour, etc -- it uses a list comprehension to repeatedly filter the list of timestamps to only a list of those in the zero hour. It then counts the size of that list with size(), so you get counts per-hour, per-day.

2 Likes