db={"accounts":[{"_id":ObjectId("5e1fe45cd05bfb0cc549297d"),"apiCallCount":0,"email":"info@data.com","password":"dummy","userName":"AAAAA","companyName":"The AAAAAA","apiKey":"5e1fe45cd05bfb0cc549297c","solutionType":"VVVVVV","parentCompany":"","buildings":[{"_id":ObjectId("5e1fe5e3d05bfb0cc5494146"),"buildingName":"xxxxxx","address":"xxx","suite":"101","floor":"22","timeZone":"us/eastern","gateways":[{"_id":ObjectId("5e1fe624d05bfb0cc549453a"),"gatewayName":"CC-GW-THF-001","gatewayKey":"gk_5e1fe624d05bfb0cc549453a","suite":"area1","devices":[{"_id":ObjectId("5e1fe751d05bfb0cc549578d"),"serialNumber":"129300000013","area":"area1","connectionStatus":1,"gatewayKey":"gk_5e1fe624d05bfb0cc549453a","applicationNumber":30,"firmwareVersion":"1.0","needsAttention":false,"verificationCode":"GAAS","createdAt":ISODate("2020-01-16T04:32:17.899Z"),"updatedAt":ISODate("2020-01-16T08:53:54.460Z")}],"createdAt":ISODate("2020-01-16T04:27:16.678Z"),"updatedAt":ISODate("2020-01-16T08:53:54.460Z")},{"_id":ObjectId("5e1fe651d05bfb0cc54947f0"),"gatewayName":"AA-GW-THF-002","gatewayKey":"gk_5e1fe651d05bfb0cc54947f0","suite":"area2","devices":[{"_id":ObjectId("5e1fe7a9d05bfb0cc5495cdf"),"serialNumber":"129300000012","area":"area2","connectionStatus":0,"gatewayKey":"gk_5e1fe651d05bfb0cc54947f0","applicationNumber":30,"firmwareVersion":"1.0","needsAttention":false,"verificationCode":"VG3K","createdAt":ISODate("2020-01-16T04:33:45.698Z"),"updatedAt":ISODate("2020-01-16T08:54:17.604Z")}],"createdAt":ISODate("2020-01-16T04:28:01.532Z"),"updatedAt":ISODate("2020-01-16T08:54:17.604Z")},],"createdAt":ISODate("2020-01-16T04:26:11.941Z"),"updatedAt":ISODate("2020-01-16T08:56:32.657Z")}],"createdAt":ISODate("2020-01-16T04:19:40.310Z"),"updatedAt":ISODate("2020-04-06T18:18:39.628Z"),"__v":1,}],"sensingresults":[{"_id":ObjectId("5e73b83e0fa16c0e5a70be90"),"accountId":ObjectId("5e1fe45cd05bfb0cc549297d"),"buildingId":ObjectId("5e1fe5e3d05bfb0cc5494146"),"gatewayId":ObjectId("5e1fe651d05bfb0cc54947f0"),"deviceId":ObjectId("5e1fe7a9d05bfb0cc5495cdf"),"presenceStatus":0,"zoneCountNumberInstant":0,"zoneCountNumber":0,"longTermIndex":0.85,"shortTermIndex":0.8,"programVersion":"30207","radarMappingValue":4,"environmentMappingValue":1.09,"__v":0,"createdAt":ISODate("2020-03-19T18:22:06.000Z"),"updatedAt":ISODate("2020-03-19T18:22:05.000Z")},{"_id":ObjectId("5e73b83e0fa16c0e5a70be93"),"accountId":ObjectId("5e1fe45cd05bfb0cc549297d"),"buildingId":ObjectId("5e1fe5e3d05bfb0cc5494146"),"gatewayId":ObjectId("5e1fe651d05bfb0cc54947f0"),"deviceId":ObjectId("5e1fe7a9d05bfb0cc5495cdf"),"presenceStatus":0,"zoneCountNumberInstant":0,"zoneCountNumber":0,"longTermIndex":1.5,"shortTermIndex":1.51,"programVersion":"30207","radarMappingValue":2.73,"environmentMappingValue":2.22,"__v":0,"createdAt":ISODate("2020-03-19T18:21:50.000Z"),"updatedAt":ISODate("2020-03-19T18:21:50.000Z")},]}

db.sensingresults.aggregate([{"$lookup":{"from":"accounts","localField":"accountId","foreignField":"_id","as":"accountInfo"}},{"$unwind":"$accountInfo"},{"$match":{"updatedAt":{"$gt":ISODate("2020-03-01T00:00:00.000Z"),"$lte":ISODate("2020-03-30T00:00:00.000Z")},"zoneCountNumber":{"$exists":1}}},{"$project":{"dateHour":{"$dateToString":{"format":"%Y-%m-%dT%H","date":"$updatedAt"}},"minuteBucket":{"$ceil":{"$divide":[{"$minute":"$updatedAt"},5.0]}},"accountId":1,"buildingId":1,"gatewayId":1,"deviceId":1,"zoneCountNumber":1,"applicationNumber":1,"presenceStatus":1,"longTermIndex":1,"shortTermIndex":1,"programVersion":1,"radarMappingValue":1,"environmentMappingValue":1,"device":{"$let":{"vars":{"building":{"$arrayElemAt":[{"$filter":{"input":"$accountInfo.buildings","cond":{"$eq":["$$this._id","$buildingId"]}}},0]}},"in":{"$let":{"vars":{"gateway":{"$arrayElemAt":[{"$filter":{"input":"$$building.gateways","cond":{"$eq":["$$this._id","$gatewayId"]}}},0]}},"in":{"$arrayElemAt":[{"$filter":{"input":"$$gateway.devices","cond":{"$eq":["$$this._id","$deviceId"]}}},0]}}}}}}},{"$group":{"_id":{"accountId":"$accountId","buildingId":"$buildingId","gatewayId":"$gatewayId","deviceId":"$deviceId","applicationNumber":"$applicationNumber","dateHour":"$dateHour","minuteBucket":"$minuteBucket",},"avgZoneCountNumber":{"$avg":"$zoneCountNumber"},"serialNumber":{"$first":"$device.serialNumber"},"area":{"$first":"$device.area"},"spaceType":{"$first":"$device.spaceType"},"applicationNumber":{"$first":"$device.applicationNumber"},"totalPresence":{"$sum":"$presenceStatus"},"totalRecords":{"$sum":1}}},{"$project":{"accountId":"$_id.accountId","buildingId":"$_id.buildingId","gatewayId":"$_id.gatewayId","deviceId":"$_id.deviceId","dateHour":{"$let":{"vars":{"parsedDate":{"$dateFromParts":{"year":{"$year":"$updatedAt"},"month":{"$month":"$updatedAt"},"day":{"$dayOfMonth":"$updatedAt"},"hour":{"$hour":"$updatedAt"}}}},"in":{"$add":["$$parsedDate",{"$multiply":["$_id.minuteBucket",300000]}]}}},"avgZoneCountNumber":1,"_id":0,"serialNumber":1,"area":1,"spaceType":1,"applicationNumber":1,"occupancyPercentage":{"$substr":[{"$multiply":[{"$divide":["$totalPresence","$totalRecords"]},1]},0,4]},createdAt:new Date()}},{"$sort":{"dateHour":1}}])