Jobs DB

Problem

Our customers are companies that want to track how candidates progress through the interviewing process. We want you to figure out how to model this data, keeping future scaling needs in mind. We've provided some context and examples around this below:

Context

Companies create a set of interviews for every job that candidates must complete in an ordered fashion. Here's an example:

• Company The Big Apple Circus has the job Clown with the following interviews:
  • Face painting
  • Miming slipping on a banana
  • Fit into a small car
• Company Ringling Brothers has a job Lion Tamer with the following interviews:
  • Why I like kitties
  • Fend off a lion with a chair

Companies sometimes need to change their interview process on a job, so you should account for the possibility of interviews being added/removed during the process. To build on the previous example:

• After interviewing a few candidates each:
  • Big Apple Circus decides to add the "Pie fight" interview for the Clown job
  • Ringling decides to remove the "Why I like kitties" interview for the Lion Tamer job

For each job, you should be able to get the following information out of the database:

• Candidates on the job
• Which interviews have been conducted
• What time interviews were conducted
• Which employees conducted each interview
• Whether conducted interviews were passed or failed
• Which interviews still need to be conducted for each candidate, and the order those interviews should be conducted in

A few other constraints:

• Companies should have unique names.
• Jobs should have unique names per company.
• You should add common sense indexes and foreign key constraints depending on the structure of the database

Exercise

We will evaluate the structure of your database and the performance of the queries as if there were large amounts of data. Please feel free to note your design decisions, assumptions, and other thoughts through comments in the SQL file.

We'll be running your SQL file against a postgres 10.7 server unless you specify a different version. You're welcome to split your answer into multiple SQL files, but let us know what order to run them in with a comment in the files.

DB Setup and Structure

1. Write out the DDL to create the database, all of its tables, constraints and any necessary indexes, given the data and queries described below.

Insert Seed Data

1. Write out the SQL (multiple statements is fine) to create the job Clown for the Big Apple Circus with the interviews "Face painting", Slipping on banana peel, Fit into small car in that order.

2. Write out the SQL to seed your database with the following interviewer data.

a. Candidate "Dumpling" was interviewed by Bozo for Face painting 5 days ago and the candidate passed

b. Candidate "Dumpling" was interviewed by Krusty for Slipping on banana peel 4 days ago and the candidate failed

c. Candidate "Dusty Bim Bam" was interviewed by Krusty for Face painting 5 days ago and the candidate passed

d. Candidate "Dusty Bim Bam" was interviewed by Bozo for Slipping on banana peel 4 days ago and the candidate passed

e. Candidate "Dusty Bim Bam" was interviewed by Trixy for Fit into a small car 4 days ago and the candidate passed

3. Write out the SQL to remove the "Slipping on banana peel" interview and in its place add the Make balloon animals interview

4. Write out the SQL to add more candidate data after the change:

a. Candidate "Twinkies" interviewed with Trixy for Face painting two days ago and the candidate passed.

b. Candidate "Twinkies" interviewed withBozo for Make balloon animals one day ago and the candidate passed

c. Candidate "Twinkies" interview with Krusty for Fit into small car one day ago and the candidate passed

Queries

1. Write the Query to return all the candidates, and all completed interviews (passed or failed). Assume this query will be run a large number of times.

2. Write the Query to return all interviews conducted by "Krusty". Assume this query will be run by an application frequently with different interviewers.

3. Write the Query to return the results of all candidates for the Clown job, whether they took the "Slipping on banana peel" interview and the result of that interview. Assume queries like this for different interviews will be run often by an application.