Metrics queries

[ianb]

Here are the queries planned for the metrics page.

How each query is used

We'll run the queries regularly (hourly?) in the background, saving the result to a database field. This way requests for the metrics won't trigger any database query and we'll avoid any DoS or dogpile problems with creating them on demand. We'll time each query and report that as part of the result, so we can see if something is becoming expensive.

The query will be run and turned into JSON data, with the data itself being stored in an independent database table. We will not keep old query results. The JSON will be published at /metrics/metric-data.json with no authentication required. The JSON will look like:

{
  "nameOfQuery": {
    "sql": "sql_that_generated_results",
    "executionTime": 104, // milliseconds of time to execute the query
    "executedAt": 1477605700730, // Date.now()-style timestamp when it was executed
    "columns": ["column1 name", "column2 name", ...],
    "rows": [
      [row1_column1, row1_column2, ...],
    ]
  }, ...
}

A separate HTML page will load that JSON data and render it in Some Pleasing Fashion.

Number of shots created by day

SELECT COUNT(data.id) AS number_of_shots, day
FROM data,
    date_trunc('day', data.created) AS day
WHERE data.created + INTERVAL '30 days' >= CURRENT_TIMESTAMP
    AND NOT data.deleted
    AND data.expire_time < CURRENT_TIMESTAMP
GROUP BY day;

Number of users who created a shot, by day

SELECT COUNT(DISTINCT data.deviceid) AS number_of_users, day
FROM data,
    date_trunc('day', data.created) AS day
WHERE data.created + INTERVAL '30 days' >= CURRENT_TIMESTAMP
    AND NOT data.deleted
    AND data.expire_time < CURRENT_TIMESTAMP
GROUP BY day;

Number of shots users have

This gives the number of users with a certain number of shots, using base 2 to group users (i.e., all users with 1 shot, with 2 shots, with 4 shots, with 8 shots, etc)

SELECT COUNT(counters.number_of_shots) AS count, counters.number_of_shots
FROM 
    (SELECT FLOOR(POWER(2, FLOOR(LOG(2, COUNT(data.id))))) AS number_of_shots
     FROM data
     WHERE NOT data.deleted AND data.expire_time < CURRENT_TIMESTAMP
     GROUP BY data.deviceid) AS counters
GROUP BY counters.number_of_shots

Retention

This gives the retention (as number of users) in number of days (where retention time is time between first creating a shot, and the latest shot), for users starting in different weeks.

SELECT COUNT(age.days) AS user_count, age.days, age.first_created_week
FROM
    (SELECT AGE(span.last_created, span.first_created) AS days, span.first_created_week
     FROM
         (SELECT
              date_trunc('week', MIN(created)) AS first_created_week,
              date_trunc('day', MIN(created)) AS first_created,
              date_trunc('day', MAX(created)) AS last_created
          FROM data
          GROUP BY deviceid) AS span) AS age
GROUP BY age.days, age.first_created_month;

[jvehent]

I don't see anything in those query that would create security or privacy concern.
/me puts security hat 🎩 on and gives r+