group_daily_scores query

group_daily_scores_query.md

We have user_scores_daily created every day for every user. Each of these records has a _user_id tag and some numeric fields.

Each user belongs to multiple "groups" (i.e. teams).

We also want to get group_scores_daily, which containns the sum of user_scores_daily fields for all users in each group (per day).

We currently do this in a background job with Ruby. It runs every day and combines data using two separate Influx queries.

1. We get a mapping of group_id => user_ids

SELECT
  DISTINCT(group_id) AS group_id
FROM
  "groups"
WHERE
  event='group_user_added'
AND
  time > 1451606400
GROUP BY
  _user_id

2. We get all user_daily_scores for the last day:

SELECT
  *
FROM
  user_scores_daily
WHERE
  time > now() - 1d

Using this data, we need to create two sets of records.

First, we need to score the same user/score/day for each of the user groups. This is so we can query for the top users in a particular group.

group_id | user_id | score
------------------------------
1          1         5
1          2         6
2          1         5
2          2         6

Second, we need to get the total score for each group/day:

group_id | score
------------------------------
1          11
2          11

I'm not sure how we would either of these in a continuous query, I think we need to do some processing in Ruby.