iNaturalist Observation Streaks

Some iNaturalist users use the site regularly. Like, really regularly. Here I'm visualizing different content types from the iNat database by users with more than 10,000 records using the excellent EventDrops visualization for D3.

For my own future reference, these data files were exported from the iNat database like this:

psql inaturalist_production -c "COPY (SELECT DISTINCT u.login, o.observed_on AS date FROM observations o JOIN users u ON u.id = o.user_id WHERE observed_on > '1960-01-01' AND u.id IN (9706,10285,2873,1115,12610,2991,1,15329,12158,2179)) TO STDOUT WITH CSV HEADER" > observations-observed-on.csv

psql inaturalist_production -c "COPY (SELECT DISTINCT u.login, DATE(o.created_at) AS date FROM observations o JOIN users u ON u.id = o.user_id WHERE observed_on > '1960-01-01' AND u.id IN (2873,1,15329,9706,8778,2179,9434,505,12610,1115)) TO STDOUT WITH CSV HEADER" > observations-created-on.csv

psql inaturalist_production -c "COPY (SELECT DISTINCT u.login, DATE(i.created_at) AS date FROM identifications i JOIN users u ON u.id = i.user_id WHERE u.id IN (1,477,2179,2991,9706,15329,10787,1115,4860,13200)) TO STDOUT WITH CSV HEADER" > identifications-created-on.csv

I selected those users with the most days like this:

-- users with the most days in the field
SELECT
  u.id,
  u.login,
  count(*) AS days
FROM
  (
    SELECT DISTINCT user_id, observed_on FROM observations
    WHERE observation_photos_count > 0
  ) AS user_days 
    JOIN users u ON u.id = user_days.user_id
GROUP BY u.id, u.login
ORDER BY count(*) DESC
LIMIT 10

-- users with the most days adding observations
SELECT
  u.id,
  u.login,
  count(*)
FROM
  (
    SELECT DISTINCT user_id, DATE(created_at) FROM observations
    WHERE observation_photos_count > 0
  ) AS user_days 
    JOIN users u ON u.id = user_days.user_id
GROUP BY u.id, u.login
ORDER BY count(*) DESC
LIMIT 10

-- users with the most days adding identifications
SELECT
  u.id,
  u.login,
  count(*)
FROM
  (
    SELECT DISTINCT i.user_id, DATE(i.created_at) FROM identifications i
    JOIN observations o ON o.id = i.observation_id
    WHERE i.user_id != o.user_id
  ) AS user_days 
    JOIN users u ON u.id = user_days.user_id
GROUP BY u.id, u.login
ORDER BY count(*) DESC
LIMIT 10