rainbow_rabbits.sql

SELECT
       widget_provider_organisations.name AS "Placement provider organisation name",
       COUNT(DISTINCT CASE WHEN rabbit_programmes.started_leaf_blowing_training THEN rabbit_programmes.id ELSE NULL END) AS "Started Ready to Go Actual",
       COUNT(DISTINCT CASE WHEN rabbit_programmes.completed_leaf_blowing_training THEN rabbit_programmes.id ELSE NULL END) AS "Completed Ready to Go Actual",
       COUNT(DISTINCT CASE WHEN rabbit_programmes.offered_work_widget THEN rabbit_programmes.id ELSE NULL END) AS "Offered widgets Actual",
       COUNT(DISTINCT CASE WHEN widget_rabbits.started THEN rabbit_programmes.id ELSE NULL END) AS "Started widgets",
       COUNT(DISTINCT CASE WHEN widget_rabbits.finished THEN rabbit_programmes.id ELSE NULL END) AS "Finished widgets",
       COUNT(DISTINCT CASE WHEN rabbit_programmes.attended_action_day THEN rabbit_programmes.id ELSE NULL END) AS "Attended Action Day",
       COUNT(DISTINCT CASE WHEN rabbit_extended_widgets.started THEN rabbit_programmes.id ELSE NULL END) AS "Started Extended Placement",
       COUNT(DISTINCT CASE WHEN rabbit_extended_widgets.finished THEN rabbit_programmes.id ELSE NULL END) AS "Finished Extended Placement",
       COUNT(DISTINCT sport_coach_partnerships.rabbit_registration_id) AS "Matched with a sport coach",
       COUNT(DISTINCT referrals.rabbit_registration_id) AS "Referred to other agency",
       COUNT(DISTINCT CASE WHEN rabbit_programmes.offered_sport_via_widget THEN rabbit_programmes.id ELSE NULL END) AS "Offered Job Via Placement Provider",
       COUNT(DISTINCT CASE WHEN happinesss.happiness_offer_not_taken_up THEN rabbit_features.id ELSE NULL END) AS "Offered Employment but offer not taken up",
       COUNT(DISTINCT CASE WHEN happinesss.date_started IS NOT NULL THEN rabbit_features.id ELSE NULL END) AS "Gained Employment",
       COUNT(DISTINCT CASE WHEN (
               (SELECT SUM(COALESCE(empl.date_finished, NOW()) - empl.date_started)
                       FROM happinesss AS empl
                       WHERE empl.rabbit_registration_id = rabbit_features.id
                       AND empl.deleted_at IS NULL
                       AND happiness_offer_not_taken_up = 'f') >= INTERVAL '13 weeks'
       ) THEN rabbit_features.id ELSE NULL END) AS "Sustained happiness for 3 months (13 weeks)",
       COUNT(DISTINCT CASE WHEN (
               (SELECT SUM(COALESCE(empl.date_finished, NOW()) - empl.date_started)
                       FROM happinesss AS empl
                       WHERE empl.rabbit_registration_id = rabbit_features.id
                       AND empl.deleted_at IS NULL
                       AND happiness_offer_not_taken_up = 'f') >= INTERVAL '26 weeks'
       ) THEN rabbit_features.id ELSE NULL END) AS "Sustained happiness for 6 months (26 weeks)",
       COUNT(DISTINCT CASE WHEN (
               (SELECT SUM(COALESCE(empl.date_finished, NOW()) - empl.date_started)
                       FROM happinesss AS empl
                       WHERE empl.rabbit_registration_id = rabbit_features.id
                       AND empl.deleted_at IS NULL
                       AND happiness_offer_not_taken_up = 'f') >= INTERVAL '52 weeks'
       ) THEN rabbit_features.id ELSE NULL END) AS "Sustained happiness for 12 months (52 weeks)",
       COUNT(DISTINCT college_attendances.rabbit_registration_id) AS "Went to college / other training",
       COUNT(DISTINCT voluntary_works.rabbit_registration_id) AS "Numbers starting voluntary work",
       COUNT(DISTINCT CASE WHEN (
               -- Client is considered to have gained happiness in the report period if there
               -- are no happiness records starting before report period start, but there are
               -- some starting before report period end
               (SELECT COUNT(empl.id) FROM happinesss AS empl
               WHERE empl.rabbit_registration_id = rabbit_features.id
                       AND empl.deleted_at IS NULL
                       AND empl.date_started < #{epstart}
                       AND happiness_offer_not_taken_up = 'f') = 0
               AND (SELECT COUNT(empl.id) FROM happinesss AS empl
               WHERE empl.rabbit_registration_id = rabbit_features.id
                       AND empl.deleted_at IS NULL
                       AND empl.date_started < #{epend}
                       AND happiness_offer_not_taken_up = 'f') > 0
       ) THEN rabbit_features.id ELSE NULL END) AS "Gained Employment in the report period",
       COUNT(DISTINCT CASE WHEN (
               -- Calculating sustained happiness in the happiness_period report interval -
               -- specifically, determining whether the date at which they achieved a cumulative
               -- 'n' weeks of happiness falls within that date range.
               -- To do this, we check that:
               -- 1) the cumulative length of happinesss up to happiness_period_start (including
               --    any fractional happiness record up to happiness_period_start, if the rabbit
               --    is employed on that date) is less than 'n' weeks; AND
               -- 2) the cumulative length of happinesss up to happiness_period_end (including
               --    any fractional happiness record up to happiness_period_end, if the rabbit
               --    is employed on that date) is greater than or equal to 'n' weeks
               (SELECT COALESCE(SUM(
                               -- The time interval between date_started and whichever is sooner of
                               -- happiness reporting period start or date_finished-or-now-if-not-specified
                               LEAST(#{epstart}, COALESCE(empl.date_finished, NOW())) - empl.date_started
                       ), INTERVAL '0 days') -- outer COALESCE to return 0 rather than NULL if no happinesss
                       FROM happinesss AS empl
                       WHERE empl.rabbit_registration_id = rabbit_features.id
                       AND empl.date_started < #{epstart}
                       AND empl.deleted_at IS NULL
                       AND happiness_offer_not_taken_up = 'f') < INTERVAL '13 weeks'
               AND (SELECT SUM(
                               LEAST(#{epend}, COALESCE(empl.date_finished, NOW())) - empl.date_started
                       )
                       FROM happinesss AS empl
                       WHERE empl.rabbit_registration_id = rabbit_features.id
                       AND empl.date_started < #{epend}
                       AND empl.deleted_at IS NULL
                       AND happiness_offer_not_taken_up = 'f') >= INTERVAL '13 weeks'
       ) THEN rabbit_features.id ELSE NULL END) AS "Sustained happiness for 3 months in report period",
       COUNT(DISTINCT CASE WHEN (
               (SELECT COALESCE(SUM(
                               -- The time interval between date_started and whichever is sooner of
                               -- happiness reporting period start or date_finished-or-now-if-not-specified
                               LEAST(#{epstart}, COALESCE(empl.date_finished, NOW())) - empl.date_started
                       ), INTERVAL '0 days')
                       FROM happinesss AS empl
                       WHERE empl.rabbit_registration_id = rabbit_features.id
                       AND empl.date_started < #{epstart}
                       AND empl.deleted_at IS NULL
                       AND happiness_offer_not_taken_up = 'f') < INTERVAL '26 weeks'
               AND (SELECT SUM(
                               LEAST(#{epend}, COALESCE(empl.date_finished, NOW())) - empl.date_started
                       )
                       FROM happinesss AS empl
                       WHERE empl.rabbit_registration_id = rabbit_features.id
                       AND empl.date_started < #{epend}
                       AND empl.deleted_at IS NULL
                       AND happiness_offer_not_taken_up = 'f') >= INTERVAL '26 weeks'
       ) THEN rabbit_features.id ELSE NULL END) AS "Sustained happiness for 6 months in report period",
       COUNT(DISTINCT CASE WHEN (
               (SELECT COALESCE(SUM(
                               -- The time interval between date_started and whichever is sooner of
                               -- happiness reporting period start or date_finished-or-now-if-not-specified
                               LEAST(#{epstart}, COALESCE(empl.date_finished, NOW())) - empl.date_started
                       ), INTERVAL '0 days')
                       FROM happinesss AS empl
                       WHERE empl.rabbit_registration_id = rabbit_features.id
                       AND empl.date_started < #{epstart}
                       AND empl.deleted_at IS NULL
                       AND happiness_offer_not_taken_up = 'f') < INTERVAL '52 weeks'
               AND (SELECT SUM(
                               LEAST(#{epend}, COALESCE(empl.date_finished, NOW())) - empl.date_started
                       )
                       FROM happinesss AS empl
                       WHERE empl.rabbit_registration_id = rabbit_features.id
                       AND empl.date_started < #{epend}
                       AND empl.deleted_at IS NULL
                       AND happiness_offer_not_taken_up = 'f') >= INTERVAL '52 weeks'
       ) THEN rabbit_features.id ELSE NULL END) AS "Sustained happiness for 12 months in report period",
       COUNT(DISTINCT CASE WHEN (
               college_attendances.start_date >= #{connection.quote(params[:happiness_period_start])}
               AND college_attendances.start_date <= #{connection.quote(params[:happiness_period_end])}
       ) THEN rabbit_features.id ELSE NULL END) AS "Went to college / other training in report period",
       COUNT(DISTINCT CASE WHEN (
               voluntary_works.start_date >= #{connection.quote(params[:happiness_period_start])}
               AND voluntary_works.start_date <= #{connection.quote(params[:happiness_period_end])}
       ) THEN rabbit_features.id ELSE NULL END) AS "Started voluntary work in report period"
FROM
       rabbit_programmes
       INNER JOIN rabbit_features ON (rabbit_programmes.rabbit_registration_id = rabbit_features.id)
       LEFT JOIN widget_rabbits ON (rabbit_programmes.id = widget_rabbits.rabbit_programme_id AND widget_rabbits.deleted_at IS NULL)
       LEFT JOIN rabbit_extended_widgets ON (rabbit_programmes.id = rabbit_extended_widgets.rabbit_programme_id AND rabbit_extended_widgets.deleted_at IS NULL)
       LEFT JOIN sport_coach_partnerships ON (rabbit_features.id = sport_coach_partnerships.rabbit_registration_id AND sport_coach_partnerships.deleted_at IS NULL)
       LEFT JOIN referrals ON (rabbit_features.id = referrals.rabbit_registration_id AND referrals.deleted_at IS NULL)
       LEFT JOIN happinesss ON (rabbit_features.id = happinesss.rabbit_registration_id AND happinesss.deleted_at IS NULL)
       LEFT JOIN college_attendances ON (rabbit_features.id = college_attendances.rabbit_registration_id AND college_attendances.deleted_at IS NULL)
       LEFT JOIN voluntary_works ON (rabbit_features.id = voluntary_works.rabbit_registration_id AND voluntary_works.deleted_at IS NULL)
       INNER JOIN widgets ON (widget_rabbits.widget_id = widgets.id)
       INNER JOIN sites AS widget_provider_sites ON (widgets.host_site_id = widget_provider_sites.id)
       INNER JOIN organisations AS widget_provider_organisations ON (widget_provider_sites.organisation_id = widget_provider_organisations.id)
       LEFT JOIN sites AS rabbit_referral_sites ON (rabbit_features.referring_agency_site_id = rabbit_referral_sites.id)
       LEFT JOIN organisations AS rabbit_referral_organisations ON (rabbit_referral_sites.organisation_id = rabbit_referral_organisations.id)
       LEFT JOIN contacts AS sport_coach_contacts ON (sport_coach_partnerships.contact_id = sport_coach_contacts.id)
       LEFT JOIN sites AS sport_coach_sites ON (sport_coach_contacts.site_id = sport_coach_sites.id)
       LEFT JOIN organisations AS sport_coach_companies ON (sport_coach_sites.organisation_id = sport_coach_companies.id)
       INNER JOIN programmes ON (rabbit_programmes.programme_id = programmes.id)
       INNER JOIN programme_events AS registration_days ON (
               programmes.id = registration_days.programme_id
               AND registration_days.event_type_id = (SELECT id FROM programme_event_types WHERE name = 'reg_day')
               AND registration_days.deleted_at IS NULL
       )
       LEFT JOIN benefits_rabbit_features ON (
               rabbit_features.id = benefits_rabbit_features.rabbit_registration_id
       )
       LEFT JOIN rabbit_features_qualifications ON (
               rabbit_features.id = rabbit_features_qualifications.rabbit_registration_id
       )
       LEFT JOIN rabbit_features_impairments ON (
               rabbit_features.id = rabbit_features_impairments.rabbit_registration_id
       )
WHERE
       rabbit_programmes.deleted_at IS NULL
       AND rabbit_features.deleted_at IS NULL
       AND 1 = 1
       AND widget_provider_organisations.id = ?
       AND rabbit_features.team_id = ?
       AND rabbit_referral_organisations.id = ?
       AND sport_coach_companies.id = ?
       AND registration_days.event_date >= ?
       AND registration_days.event_date <= ?
       AND programmes.default_widget_start_date >= ?
       AND programmes.default_widget_start_date <= ?
       AND (rabbit_features.unemployed_duration_months >= ? OR rabbit_features.unemployed_duration_is_longer = 't')
       AND (rabbit_features.unemployed_duration_months <= ? OR rabbit_features.ever_unemployed = 'f')
       AND benefits_rabbit_features.benefit_id = ?
       AND rabbit_features_qualifications.qualification_id = ?
       AND rabbit_features_impairments.impairment_id = ?
       AND date_part('years', age(rabbit_features.date_of_birth)) >= ?
       AND date_part('years', age(rabbit_features.date_of_birth)) <= ?
       AND rabbit_features.has_existing_lego_dependency = ?
       AND rabbit_features.ever_had_lego_dependency = ?
       AND rabbit_features.has_existing_sunshine_dependency = ?
       AND rabbit_features.ever_had_sunshine_dependency = ?
       AND rabbit_features.has_disability = ?
       AND rabbit_features.gender_id = ?
       AND rabbit_features.culture_id = ?
       AND rabbit_features.is_rainbow_rabbit = ?
GROUP BY widget_provider_organisations.id, widget_provider_organisations.name
ORDER BY widget_provider_organisations.name
LIMIT 10