DB Queries

Number of tasks fixed

copy (select count(sa.*), sa.status from status_actions sa where age(sa.created) < '1 month' group by sa.status order by status) to stdout with csv;

Most active challenges

copy (select count(sa.*) cnt, c.name, c.id from status_actions sa, challenges c where c.id = sa.challenge_id and age(sa.created) < '1 month' and c.enabled and sa.status=1 group by c.name, c.id order by cnt desc) to stdout with csv;

Challenges created

copy (select count(t.*), c.name, c.id from challenges c, tasks t where c.enabled and age(c.created) < '1 month' and c.id = t.parent_id group by c.id, c.name order by count desc) to stdout with csv;

Most active users

copy (select count(sa.*), u.osm_id, u.name from users u, status_actions sa where u.osm_id = sa.osm_user_id and age(sa.created) < '1 month' group by u.osm_id, u.name order by count desc; active users: select count(distinct u.osm_id) from users u, status_actions sa where u.osm_id = sa.osm_user_id and age(sa.created) < '1 month') to stdout with csv';