Health Catalyst Analytics Engineer interview questions

SQL Assessment . I did not get final call ,I took interview little casually. I would have given better had they provided me actual SSMS platform or used a code editor to test. They gave good SQL feedback assessment : Candidate added a lot of unnecessary complexity to the exam. On the ERD in the Employee table EmployeeID is listed as the primary key. The candidate should have realized this indicated a unique value in the table that would make it unnecessary to sum vacation hours and select from a subquery in question one. In question two the candidate used a union although the question specified to right one query that returns two rows. In question three there was also considerable over complication which would ultimately cause the query not to run. The candidate summed vacation hours here as well and has several other non aggregated fields in the select but only grouped by EmployeeID. There also appears to be an closing parenthesis aliased as a with no opening parenthesis that looks like it was supposed to be part of a subquery. It seems the candidate has a decent grasp of SQL syntax but maybe less of a grasp on data modeling. Candidate did a good job of capturing the requirements. The only thing that was missed was the filter for a HOME address in question 3 which is a common oversight. Candidate did a great job documenting code with the exception of probably the most important assumption which was the values in the Gender field. Although the assumption was incorrect in question three given the ERD that there could be multiple address records for an Employee, it was good to see the assumption documented to explain the use of the window function to find the last modified date. Formatting was ok in some areas but wasn't particularly consistent. Some SQL keywords had the first letter capitalized while others where completely lowercase, some aliased columns had underscores between words others didn't.

First round was HR followed by technical assessment followed by in person interview. the technical assessment was a SQL test. A physical diagram was provided and was asked to create queries for certain questions. In 1:1, was given a business use case and was asked to form a provide an ERD and a query that involved inner joins on three database tables.