Aah! the long mongo query!

AggregationQuery

[
  {
    "$project": {
      "ts": 1,
      "servedBy": 1,
      "state": 1,
      "metrics": 1,
      "source": 1,
      "ASA": {
        "$cond": [
          {
            "$gte": [
              {
                "$subtract": [
                  "$servedBy.ts",
                  "$ts"
                ]
              },
              0
            ]
          },
          {
            "$divide": [
              {
                "$subtract": [
                  "$servedBy.ts",
                  "$ts"
                ]
              },
              1000
            ]
          },
          0
        ]
      }
    }
  },
  {
    "$group": {
      "_id": {
        "source": "$source",
        "year": {
          "$year": "$ts"
        },
        "month": {
          "$month": "$ts"
        },
        "day": {
          "$dayOfMonth": "$ts"
        },
        "hour": {
          "$hour": "$ts"
        },
        "state": "$state",
        "interval": {
          "$subtract": [
            {
              "$minute": "$ts"
            },
            {
              "$mod": [
                {
                  "$minute": "$ts"
                },
                15
              ]
            }
          ]
        }
      },
      "count": {
        "$sum": 1
      },
      "AHT": {
        "$sum": "$metrics.response.avg"
      },
      "ASA": {
        "$sum": "$ASA"
      }
    }
  },
  {
    "$project": {
      "_id.year": 1,
      "_id.month": 1,
      "_id.day": 1,
      "_id.hour": 1,
      "_id.interval": 1,
      "_id.state": 1,
      "_id.source": 1,
      "count": 1,
      "AHT": 1,
      "ASA": 1,
      "abandoned": {
        "$cond": [
          {
            "$setIsSubset": [
              [
                "$_id.state"
              ],
              [
                "expired",
                "dropped"
              ]
            ]
          },
          "$count",
          0
        ]
      },
      "accept": {
        "$cond": [
          {
            "$setIsSubset": [
              [
                "$_id.state"
              ],
              [
                "expired",
                "dropped"
              ]
            ]
          },
          0,
          "$count"
        ]
      }
    }
  },
  {
    "$group": {
      "_id": {
        "year": "$_id.year",
        "month": "$_id.month",
        "day": "$_id.day",
        "hour": "$_id.hour",
        "interval": "$_id.interval",
        "source": "$_id.source"
      },
      "Abandoned": {
        "$sum": "$abandoned"
      },
      "Accepted": {
        "$sum": "$accept"
      },
      "total": {
        "$sum": "$count"
      },
      "AHT": {
        "$sum": "$AHT"
      },
      "ASA": {
        "$sum": "$ASA"
      }
    }
  },
  {
    "$project": {
      "_id": 1,
      "Abandoned": 1,
      "Accepted": 1,
      "total": 1,
      "AHT": {
        "$cond": [
          {
            "$eq": [
              "$Accepted",
              0
            ]
          },
          0,
          {
            "$divide": [
              "$AHT",
              "$Accepted"
            ]
          }
        ]
      },
      "ASA": {
        "$cond": [
          {
            "$eq": [
              "$Accepted",
              0
            ]
          },
          0,
          {
            "$divide": [
              "$ASA",
              "$Accepted"
            ]
          }
        ]
      }
    }
  },
  {
    "$sort": {
      "_id.year": 1,
      "_id.month": 1,
      "_id.day": 1,
      "_id.hour": 1,
      "_id.interval": 1
    }
  }
]

Document.json

{
	"_id" : "GENERAL",
	"ts" : ISODate("2019-05-06T08:44:18.633Z"),
	"t" : "c",
	"name" : "general",
	"usernames" : [ ],
	"msgs" : 11,
	"usersCount" : 4,
	"default" : true,
	"_updatedAt" : ISODate("2019-05-22T20:20:04.825Z"),
	"lm" : ISODate("2019-05-16T12:27:27.391Z"),
	"lastMessage" : {
		"_id" : "4TGwcfvNrPo5sM6oj",
		"rid" : "GENERAL",
		"msg" : "can you have something",
		"ts" : ISODate("2019-05-16T12:27:27.391Z"),
		"u" : {
			"_id" : "MD2L82DJ6HraWtZb8",
			"username" : "admin",
			"name" : "admin"
		},
		"_updatedAt" : ISODate("2019-05-16T12:27:27.433Z"),
		"mentions" : [ ],
		"channels" : [ ],
		"sandstormSessionId" : null
	}
}
{
	"_id" : "kM3hiWjSHNzWquQW8",
	"msgs" : 6,
	"usersCount" : 1,
	"lm" : ISODate("2019-05-06T09:47:48.054Z"),
	"fname" : "Customer ",
	"t" : "l",
	"ts" : ISODate("2019-05-06T09:42:02.666Z"),
	"v" : {
		"_id" : "znsdFEcsuhT3c6gYx",
		"username" : "guest-2",
		"token" : "cli_63f313d3-ee1b-46d7-ab65-94a193034f45",
		"status" : "offline"
	},
	"cl" : false,
	"_updatedAt" : ISODate("2019-05-06T09:47:48.152Z"),
	"servedBy" : {
		"_id" : "DJGiMvsMdmPto6T8R",
		"username" : "agent1",
		"ts" : ISODate("2019-05-06T09:42:30.235Z")
	},
	"lastMessage" : {
		"t" : "livechat-close",
		"msg" : "The customer is not responding. I am disconnecting the chat. ",
		"groupable" : false,
		"ts" : ISODate("2019-05-06T09:47:48.054Z"),
		"u" : {
			"_id" : "DJGiMvsMdmPto6T8R",
			"username" : "agent1",
			"name" : "agent1"
		},
		"rid" : "kM3hiWjSHNzWquQW8",
		"_id" : "a74zon9Qui7zMyeKW",
		"_updatedAt" : ISODate("2019-05-06T09:47:48.083Z"),
		"mentions" : [ ],
		"channels" : [ ],
		"sandstormSessionId" : null
	},
	"responseBy" : {
		"_id" : "DJGiMvsMdmPto6T8R",
		"username" : "agent1"
	},
	"metrics" : {
		"response" : {
			"avg" : 168.765,
			"fd" : ISODate("2019-05-06T09:47:48.152Z"),
			"ft" : 345.486,
			"total" : 4,
			"tt" : 675.06
		},
		"reaction" : {
			"fd" : ISODate("2019-05-06T09:47:48.152Z"),
			"ft" : 317.917,
			"tt" : 564.784
		},
		"chatDuration" : 345.34
	},
	"promptMessageUserCount" : 4,
	"promptMessageAgentCount" : 0,
	"closer" : "user",
	"closedBy" : {
		"_id" : "DJGiMvsMdmPto6T8R",
		"username" : "agent1"
	},
	"closedAt" : ISODate("2019-05-06T09:47:48.006Z")
}