Get Common Sitter Groups From BSP

babysitter_success_progress_groups.sql

-- users in city without complete profile
with city as (
select poly from sitter_area area where area.name = 'Cincinnati'
)

select u.id, u.first_name, u.last_name from sitter_user u
join sitter_babysitter b on b.user_id = u.id
join sitter_babysittersuccessprogress bsp on bsp.babysitter_id = b.id
join sitter_address a on a.user_id = u.id and ST_WITHIN(a.point, (select poly from city))
where bsp.completed_profile is null
order by u.id desc
;


-- users in city with complete profile and > 18 years and background check was not initiated or was never completed
with city as (
select poly from sitter_area area where area.name = 'Cincinnati'
)

select * from sitter_user u
join sitter_babysitter b on b.user_id = u.id
join sitter_babysittersuccessprogress bsp on bsp.babysitter_id = b.id
join sitter_address a on a.user_id = u.id and ST_WITHIN(a.point, (select poly from city))

where bsp.completed_profile is not null
and age(b.birthday) > '18 years'::interval
and bsp.provider_completed_bci_fbi_background_check is null
and ((bsp.bci_fbi_appointment_date is null or bsp.bci_fbi_appointment_date < now() - '1 week'::interval) and bsp.billed_for_fingerprinting is null)

order by u.id desc
;

-- users in city with complete profile and initiated bci fbi and > 18 years and fingerprint was billed or date has not passed or is not far in the past
with city as (
select poly from sitter_area area where area.name = 'Columbus'
)

select * from sitter_user u
join sitter_babysitter b on b.user_id = u.id
join sitter_babysittersuccessprogress bsp on bsp.babysitter_id = b.id
join sitter_address a on a.user_id = u.id and ST_WITHIN(a.point, (select poly from city))

where bsp.completed_profile is not null
and bsp.scheduled_bci_fbi_over_the_phone is not null
and bsp.provider_completed_bci_fbi_background_check is null
and (bsp.bci_fbi_appointment_date > now() - '1 week'::interval or bsp.billed_for_fingerprinting is null)
and age(b.birthday) > '18 years'::interval
order by u.id desc
;


-- users in city with complete profile and completed bci fbi and > 18 years
with city as (
select poly from sitter_area area where area.name = 'Columbus'
)

select * from sitter_user u
join sitter_babysitter b on b.user_id = u.id
join sitter_babysittersuccessprogress bsp on bsp.babysitter_id = b.id
join sitter_address a on a.user_id = u.id and ST_WITHIN(a.point, (select poly from city))

where bsp.completed_profile is not null
and bsp.provider_completed_bci_fbi_background_check is not null
and age(b.birthday) > '18 years'::interval
order by u.id desc
;