KeonaHealth-Answers.md

Exercise 1

Revised Answer The correct SQL came to me last night. Here's my revised SQL. I left my original answer for completeness and disclosure (though you could see it from the history of the gist).

SELECT
     p.PersonId, p.NameFirst, p.NameLast,
     (select top 1 OrderId from Order o where o.PersonId=p.PersonId Order by OrderDateTime DESC) as LastOrderId,
     (select max(OrderDateTime) from Order o where o.PersonId=p.PersonId) as LastOrderDateTime
FROM Person p
ORDER BY p.PersonId DESC

Old Answer

SELECT
     p.PersonId, p.NameFirst, p.NameLast,
    o2.OrderId as LastOrderId, o.LastOrderDateTime
FROM Person p
LEFT JOIN (
  select PersonId, MAX(OrderDateTime) as LastOrderDateTime
  FROM Order
  GROUP BY PersonId
) O ON p.PersonId = o.PersonId
LEFT JOIN Order o2 on o2.PersonId=p.PersonId
                         AND o.LastOrderDateTime=o2.OrderDateTime
ORDER BY O.PersonId DESC

Original Note (for Old Answer): I'm confident this produces the correct results however I believe there is ways to improve the query. My SQL is a bit rusty, to be honest. I favored providing something I was confident would "work" vs. complex SQL.

Exercise 2

My strategies for high availability upgrades has been to use a deployment tool called Octopus Deploy with a CI/CD process (usually GitLab lately but GitHub, TFS, etc will work fine too). The CI/CD part ensure that the product deploys consistently & successfully. The deployment tool is used to "script" the deployment thus making it repeatable and much faster than a person could deploy software.

In order to deploy to a production environment without downtime, I'd use a pair of load balanced servers. Using Octopus Deploy, I'd separate the deployments of the load balanced servers. I'd drain the first target server and execute the deployment "promotion" to the server. I'd then use the load balancer to move the traffic to the next server and would execute the second deployment to update the second server.

Ideally, the database is addressed via in-app migration. So once the load moved over to the new instance (of the just-deployed code) the database would be auto-upgraded. To implement this, I'd use either Evolve (https://github.com/lecaillon/Evolve) or FluentMigrator (https://github.com/fluentmigrator/fluentmigrator) depending on team preference.

In the event in-app migration isn't used or isn't possible, I'd again use Octopus Deploy to "deploy" the database to the target servers.

Exercise 3

Node based javascript using a library called "Moment". Moment can be used in a browser as well. https://momentjs.com/

var log = console.log;
var moment = require('moment');
const startDate = moment("2017-01-01", "YYYY-MM-DD");
var endDate = moment(); //current date.
var daysElapsed = endDate.diff(startDate, 'days');
log("Days since 1/1/2017", daysElapsed);
log("Today", endDate.format("MM/D/YYYY hh:MM:SS a"));

Example output:

Days since 1/1/2017 583
Today 08/7/2018 05:08:26 pm

Exercise 4

Assumptions

• This is contained within a class
• Person is defined
• Dapper is installed and loaded
• Database connection was previously established and validated.
• Stored procedure Proc_AllOrdersForDate contains needed SQL to do SELECT
• Stored procedure takes one prameter called @OrderDate

public ICollection<Person> AllOrdersForDate(DateTime orderDate)
{
  var queryResults = _dbConnection.Query<Person>("Proc_AllOrdersForDate",
      new {OrderDate = orderDate},
      commandType: CommandType.StoredProcedure
  );
  return queryResults.ToList();
}

Exercise 5

I'd use the following if I was building a website from scratch today. I'm assuming a web application and not just a website.

Development Technologies

• .NET core 2.1 - Backend server, would run on linux
• React (maybe Angular but right now I know React better) - Front End among traditional HTML
• DynamoDB - Data storage
• Bootstrap - Base UI
• Less - Front end element styling.

Development Tools

• Rider (VS.NET Replacement) - Development IDE (I prefer it over VS.NET)
• Octopus Deploy - Deployment tool
• GitLab - source control and CI/CD
• SourceTree - Git front end to augment Rider's Git UI
• xUnit - Unit Testing
• TestCafe - UI & Functional Testing