Database

Connect

1. Login to jumpbox.
2. Run aurora/connect.sh. Enter password when prompted (from gdrive).
3. After login, a mysql client is presented.

Queries

Discovery

> show databases;   
> use *<database>*; 
> show tables;
> show columns from *<table>*;

Statistics

All conversations created

> select count(*) from h2conversation 
  where creation > UNIX_TIMESTAMP('20160801') * 1000;

Unique conversation creators

> select count(distinct author) from h2conversation 
  where creation > UNIX_TIMESTAMP('20160801') * 1000;

Stanzas created

> select count(id) from h2ink where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000);
> select count(id) from h2multiwordtext where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000);
> select count(id) from h2image where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000);
> select count(id) from h2quizresponse where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000);

All stanzas created (informal)

> select sum(
    ( select count(id) from h2ink where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) ) +
    ( select count(id) from h2text where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) ) +
    ( select count(id) from h2image where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) )
  );

All poll answers created (formal)

> select count(id) from h2quizresponse where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000);

All stanzas created (formal and informal)

> select sum(
    ( select count(id) from h2ink where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) ) +
    ( select count(id) from h2text where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) ) +
    ( select count(id) from h2image where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) ) +
    ( select count(id) from h2quizresponse where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) )
  );

All unique stanza creators (excluding quiz)

Incorrect - counts the same author multiple times for different stanza types

> select sum(
    ( select count(distinct author) from h2ink where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) ) +
    ( select count(distinct author) from h2text where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) ) +
    ( select count(distinct author) from h2image where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) )
  );

or:

> select count(a) from (
    select distinct author as a from h2ink where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) 
      union
    select distinct author as a from h2text where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) 
      union
    select distinct author as a from h2image where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000)
  ) as bob;

All unique quiz responders

> select count(distinct author) from h2quizresponse where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000);

All unique stanza creators (including quiz)

Incorrect - counts the same author multiple times for different stanza types

> select sum(
    ( select count(distinct author) from h2ink where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) ) +
    ( select count(distinct author) from h2text where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) ) +
    ( select count(distinct author) from h2image where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) ) +
    ( select count(distinct author) from h2quizresponse where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) )
  );

or:

> select count(a) from (
    select distinct author as a from h2ink where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) 
      union
    select distinct author as a from h2text where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) 
      union
    select distinct author as a from h2image where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) 
      union
    select distinct author as a from h2quizresponse where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) 
  ) as bob;

Unique conversation attendees

> select count(distinct author) from h2attendance 
  where timestamp_c > UNIX_TIMESTAMP('20160801') * 1000;