Metrics [prod]

https://firelemons.github.io/casaMetricsComparison/

1. case contact count per org https://data.heroku.com/dataclips/idfolumrbaubogbmewdoeyahhdtj

select casa_case_id, count(*)
from case_contacts
         join casa_cases on case_contacts.casa_case_id = casa_cases.id
group by casa_case_id

1. volunteers assigned to supervisors per org https://data.heroku.com/dataclips/ymbdlyldhiiqcmsslbjfjdjmzwco

2. volunteer invitations accepted per org https://data.heroku.com/dataclips/ibzctyhepsfsgpiobxrltuhejxds

3. number of notifications by org https://data.heroku.com/dataclips/xsikhducnqfdrmfcntvdhtehuuwp

4. cases with emancipation entries per org https://data.heroku.com/dataclips/cnluraqwatwiupkkhkpueonwqcfz

5. cases with mandates per org https://data.heroku.com/dataclips/fairemyutljnkjgwldlaqtpecvvt

6. total hours in case contacts for ALL orgs https://data.heroku.com/dataclips/vgblwvzhclatsdxzdbihypqulckq

7. x case contacts by y volunteers per casa org https://data.heroku.com/dataclips/zjwklxzqruwfquvzbsovbsodipqd

select casa_org_id, sum(volunteer_count), sum(case_contacts_count)
from (
         select casa_cases.casa_org_id,
                count(distinct case_assignments.volunteer_id) as volunteer_count,
                count(*)                                      as case_contacts_count
         from case_contacts
                  join casa_cases
                       on case_contacts.casa_case_id = casa_cases.id
                  join case_assignments on casa_cases.id = case_assignments.casa_case_id
         where case_contacts.created_at > NOW() - INTERVAL '14 days'
         group by casa_cases.casa_org_id, case_assignments.volunteer_id
         order by casa_cases.casa_org_id
     ) as x
group by casa_org_id