Skip to content

Instantly share code, notes, and snippets.

@shofetim

shofetim/report.sql

Created February 17, 2020 21:11
Show Gist options
  • Save shofetim/2d4fe40b80558d01952aa6dca392abe6 to your computer and use it in GitHub Desktop.
Save shofetim/2d4fe40b80558d01952aa6dca392abe6 to your computer and use it in GitHub Desktop.
Download ZIP
Guild Report
Raw
report.sql
with work_history as (
select
distinct on (profile_id)
profile_id,
created_at
from profiles_workhistory
where finalized = true
order by profile_id, created_at asc, id
), verification as (
select
distinct on (profile_id)
profile_id,
created_at
from profiles_workhistory
where finalized = true and verifier_email is not null and verifier_email != ''
order by profile_id, created_at asc, id
), code_test_passed as (
select
distinct on (profile_id)
profile_id,
created_at
from profiles_codetest
where passed = true
order by profile_id, created_at asc, id
), code_test_failed as (
select
distinct on (profile_id)
profile_id,
created_at
from profiles_codetest
where passed = false
order by profile_id, created_at asc, id
)
select
p.name as "Name",
u.email as "Email",
p.referred_by as "Referred By",
u.date_joined::date as "First Login Date",
u.last_login::date as "Most recent login date",
p.date_completed_required_information::date as "Required Information section completed - date",
case when p.date_completed_required_information is not null then 'Required Information Complete'
else 'Required Information Incomplete'
end as "Required Information",
wh.created_at::date as "Work History entry completed - date",
case when wh.created_at is not null then 'Work History Complete'
else 'Work History Incomplete'
end as "Work History",
v.created_at::date as "Verification requested - date",
case when v.created_at is not null then 'Verification Complete'
else 'Verification Incomplete'
end as "Verification",
p.audio_cover_letter_date::date as "Audio intro uploaded - date",
case when p.audio_cover_letter_date is not null then 'Audio intro uploaded'
else 'Audio intro Incomplete'
end as "Audio intro",
code_test_passed.created_at::date as "Code test passed - date",
code_test_failed.created_at::date as "Code test failed - date",
case
when code_test_passed.created_at is not null then 'Code Test Passed'
when code_test_failed.created_at is not null then 'Code Test Failed'
else 'Code Test Incomplete'
end as "Code test"
from profiles_profile as p
left join auth_user as u on u.id = p.user_id
left join work_history as wh on wh.profile_id = p.id
left join verification as v on v.profile_id = p.id
left join code_test_passed on code_test_passed.profile_id = p.id
left join code_test_failed on code_test_failed.profile_id = p.id
guild=# \d auth_user
Table "public.auth_user"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------------------------------------
id | integer | | not null | nextval('auth_user_id_seq'::regclass)
password | character varying(128) | | not null |
last_login | timestamp with time zone | | |
is_superuser | boolean | | not null |
username | character varying(150) | | not null |
first_name | character varying(30) | | not null |
last_name | character varying(150) | | not null |
email | character varying(254) | | not null |
is_staff | boolean | | not null |
is_active | boolean | | not null |
date_joined | timestamp with time zone | | not null |
Indexes:
"auth_user_pkey" PRIMARY KEY, btree (id)
"auth_user_username_key" UNIQUE CONSTRAINT, btree (username)
"auth_user_username_6821ab7c_like" btree (username varchar_pattern_ops)
Referenced by:
TABLE "account_account" CONSTRAINT "account_account_user_id_8d4f4816_fk_auth_user_id" FOREIGN KEY (user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
TABLE "account_accountdeletion" CONSTRAINT "account_accountdeletion_user_id_c205475f_fk_auth_user_id" FOREIGN KEY (user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
TABLE "account_emailaddress" CONSTRAINT "account_emailaddress_user_id_2c513194_fk_auth_user_id" FOREIGN KEY (user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
TABLE "account_passwordexpiry" CONSTRAINT "account_passwordexpiry_user_id_905230ec_fk_auth_user_id" FOREIGN KEY (user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
TABLE "account_passwordhistory" CONSTRAINT "account_passwordhistory_user_id_dc325181_fk_auth_user_id" FOREIGN KEY (user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
TABLE "account_signupcode" CONSTRAINT "account_signupcode_inviter_id_9706983e_fk_auth_user_id" FOREIGN KEY (inviter_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
TABLE "account_signupcoderesult" CONSTRAINT "account_signupcoderesult_user_id_c10dc604_fk_auth_user_id" FOREIGN KEY (user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
TABLE "auth_user_groups" CONSTRAINT "auth_user_groups_user_id_6a12ed8b_fk_auth_user_id" FOREIGN KEY (user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
TABLE "auth_user_user_permissions" CONSTRAINT "auth_user_user_permissions_user_id_a95ead1b_fk_auth_user_id" FOREIGN KEY (user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
TABLE "django_admin_log" CONSTRAINT "django_admin_log_user_id_c564eba6_fk_auth_user_id" FOREIGN KEY (user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
TABLE "eventlog_log" CONSTRAINT "eventlog_log_user_id_a3f98596_fk_auth_user_id" FOREIGN KEY (user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
TABLE "profiles_job" CONSTRAINT "profiles_job_owner_id_209d19ea_fk_auth_user_id" FOREIGN KEY (owner_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
TABLE "profiles_note" CONSTRAINT "profiles_note_created_by_id_d201eda2_fk_auth_user_id" FOREIGN KEY (created_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
TABLE "profiles_profile" CONSTRAINT "profiles_profile_user_id_a3e81f91_fk_auth_user_id" FOREIGN KEY (user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
guild=# \d profiles_profile
Table "public.profiles_profile"
Column | Type | Collation | Nullable | Default
-------------------------------------+--------------------------+-----------+----------+----------------------------------------------
id | integer | | not null | nextval('profiles_profile_id_seq'::regclass)
user_id | integer | | not null |
bitbucket_url | character varying(250) | | not null |
current_company | character varying(250) | | not null |
currently_freelancer | boolean | | |
freelance_fulltime | boolean | | |
github_url | character varying(250) | | not null |
linkedin_url | character varying(250) | | not null |
location | character varying(250) | | not null |
other_url | character varying(250) | | not null |
phone | character varying(250) | | not null |
portfolio_url | character varying(250) | | not null |
referred_by | character varying(250) | | not null |
sauce_or_juice | character varying(50) | | not null |
stackoverflow_url | character varying(250) | | not null |
years_of_expertise | integer | | |
years_of_freelancing | integer | | |
additional_information | text | | not null |
photo | character varying(100) | | not null |
resume | character varying(100) | | not null |
work_sample | character varying(100) | | not null |
available | boolean | | |
headline | text | | not null |
lever_summary | text | | not null |
name | text | | not null |
rate | integer | | |
role | integer | | |
status | integer | | |
time_available | integer | | |
partner_id | integer | | |
lever_id | character varying(36) | | not null |
code_id | integer | | |
external_id | uuid | | not null |
contract_to_hire | boolean | | |
timezone | integer | | |
work_end | integer | | |
work_start | integer | | |
receive_job_notification_emails | boolean | | not null |
legal_workflow_complete | boolean | | |
audio_cover_letter | character varying(100) | | not null |
gun_sponsored_interview | boolean | | not null |
next | boolean | | not null |
has_seen_became_initiate_email | boolean | | not null |
staff_level_override | integer | | |
date_completed_required_information | timestamp with time zone | | |
audio_cover_letter_date | timestamp with time zone | | |
Indexes:
"profiles_profile_pkey" PRIMARY KEY, btree (id)
"profiles_profile_external_id_8437c1af_uniq" UNIQUE CONSTRAINT, btree (external_id)
"profiles_profile_user_id_key" UNIQUE CONSTRAINT, btree (user_id)
"profiles_profile_code_id_83a517cf" btree (code_id)
"profiles_profile_partner_id_91ba1edf" btree (partner_id)
Foreign-key constraints:
"profiles_profile_code_id_83a517cf_fk_account_signupcode_id" FOREIGN KEY (code_id) REFERENCES account_signupcode(id) DEFERRABLE INITIALLY DEFERRED
"profiles_profile_partner_id_91ba1edf_fk_profiles_partner_id" FOREIGN KEY (partner_id) REFERENCES profiles_partner(id) DEFERRABLE INITIALLY DEFERRED
"profiles_profile_user_id_a3e81f91_fk_auth_user_id" FOREIGN KEY (user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
TABLE "profiles_candidate" CONSTRAINT "profiles_candidate_profile_id_13715ea7_fk_profiles_profile_id" FOREIGN KEY (profile_id) REFERENCES profiles_profile(id) DEFERRABLE INITIALLY DEFERRED
TABLE "profiles_codetest" CONSTRAINT "profiles_codetest_profile_id_c4798408_fk_profiles_profile_id" FOREIGN KEY (profile_id) REFERENCES profiles_profile(id) DEFERRABLE INITIALLY DEFERRED
TABLE "profiles_interview" CONSTRAINT "profiles_interview_profile_id_d802337c_fk_profiles_profile_id" FOREIGN KEY (profile_id) REFERENCES profiles_profile(id) DEFERRABLE INITIALLY DEFERRED
TABLE "profiles_note" CONSTRAINT "profiles_note_profile_id_7dcca681_fk_profiles_profile_id" FOREIGN KEY (profile_id) REFERENCES profiles_profile(id) DEFERRABLE INITIALLY DEFERRED
TABLE "profiles_profile_skills" CONSTRAINT "profiles_profile_ski_profile_id_1a5be025_fk_profiles_" FOREIGN KEY (profile_id) REFERENCES profiles_profile(id) DEFERRABLE INITIALLY DEFERRED
TABLE "profiles_profile_top_skills" CONSTRAINT "profiles_profile_top_profile_id_7d30b29a_fk_profiles_" FOREIGN KEY (profile_id) REFERENCES profiles_profile(id) DEFERRABLE INITIALLY DEFERRED
TABLE "profiles_workhistory" CONSTRAINT "profiles_workhistory_profile_id_21a46fc8_fk_profiles_profile_id" FOREIGN KEY (profile_id) REFERENCES profiles_profile(id) DEFERRABLE INITIALLY DEFERRED
guild=# \d profiles_workhistory
Table "public.profiles_workhistory"
Column | Type | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+--------------------------------------------------
id | integer | | not null | nextval('profiles_workhistory_id_seq'::regclass)
role | text | | not null |
start | date | | |
end | date | | |
current | boolean | | |
description | text | | not null |
verifier_name | text | | not null |
verifier_email | text | | not null |
profile_id | integer | | not null |
hidden | boolean | | |
finalized | boolean | | not null |
external_id | uuid | | not null |
created_at | timestamp with time zone | | not null |
Indexes:
"profiles_workhistory_pkey" PRIMARY KEY, btree (id)
"profiles_workhistory_external_id_key" UNIQUE CONSTRAINT, btree (external_id)
"profiles_workhistory_profile_id_21a46fc8" btree (profile_id)
Foreign-key constraints:
"profiles_workhistory_profile_id_21a46fc8_fk_profiles_profile_id" FOREIGN KEY (profile_id) REFERENCES profiles_profile(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
TABLE "profiles_reference" CONSTRAINT "profiles_reference_work_history_id_22b3e73a_fk_profiles_" FOREIGN KEY (work_history_id) REFERENCES profiles_workhistory(id) DEFERRABLE INITIALLY DEFERRED
TABLE "profiles_workhistory_skills" CONSTRAINT "profiles_workhistory_workhistory_id_fa6ddf0c_fk_profiles_" FOREIGN KEY (workhistory_id) REFERENCES profiles_workhistory(id) DEFERRABLE INITIALLY DEFERRED
guild=# \d profiles_codetest
Table "public.profiles_codetest"
Column | Type | Collation | Nullable | Default
------------+--------------------------+-----------+----------+-----------------------------------------------
id | integer | | not null | nextval('profiles_codetest_id_seq'::regclass)
passed | boolean | | |
profile_id | integer | | |
score | integer | | |
skill_id | integer | | |
time_end | timestamp with time zone | | |
time_start | timestamp with time zone | | not null |
created_at | timestamp with time zone | | not null |
Indexes:
"profiles_codetest_pkey" PRIMARY KEY, btree (id)
"profiles_codetest_profile_id_c4798408" btree (profile_id)
"profiles_codetest_skill_id_c10f29fc" btree (skill_id)
Foreign-key constraints:
"profiles_codetest_profile_id_c4798408_fk_profiles_profile_id" FOREIGN KEY (profile_id) REFERENCES profiles_profile(id) DEFERRABLE INITIALLY DEFERRED
"profiles_codetest_skill_id_c10f29fc_fk_profiles_skill_id" FOREIGN KEY (skill_id) REFERENCES profiles_skill(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
TABLE "profiles_codetestresponse" CONSTRAINT "profiles_codetestres_code_test_id_f52cbdec_fk_profiles_" FOREIGN KEY (code_test_id) REFERENCES profiles_codetest(id) DEFERRABLE INITIALLY DEFERRED
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment