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

select casa_org_id, count(distinct volunteer_id)
from (
         select casa_org_id, volunteer_id
         from supervisor_volunteers
                  join users on supervisor_volunteers.volunteer_id = users.id
         where users.type = 'Volunteer'
           and users.active = true
           and supervisor_volunteers.is_active = true
         group by users.casa_org_id, volunteer_id
     ) x
group by 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 accepted, count(*)
from users
group by casa_org_id, invitation_accepted_at is not NULL

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
group by users.casa_org_id

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

select casa_org_id, count(casa_case_id)
from casa_case_emancipation_categories
         join casa_cases on casa_case_emancipation_categories.casa_case_id = casa_cases.id
group by casa_org_id, casa_case_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