pcc_010_challenge_overview.Rmd

---
title: "Paylocity Coding Challenge Overview"
author: "Eric Milgram, PhD"
date: "December 15, 2021"
knit: (function(inputFile, encoding) {
        rmarkdown::render(
              input = inputFile,
              encoding = encoding,
              output_dir = here::here("./code/Markdown"))
        }
      )
output:
  md_document:
    toc: no
    number_sections: no
    variant: gfm
---

```{r setup, include=FALSE}
library(here)
library(gluedown)

knitr::opts_chunk$set(echo = FALSE, include = TRUE, results = "hold", message = FALSE)
```

```{css, echo = FALSE}

pre {
  max-height: 800px !important;
  overflow-y: auto !important;
  overflow-x: scroll !important;
}
pre code {
  white-space: pre
}

```

# Paylocity Coding Challenge Overview

<div style="font-size: 1.5em; padding-bottom: 0;">Eric Milgram, PhD</div>
<table>
<tbody>
<tr>
  <td style="padding: 0; display: none;">
    <a href="https://github.com/ScientificProgrammer/PaylocityCodingChallenge">ScientificProgrammer/PaylocityCodingChallenge</a>
  </td>
</tr>
<tr>
  <td style="padding: 0;">
    Created: December 15, 2021
  </td>
</tr>
<tr>
  <td style="padding: 0;">
    Last Updated: `r Sys.time()`</span>
  </td>
</tr>
</tbody>
</table>

## PURPOSE

<div style="font-size: 1.5em; color: red;">
<div>
  TO DO:
</div>
<ul>
<li>GIVE A SUCCINCT EXPLANATION OF THE PAYLOCITY CODING CHALLENGE</li>
<li>POINT OUT THAT THIS DOCUMENT IS JUST AN OVERVIEW OF THE ASSIGNMENT</li>
<li>PUT LINKS HERE TO THE DETAILED EXPLANATIONS FOR PROBLEMS 1 &amp; 2</li>
</ul>
</div>

<blockquote style="background-color: lightgray; border-radius: 1em; box-shadow: 5px 5px 10px black; font-size: 1.5em; margin: 4em auto 4em auto; max-width: 600px; padding: 1.5em; text-align: left;">
  A written summary of the candidate&apos;s work from these coding challenges must be submitted to Paylocity at least 24 hours prior to the candidate&apos;s interview.
</blockquote>

---

## Where can I find the detailed instructions for problems 1 &amp; 2?

1. Problem 1: [Write a Python Program](./pcc_020-010_problem1_write_python_program.md)

1. Problem 2: [Live SQL Walkthrough](./pcc_020-020_problem2_sql_live_walkthrough.md)

---

## Problem 1 of 2: Writing a *Python* program to manage records in a *PostgreSQL* database

Each candidate is required to complete the following exercise and return it a
minimum of 24 hours before their interview. They will be asked to share their
ideas with the team during the interview. They should be prepared to walk the
team through concepts such as the following.

1.  &ldquo;*Here is a summary of the work that I performed.*&rdquo;

2.  &ldquo;*How did I accomplish the task?*&rdquo;

3.  &ldquo;*Why did I choose a particular approach?*&rdquo;

Also be prepared for a few questions from the team such as, “*What would you do
if 'this issue' came up?*”

The team wants to get an idea how the person thinks. Also, the
submitted/developed code should run.

The expectation is that the candidate will complete the `Python` exercise ahead
of time and walk through their answer during the **Technical Interview**. The
candidate will be expected to present their code and show it running on their
machine.

---

## Problem 2 of 2: Multi-part SQL Exercise: Live walk-through

The following figure shows an *entity relationship diagram* (ERD) for a
conceptual data model containing the following four tables, which are are part
of a generic data model for managing a company's HR functions.

1. Company

1. Position

1. Employee

![**FIGURE 1:** An ERD for a conceptual data model.](./../../img/fig_company_db_schema_939x151.png){style="box-shadow: 5px 5px 10px black; border: solid medium black; border-radius: 5px; margin: 2em auto 2em auto; width: 959px; height: 151px;"}

```{r TableSet1, eval = FALSE}
library(kableExtra)
library(purrr)

dbTables <- vector("list", length = 4)

names(dbTables) <- c("Company", "Position", "Employee", "Job")
  
dbTables[["Company"]] <- data.frame(Company = c("guid", "name", "status"))

dbTables[["Position"]] <- data.frame(Position = c("guid", "name", "status"))

dbTables[["Employee"]] <- data.frame(Employee = c("guid", "state", "status"))

dbTables[["Job"]] <- data.frame(Job = c("guid", "company_guid", "position_guid", "employee_guid"))

htmlTables <- dbTables %>%
  map(function(tbl) {
    tbl %>%  kableExtra::kable()  %>%
      row_spec(0, font_size = 20, color = "white", background = "gray") %>%
       column_spec(1, width = "15em")  %>%
      kableExtra::kable_styling(
        bootstrap_options = c("bordered", "striped", "hover", "condensed", "responsive"),
        full_width = FALSE
      )
  })

knitr::raw_html("<div style='float: left; margin: 0 1em 0 1em;'>\n")
knitr::raw_html(htmlTables[["Company"]])
knitr::raw_html("\n</div>\n")

knitr::raw_html("<div style='float: left; margin: 0 1em 0 1em;'>\n")
knitr::raw_html(htmlTables[["Position"]])
knitr::raw_html("\n</div>\n")

knitr::raw_html("<div style='float: left; margin-right: 1em;'>\n")
knitr::raw_html(htmlTables[["Employee"]])
knitr::raw_html("\n</div>\n")

knitr::raw_html("<div style='float: left; margin: 0 1em 0 1em;'>\n")
knitr::raw_html(htmlTables[["Job"]])
knitr::raw_html("\n</div>\n")

```

:::{style="clear: both;"}
The expectation is that the candidate is able to write SQL live while using
whatever tool to which they have access, as well as asking questions of the
team. For our data needs, we will be either retrieving or inserting additional
data into our physical database schema that is based on the data model
described here. All of the following actions will be performed
on the physical database schema.
:::

**ACTIONS**

* INSERT

* UPDATE

* DELETE

A `timestamp` will be inserted or updated with various records in each of the tables in our database.

:::{style="height: 5em;"}
:::