Given a set of games indicies with a score attribute:

{
  "user_id": 1,
  "score": 90,
  "timestamp": "2014-03-17T10:00:00Z"
}

The following query will retrieve a week’s worth of scores for the user and sum the totals for each day.

{
  "query": {
    "filtered": {
      "query": {
        "match": {
          "user_id": 1
        }
      },
      "filter": {
        "range": {
          "timestamp": {
            "from": "2014-03-16T00:00:00Z",
            "to": "2014-03-23T23:59:59Z"
          }
        }
      }
    }
  },
  "aggs": {
    "weekly_score_totals": {
      "date_histogram": {
        "field": "timestamp",
        "interval": "day",
        "min_doc_count": 0
      },
      "aggs": {
        "score_stats": {
          "stats": {
            "field": "score"
          }
        }
      }
    },
  },
  "sort": [
    {
      "timestamp": {
        "order": "asc"
      }
    }
  ]
}

Let’s break down the search above. The first level contains two parts, query and aggs. Let’s look at the query first.

Query

This query is a filtered query. The top part of this query will match any scores in the index with a user_id matching the one provided.

The second part of the query is the filter. This filter includes scores with a timestamp value within the from and to bounds.

Putting these two pieces together, this query will match scores within the given range and matching the provided user_id.

Aggregation

The other half of this Elasticsearch search is the aggregation. The first part of this aggregation will group the scores from the query based on their timestamp attribute. We control the granularity of this grouping by providing an interval value; in this case we’re using day. The inclusion of min_doc_count ensures that, if there are intervals with no scores, Elasticsearch returns an empty interval.

The other piece of this aggregation is a stats aggregation. The stats aggregation will compute statistics over all the scores in each interval. This will provide the total of scores for each interval.

Conclusion

So there you have it. A simple elasticsearch request to return a week’s worth of scores for a user totaled by day.