Metrics [prod]

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

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

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

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

select users.casa_org_id, count(*)
from supervisor_volunteers
         join users on supervisor_volunteers.supervisor_id = users.id
where supervisor_volunteers.is_active = true
group by users.casa_org_id
order by users.casa_org_id

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

select casa_org_id,
       invitation_accepted_at is not NULL as active_volunteer_invitations_accepted,
       count(*)
from users
where active = true
  and type = 'Volunteer'
group by casa_org_id, invitation_accepted_at is not NULL
order by casa_org_id asc, active_volunteer_invitations_accepted desc;

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

select users.casa_org_id, count(*)
from notifications
         join users on recipient_id = users.id
where recipient_type = 'User'
group by users.casa_org_id

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

select casa_org_id, count(*)
from (
         select casa_org_id, casa_case_id, count(*)
         from casa_cases_emancipation_options
                  join casa_cases on casa_cases_emancipation_options.casa_case_id = casa_cases.id
         group by casa_org_id, casa_case_id
     ) as cases_with_emancipation_used group by casa_org_id

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

select casa_org_id, count(distinct casa_case_id), sum(mandates_count)
from (
         select casa_org_id, casa_case_id, count(*) as mandates_count
         from case_court_mandates
                  join casa_cases on case_court_mandates.casa_case_id = casa_cases.id
         group by casa_org_id, casa_case_id
     ) as x
group by casa_org_id

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

select sum(duration_minutes) / 60.0 as hours from case_contacts 

1. total hours in case contacts by org

select casa_org_id, sum(duration_minutes)
from case_contacts
         join casa_cases on case_contacts.casa_case_id = casa_cases.id
group by casa_org_id

1. 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