Created
September 26, 2022 15:13
-
-
Save buckmaxwell/246895e18b1b143066a76a11a8b158cc to your computer and use it in GitHub Desktop.
Get Common Sitter Groups From BSP
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 | |
; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment