45:["$","title",null,{"children":"Team Phone Screen Dashboard | Alexey Komissarouk | Observable"}] 4d:T6a2,onsite_query = lever(` WITH technical_interviews AS ( SELECT c.id as candidate_id, f.completed_at FROM public.feedback f INNER JOIN public.candidates c ON f.candidate = c.id WHERE f.completed_at IS NOT NULL AND f.text IN ( 'Tech Phone Interview', 'On-site interview', 'Systems Design', 'Whiteboard', 'Pair Eng Problem', 'Pair DS', 'Algorithms Session', 'Lunch w/ Candidate', 'Lunch + Culture', 'Lunch + culture', 'Lunch', 'Culture' ) ), candidate_rollup as ( SELECT c.name, ar.text, ( SELECT min(completed_at) FROM technical_interviews WHERE c.id = technical_interviews.candidate_id ) AS first_interview_date, -- These queries should really be done using JSONB but this was faster c.stage_changes::text LIKE '%39ba596d-afd9-4869-aca8-c4f3b61fb591%' as phone_screened, c.stage_changes::text LIKE '%a97211c4-e7d9-4dde-ae7b-a9f0ce28c281%' as invited_onsite, c.stage_changes::text LIKE '%0f3a1261-2512-4706-a434-1e13358c5f19%' as offered, ar.text = 'Hired' as hired, (SELECT COUNT(*) from technical_interviews WHERE c.id = technical_interviews.candidate_id) as hours_of_interviews FROM public.candidates c INNER JOIN public.stages ON c.stage = stages.id LEFT JOIN public.archive_reasons ar ON ar.id = (c.archived->>'reason')::uuid WHERE c.archived IS NOT NULL AND ar.text != 'Withdrew' -- arguably, nothing to be done if they never onsited ) select * from candidate_rollup WHERE invited_onsite AND first_interview_date IS NOT NULL order by first_interview_date DESC LIMIT 100 `)4e:[] 49:{"id":"89b5e5a798472f22","slug":"team-phone-screen-dashboard","title":"Team Phone Screen Dashboard","thumbnail":"6d5f79a117ef2548564b7f1cb6d8f9baaaf9f819b6b9159554c5fd7cd132fafb","access_level":"public","create_time":"$D2018-02-26T20:29:38.566Z","update_time":"$D2018-02-27T20:37:56.012Z","view_time":null,"first_public_time":"$D2018-02-26T21:11:16.330Z","has_unpublished_edits":null,"fork":{"id":"2a1f7b7fb63769df","slug":"interviewer-phone-screen-dashboard","title":"Interviewer Phone Screen Dashboard","owner":{"id":"e14ef6c8566ebea1","login":"alexeymk","name":"Alexey Komissarouk"}},"stars":0,"starred":false,"owner":{"type":"team","id":"e14ef6c8566ebea1","login":"alexeymk","name":"Alexey Komissarouk","avatar_url":"https://avatars-next.observableusercontent.com/0f6d561d4d7c5f25de8ea2c87156dea3b3b50b961c90f1294c4c5cf8ca6fdb8f","tier":"starter_2024","bio":"","followed_by_you":false,"follows_you":false},"version":1156,"publish_version":1156,"role":null,"authored":false,"authors":[{"type":"individual","id":"0c73579b7872f3ef","login":"alexeymk","name":"Alexey Komissarouk","avatar_url":"https://avatars-next.observableusercontent.com/0f6d561d4d7c5f25de8ea2c87156dea3b3b50b961c90f1294c4c5cf8ca6fdb8f","tier":null,"bio":"","followed_by_you":false,"follows_you":false}],"members":[{"type":"individual","id":"0c73579b7872f3ef","login":"alexeymk","name":"Alexey Komissarouk","avatar_url":"https://avatars-next.observableusercontent.com/0f6d561d4d7c5f25de8ea2c87156dea3b3b50b961c90f1294c4c5cf8ca6fdb8f","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":null,"type":"notebook","body":{"titling":"auto","title":"Team Phone Screen Dashboard","cells":[{"id":0,"value":"md`# Team Phone Screen Dashboard`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":953,"value":"viewof startDate = html``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":432,"value":"query = lever(`\n WITH rollup as (\n select u.name,\n count(*) as count_phone_screens,\n count(*) FILTER (WHERE SUBSTR(f.fields->0->>'value', 1, 1) IN ('3', '4')) as invited_onsites,\n count(*) FILTER (WHERE SUBSTR(f.fields->0->>'value', 1, 1) IN ('3', '4') AND stages.text IN ('Reference Check', 'Offer')) as offers,\n count(*) FILTER (WHERE ar.text = 'Hired') as hires\n from public.feedback f \n INNER JOIN public.users u ON u.id = f.user\n INNER JOIN public.candidates c ON f.candidate = c.id\n INNER JOIN public.stages ON c.stage = stages.id \n LEFT JOIN public.archive_reasons ar ON ar.id = (c.archived->>'reason')::uuid\n WHERE f.text = 'Tech Phone Interview'\n AND f.completed_at::DATE > '${d3.isoFormat(startDate)}'\n GROUP BY 1\n )\n\n SELECT *,\n invited_onsites::FLOAT / GREATEST(1, count_phone_screens) as percent_invited_to_onsite,\n offers::FLOAT / GREATEST(1, invited_onsites) as onsite_pass_rate\n FROM rollup\n ORDER BY 2 DESC\n`)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1036,"value":"interviewers = query.rows.map(i => {\n i.short_name = name(i.name)\n return i\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1010,"value":"vegalite({\n width: width,\n height: width / 2,\n data: {\n values: interviewers,\n },\n layer: [{\n mark: \"point\",\n encoding: {\n x: {\n field: \"percent_invited_to_onsite\"\n },\n y: {\n field: \"onsite_pass_rate\",\n },\n size: {\n field: \"count_phone_screens\"\n },\n tooltip: {\n field: \"name\"\n }\n }\n }, {\n mark: {\n type: \"text\",\n align: \"left\",\n dx: 4,\n angle: -25,\n },\n encoding: {\n x: {\n field: \"percent_invited_to_onsite\"\n },\n y: {\n field: \"onsite_pass_rate\",\n },\n text: {\n field: \"short_name\"\n },\n }\n }]\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":45,"value":"intereviewers = table(query)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1037,"value":"function name(fullName) {\n return fullName.split(' ').map((s, i) => i === 0 ? s : s[0]).join(' ')\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":235,"value":"lever = async function(sql) {\n const response = await fetch(`https://opendoor-lever.herokuapp.com/db?q=${encodeURIComponent(sql)}`, {credentials: 'include'})\n return response.json()\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":260,"value":"_ = require('lodash')","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1052,"value":"table = (query) => { \n return html`\n \n \n ${query.fields.map((f) => ``)}\n \n ${query.rows.map((r) => `\n \n ${query.fields.map((f) => ``).join('')}\n \n `)}\n

${f.name}
${r[f.name]}

\n `\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":66,"value":"vegalite = require(\"@observablehq/vega-lite@0.1\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":375,"value":"d3 = require('d3')","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1050,"value":"$4d","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1059,"value":"recent_onsiters = table(onsite_query)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W4e","stdlib":"1"}} 44:null