sync cohorts sql

cohorts.sql

-- this produces a table of daily retention of new users over a period of 10 days

CREATE TABLE cohorts10 AS
SELECT DAY,
       period,
       users,
       retained
FROM
  ( SELECT r.DAY,
          (f.DAY - r.DAY) AS period,
          max(cohort_size.count) AS users,
          count(DISTINCT f.uid) AS retained
   FROM
     ( SELECT rollup.*
      FROM rollup
      JOIN user_created u ON u.uid = rollup.uid
      AND u.created = rollup.DAY ) r
   LEFT JOIN rollup f ON r.uid = f.uid
   AND r.DAY < f.DAY
   AND r.DAY + 10 >= f.DAY
   LEFT JOIN
     ( SELECT DAY,
              count(DISTINCT uid) AS COUNT
      FROM
        ( SELECT rollup.*
         FROM rollup
         JOIN user_created u ON u.uid = rollup2.uid
         AND u.created = rollup.DAY )
      GROUP BY DAY ) AS cohort_size ON r.DAY = cohort_size.DAY
   GROUP BY 1,
            2) t
WHERE period IS NOT NULL
ORDER BY DAY,
         period;

schemas.sql

-- the schema for the rollup table. Each day a query rolls up all the activity of the new day into one row per dev(ice).

create table rollup(
  uid char(32) not null,
  dev char(32) not null,
  day date not null,
  min_t float,
  max_t float,
  posts integer,
  gets integer,
  puts integer,
  dels integer,
  aoks integer,
  oops integer,
  fups integer,
  clients integer,
  crypto integer,
  forms integer,
  history integer,
  keys integer,
  meta integer,
  bookmarks integer,
  prefs integer,
  tabs integer,
  passwords integer,
  addons integer
)

-- a lookup table populated from fxa tells us the date a uid was created

create table user_created (
  uid char(32),
  created date
)