30:T7ed,viewof interviews = lever(` select u.name as interviewer, i.id, case when max(f.fields->2->'value'->>0) like 'Other%' then 'Other' else lower(max(f.fields->2->'value'->>0)) end as question, max(f.fields->0->>'value') as rating, bool_or(f.fields->0->>'value' in ('3 - Hire', '4 - Strong Hire')) as pass, date_trunc('week', i.date) as week, i.date, i.feedback_template, i.subject, s.text as stage, ar.text as archived, case when bool_or(s.text in ('Reference Check', 'Offer')) then 'true' when c.archived is null then null when ar.text in ('Withdrew', 'Timing', 'Unresponsive', 'Cleanup') then 'ghosted' else 'false' end as offer, c.name as candidate, c.urls->>'show' as url, o.name as owner from interviews i join interviewers ii on ii.interview = i.id join users u on ii.user = u.id join candidates c on i.candidate = c.id join users o on c.owner = o.id join stages s on c.stage = s.id left join feedback f on f.interview = i.id left join archive_reasons ar on (c.archived->>'reason')::uuid = ar.id where i.feedback_template in ( '1f5520ee-98e3-4b5f-bea5-a82b7b006f81', -- Algorithms Session '2afbf449-d595-4c30-88d3-5ac2b9849a54', -- Culture '018ca013-4c34-4e9c-9baf-28bd061b47f7', -- Lunch + culture '6023dce2-104c-4d5c-af55-9d8eeb5b97a4', -- Pair DS 'fb1e02f4-b74e-48c3-b4ef-daca8e9029d1', -- Pair Eng Problem '3a3e7c77-aad1-42a5-b6a7-c20edde774f6', -- Pair Eng w/ Hiring Manager '73d0c66d-9ed1-4bc9-a77f-bb2088c15784', -- Post-sync 'fae8a918-fc99-4af5-b2f9-c893fc2e51e6' -- Systems Design ) and ('${interviewer}' = '' or u.name = '${interviewer}') and u.name != 'Madina Farid' -- stole our culture template and i.canceled_at is null and i.date between now() - interval '${weeks-3} weeks' and now() + interval '3 weeks' group by u.name, i.id, s.text, ar.text, c.name, c.urls->>'show', o.name, c.archived order by date_trunc('week', i.date) desc, u.name asc `)31:[] 2e:{"title":"On-sites","cells":[{"id":0,"value":"md`# On-sites`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1564,"value":"viewof interviewers = lever(`\n select u.name, count(*)\n from interviews i\n join interviewers ii on ii.interview = i.id\n join users u on ii.user = u.id\n where i.feedback_template in (\n '1f5520ee-98e3-4b5f-bea5-a82b7b006f81', -- Algorithms Session\n '2afbf449-d595-4c30-88d3-5ac2b9849a54', -- Culture\n '018ca013-4c34-4e9c-9baf-28bd061b47f7', -- Lunch + culture\n '6023dce2-104c-4d5c-af55-9d8eeb5b97a4', -- Pair DS\n 'fb1e02f4-b74e-48c3-b4ef-daca8e9029d1', -- Pair Eng Problem\n '3a3e7c77-aad1-42a5-b6a7-c20edde774f6', -- Pair Eng w/ Hiring Manager\n '73d0c66d-9ed1-4bc9-a77f-bb2088c15784', -- Post-sync\n 'fae8a918-fc99-4af5-b2f9-c893fc2e51e6' -- Systems Design\n )\n and i.canceled_at is null\n and i.date > now() - interval '${weeks} weeks'\n group by u.name\n order by count(*) desc\n`)","pinned":false,"hidden":false,"mode":"ojs"},{"id":1297,"value":"viewof interviewer = html`\n\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":27,"value":"$30","pinned":false,"hidden":false,"mode":"ojs"},{"id":18,"value":"md`## Hours per hire\n\nTODO`","pinned":false,"hidden":false,"mode":"ojs"},{"id":7,"value":"md`## Scores to hires\n\nWhat scores are hires getting? What scores are underqualified candidates getting?`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1571,"value":"vegalite({\n data: { values: interviews.rows },\n width: width - 190,\n height: 100,\n config: { padding: { left: 30 } },\n mark: 'square',\n encoding: {\n x,\n y: { field: 'rating', type: 'ordinal', axis: { orient: 'right', offset: 20 } },\n size: { aggregate: 'count', type: 'quantitative', scale: { range: [ 80, 600 ] }, legend: false },\n color: { field: 'rating', type: 'ordinal', scale: { domain: ratings.filter(r => r), range: 'diverging' }, legend: false },\n row: { field: 'offer', type: 'nominal' },\n }\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":5,"value":"md`## Volume`","pinned":false,"hidden":false,"mode":"ojs"},{"id":678,"value":"md`### Interviews per week\n\nHow many interviews are we conducting per week? How many are scheduled?`","pinned":false,"hidden":false,"mode":"ojs"},{"id":50,"value":"vegalite({\n data: { values: interviews.rows },\n width: width - 190,\n height: 100,\n config: {\n axisLeft: { minExtent: 96, maxExtent: 96 }\n },\n layer: [{\n mark: 'bar',\n encoding: {\n x,\n y: { aggregate: 'count', axis: { orient: 'right' } },\n size: { value: (width-190)/weeks - 2 },\n color: { aggregate: 'count', type: 'quantitative', scale: { range: 'heatmap' }, legend: false },\n // detail: { field: 'pass', type: 'ordinal', scale: { domain: [true, false, null], range: [1, 0.5, 0.2] } },\n }\n }, {\n mark: { type: 'text', dy: -4 },\n encoding: {\n x,\n y: { aggregate: 'count', type: 'quantitative' },\n text: { aggregate: 'count', type: 'quantitative' },\n } \n }]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1553,"value":"md`### Candidates\n\nHow many candidates are we interviewing?`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1546,"value":"vegalite({\n data: { values: interviews.rows },\n width: width - 190,\n height: 100,\n config: {\n axisLeft: { minExtent: 96, maxExtent: 96 }\n },\n layer: [{\n mark: 'bar',\n encoding: {\n x,\n y: { aggregate: 'distinct', field: 'candidate', axis: { orient: 'right' } },\n size: { value: (width-190)/weeks - 2 },\n color: { aggregate: 'count', type: 'quantitative', scale: { range: 'heatmap' }, legend: false },\n // detail: { field: 'pass', type: 'ordinal', scale: { domain: [true, false, null], range: [1, 0.5, 0.2] } },\n }\n }, {\n mark: { type: 'text', dy: -4 },\n encoding: {\n x,\n y: { aggregate: 'distinct', field: 'candidate' },\n text: { aggregate: 'distinct', field: 'candidate' },\n } \n }]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":680,"value":"md`### Interviewers\n\nWho is performing the interviews?`","pinned":false,"hidden":false,"mode":"ojs"},{"id":176,"value":"viewof atLeastPerWeek = html``","pinned":false,"hidden":false,"mode":"ojs"},{"id":42,"value":"vegalite({\n data: { values: interviews.rows.filter(i => interviewTotals[i.interviewer] > atLeastPerWeek) },\n width: width - 200,\n config: { padding: { left: 30 } },\n layer: [{\n mark: 'square',\n encoding: {\n x,\n y: { field: 'interviewer', type: 'nominal', sort: { op: 'count', order: 'descending' }, axis: { orient: 'right' } },\n size: { aggregate: 'count', type: 'quantitative', scale: { range: [40, 500] }, legend: false },\n color: { aggregate: 'count', type: 'quantitative', scale: { range: 'heatmap' }, legend: false },\n }\n }, {\n mark: { type: 'text', dy: 4 },\n encoding: {\n x,\n y: { field: 'interviewer', type: 'nominal', sort: { op: 'count', order: 'descending' } },\n text: { aggregate: 'count', type: 'quantitative' },\n color: { value: 'white' },\n }\n }]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":140,"value":"interviewTotals = _.countBy(interviews.rows, 'interviewer')","pinned":false,"hidden":false,"mode":"ojs"},{"id":1143,"value":"md`### Source\n\nWhere are the candidates coming from?`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1145,"value":"vegalite({\n data: { values: interviews.rows },\n width: width - 200,\n config: { padding: { left: 30 } },\n layer: [{\n mark: 'square',\n encoding: {\n x,\n y: { field: 'owner', type: 'nominal', sort: { op: 'count', order: 'descending' }, axis: { orient: 'right' } },\n size: { aggregate: 'distinct', field: 'candidate', type: 'quantitative', scale: { range: [40, 500] }, legend: false },\n color: { aggregate: 'distinct', field: 'candidate', type: 'quantitative', legend: false },\n }\n }, {\n mark: { type: 'text', dy: 4 },\n encoding: {\n x,\n y: { field: 'owner', type: 'nominal', sort: { op: 'count', order: 'descending' } },\n text: { aggregate: 'distinct', field: 'candidate', type: 'quantitative' },\n color: { value: 'white' },\n }\n }]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":13,"value":"md`## Ratings\n\nCan indicate quality of incoming sourcing. \"Strong hire\"s indicate increased sourcing bar. \"Strong no hire\"s indicate lowering sourcing bar.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":305,"value":"md`### Pass rate\n\nRate at which candidates pass the phone screen. \"3 - Hire\" and \"4 - Strong Hire\" are successes. The dotted line is the percentage of candidates who pass the phone screen.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":290,"value":"vegalite({\n data: { values: interviews.rows },\n width: width - 190,\n height: 100,\n config: {\n axisLeft: { minExtent: 96, maxExtent: 96 },\n },\n layer: [{\n mark: 'bar',\n encoding: {\n x,\n y: { aggregate: 'count', axis: { orient: 'right' } },\n // color: { field: 'pass', type: 'nominal', scale: { domain: [true, null, false], range: 'diverging' } },\n color: { field: 'rating', type: 'ordinal', scale: { domain: ratings, range: 'diverging' } },\n size: { value: (width - 190)/weeks - 2 },\n }\n }, {\n mark: { type: 'text', dy: -4 },\n encoding: {\n x,\n y: { aggregate: 'count', stack: 'zero' },\n text: { aggregate: 'count', type: 'quantitative' },\n detail: { field: 'pass' },\n }\n }]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":724,"value":"vegalite({\n data: { values: interviews.rows },\n width: width - 190,\n height: 100,\n config: {\n axisLeft: { minExtent: 96, maxExtent: 96 },\n },\n layer: [{\n mark: 'bar',\n encoding: {\n x,\n y: { aggregate: 'count', stack: 'normalize', axis: { orient: 'right', title: 'Percentage', format: '%' } },\n color: { field: 'rating', type: 'ordinal', scale: { domain: ratings, range: 'diverging' } },\n size: { value: (width - 190)/weeks - 2 },\n // order: { field: 'rating', type: 'ordinal' },\n }\n }, {\n mark: { type: 'line', interpolate: 'cardinal', tension: 0.5, strokeDash: [ 5, 3 ] },\n encoding: {\n x,\n y: { aggregate: 'count', stack: 'normalize' },\n color: { field: 'pass', type: 'nominal', scale: { domain: [true], range: ['gray'] }, legend: false },\n size: { value: 1 },\n }\n }],\n resolve: { scale: { color: 'independent' } },\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":311,"value":"md`### Score distribution`","pinned":false,"hidden":false,"mode":"ojs"},{"id":213,"value":"vegalite({\n data: { values: interviews.rows.filter(i => i.rating) },\n width: width - 190,\n height: 100,\n config: { padding: { left: 30 } },\n layer: [{\n mark: 'square',\n encoding: {\n x,\n y: { field: 'rating', type: 'ordinal', scale: { domain: ratings }, axis: { orient: 'right', offset: 20 } },\n size: { aggregate: 'count', type: 'quantitative', scale: { range: [ 80, 600 ] }, legend: false },\n color: { field: 'rating', type: 'ordinal', scale: { domain: ratings.filter(r => r), range: 'diverging' }, legend: false },\n }\n }/*, {\n mark: { type: 'text', dy: 4 },\n encoding: {\n x,\n y: { field: 'rating', type: 'ordinal' },\n text: { aggregate: 'count', type: 'quantitative' },\n }\n }*/]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":390,"value":"ratings = ['4 - Strong Hire', '3 - Hire', null, '2 - No Hire', '1 - Strong No Hire']","pinned":false,"hidden":false,"mode":"ojs"},{"id":1329,"value":"md`## Questions\n\nWhat questions are being asked? How often? TODO: What are the results?`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1344,"value":"vegalite({\n data: { values: interviews.rows.filter(i => i.question) },\n width: width - 200,\n config: { padding: { left: 30 } },\n layer: [{\n mark: 'square',\n encoding: {\n x,\n y: { field: 'question', type: 'nominal', sort: { op: 'count', order: 'descending' }, axis: { orient: 'right' } },\n size: { aggregate: 'count', type: 'quantitative', scale: { range: [ 40, 500 ] }, legend: false },\n color: { aggregate: 'count', type: 'quantitative', legend: false, scale: { range: 'heatmap' } },\n }\n }, {\n mark: { type: 'text', dy: 4 },\n encoding: {\n x,\n y: { field: 'question', type: 'nominal', sort: { op: 'count', order: 'descending' } },\n text: { aggregate: 'count', type: 'quantitative' },\n color: { value: 'white' },\n }\n }]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1110,"value":"md`## Feedback\n\nCandidates we should review with recruiters and sourcers to calibrate on people we shouldn't and should be sending to phone screen.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1027,"value":"md`### 1 - Strong No Hire\n\nCandidates we should try to filter out earlier, if reasonably possible.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1021,"value":"candidateReview(interviews.rows.filter(i => i.rating == '1 - Strong No Hire'))","pinned":false,"hidden":false,"mode":"ojs"},{"id":1102,"value":"md`### 4 - Strong Hire\n\nCandidates we should look for more.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1105,"value":"candidateReview(interviews.rows.filter(i => i.rating == '4 - Strong Hire'))","pinned":false,"hidden":false,"mode":"ojs"},{"id":1099,"value":"function candidateReview(rows) {\n let ago = d3.timeWeek.offset(new Date(), -4)\n let ones = rows.filter(i => new Date(i.date) > ago)\n let grouped = d3.nest()\n .key(d => d.week)\n .entries(ones)\n let timeFormat = d3.timeFormat('%b %e, %Y')\n \n return html`\n \n \n ${grouped.map(({ key: date, values: interviews }) => html`\n \n \n ${interviews.map(interview => html`\n \n \n \n \n \n `)}\n \n `)}\n

${timeFormat(new Date(date))}
${interview.candidate}	${interview.interviewer}	${interview.owner}

\n `\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":956,"value":"md`### Common configuration`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1306,"value":"weeks = Math.round(width / 40)","pinned":false,"hidden":false,"mode":"ojs"},{"id":218,"value":"x = ({ field: 'week', type: 'temporal', timeUnit: 'yearmonthdate', axis: { grid: false }, scale: { domain: xDomain } })","pinned":false,"hidden":false,"mode":"ojs"},{"id":908,"value":"xDomain = d3.extent(interviews.rows, i => i.week).map(d => +new Date(d))","pinned":false,"hidden":false,"mode":"ojs"},{"id":954,"value":"md`### Imports`","pinned":false,"hidden":false,"mode":"ojs"},{"id":68,"value":"d3 = require('d3')","pinned":false,"hidden":false,"mode":"ojs"},{"id":142,"value":"_ = require('lodash')","pinned":false,"hidden":false,"mode":"ojs"},{"id":23,"value":"import { lever, vegalite } from '@visnup/lever'","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W31","stdlib":"1","titling":"auto"}