9 L3Harris SQL Interview Questions (Updated 2024)
At L3Harris Technologies, SQL is used quite frequently for analyzing vast defense-related datasets for actionable insights, and managing and querying aerospace and communication databases for optimization tasks. That's why L3Harris frequently asks SQL query questions in interviews for Data Analyst, Data Science, and BI jobs.
To practice here’s 9 L3Harris Technologies SQL interview questions, which are similar to recently asked questions at L3Harris – how many of them can you solve?
9 L3Harris SQL Interview Questions
SQL Question 1: Monthly Average Review Rating for Products
L3Harris often ask technicians who mantain and fix their defense and weapons systems to write reviews of different parts and products L3Harris makes.
Write a SQL query to calculate the monthly average review rating for each product. The output should have the month(from the submit date), product id and the average stars that product received in that given month. Consider the given sample input and expected output for reference.
Example Input:
| review_id | user_id | submit_date | product_id | stars |
|---|---|---|---|---|
| 6171 | 123 | 06/08/2022 00:00:00 | 50001 | 4 |
| 7802 | 265 | 06/10/2022 00:00:00 | 69852 | 4 |
| 5293 | 362 | 06/18/2022 00:00:00 | 50001 | 3 |
| 6352 | 192 | 07/26/2022 00:00:00 | 69852 | 3 |
| 4517 | 981 | 07/05/2022 00:00:00 | 69852 | 2 |
Expected Output:
| mth | product | avg_stars |
|---|---|---|
| 6 | 50001 | 3.50 |
| 6 | 69852 | 4.00 |
| 7 | 69852 | 2.50 |
Answer:
This query is first extracting the month from the submit_date, grouping the rows by this extracted month and product_id. Then it is calculating the average stars for this grouping. AVG function is used to calculate the mean of the stars column, resulting in the average star rating a product received in a given month.
SQL Question 2: Inventory Management for L3Harris
L3Harris works with very complicated supply-chains, and uses SQL to analyze the inventory of their different equipment parts. The task is to design a database system that tracks the inventory of different parts: how many are in storage, how many are in use, and how many are in need of maintenance.
Further, write a SQL query that will show all parts that are low in stock in storage (less than 10 items) and need replenishing, also list out the parts that are in need of maintenance.
Below are sample tables:
Example Input:
| item_id | part_name | in_storage | in_use | maintenance_needed |
|---|---|---|---|---|
| 1001 | Engine Type X | 15 | 35 | 8 |
| 1002 | Propeller Model Y | 5 | 27 | 4 |
| 1003 | Radar Type Z | 9 | 34 | 10 |
| 1004 | Aileron Model A | 12 | 22 | 6 |
| 1005 | Altitude Meter Model B | 8 | 18 | 3 |
Example Output:
| item_id | part_name |
|---|---|
| 1002 | Propeller Model Y |
| 1003 | Radar Type Z |
| 1005 | Altitude Meter Model B |
SQL Query:
The SQL query above selects and lists all parts that are either low in stock in storage (i.e., less than 10 items), or are in need of maintenance.
SQL QUESTION 3: What's the SQL command do, and when would you use it?
combines the results from two or more SELECT statements, but only returns the rows that are exactly the same between the two sets.
For example, say you were doing an HR Analytics project for L3Harris, and had access to L3Harris's contractors and employees data. Assume that some employees were previously contractors, and vice versa, and thus would show up in both tables. You could use operator to find all employees who also show up in the contractors table:
SQL Question 4: Filter Customer Records for Specific Criteria
L3Harris Technologies produces tactical radios, avionics, and electronic systems. They often need to filter their customer records based on various criteria.
In particular, the company wants to filter records to find out the customers from the USA parts who purchased avionic products in the last six months, and who have a customer status of 'active'.
Consider the following tables and as source data:
Example Input:
| customer_id | first_name | last_name | country | status |
|---|---|---|---|---|
| 1 | John | Doe | USA | Active |
| 2 | Jane | Smith | Canada | Inactive |
| 3 | Albert | Johnson | USA | Active |
| 4 | Maria | Garcia | USA | Inactive |
| 5 | Peter | Williams | UK | Active |
Example Input:
| purchase_id | customer_id | product_category | purchase_date |
|---|---|---|---|
| 1001 | 1 | Avionics | 2022-04-01 |
| 1002 | 2 | Avionics | 2022-05-30 |
| 1003 | 3 | Tactical Radios | 2022-03-15 |
| 1004 | 4 | Avionics | 2022-06-20 |
| 1005 | 5 | Electronic Systems | 2022-04-10 |
Write an SQL query that filters the customer records based on the above criteria.
Answer:
This SQL query joins the and tables on , and filters the records based on the specified conditions. It uses the clause to filter on country being 'USA', status being 'Active', product category being 'Avionics', and the purchase date being in the last six months.
The keyword in SQL is used to select values within a range. The function and function are used to calculate the range for the last six months.
SQL QUESTION 5: When it comes to database normalization, what's the difference between 1NF, 2NF, and 3NF?
The three levels of database normalization (also known as normal forms) are:
First Normal Form (1NF):
- Each column should contain the same type of data (no mixing strings vs. integers)
- Each column in a table contains a single value (no lists or containers of data)
- Each row in the table is unique
Second Normal Form (2NF)
- It's in 1st Normal Form
- All non-key attributes are dependent on the primary key
Said another way, to achieve 2NF, besides following the 1NF criteria, the columns should also be dependent only on that table's primary key.
Third normal form (3NF) if it meets the following criteria:
- It's in 2nd Normal Form
- There are no transitive dependencies in the table.
A transitive dependency means values in one column are derived from data in another column. For example, it wouldn't make sense to keep a column called ""vehicle's age"" and ""vehicle's manufacture date" (because the vehicle's age can be derived from the date it was manufactured).
Although there is a 4NF and 5NF, you probably don't need to know it for the L3Harris interview.
SQL Interview Question 6: Find the Average Working Hours of Employees
Consider a scenario where you are working as a data analyst at L3Harris in the HR department, and are tasked with understanding more about the working hours of L3Harris employees. They might ask you to find the average working hours per day for each employee for the past month.
Here is some sample data:
Example Input:
| emp_id | name | date | hours_worked |
|---|---|---|---|
| 1 | Smith | 2022-08-01 | 8 |
| 1 | Smith | 2022-08-02 | 6 |
| 1 | Smith | 2022-08-03 | 9 |
| 2 | Jones | 2022-08-01 | 7 |
| 2 | Jones | 2022-08-02 | 8 |
| 2 | Jones | 2022-08-03 | 7 |
| 3 | Brown | 2022-08-01 | 8 |
| 3 | Brown | 2022-08-02 | 8 |
| 3 | Brown | 2022-08-03 | 8 |
Example Output:
| emp_id | name | avg_hours_worked |
|---|---|---|
| 1 | Smith | 7.67 |
| 2 | Jones | 7.33 |
| 3 | Brown | 8.00 |
Answer:
We could use the AVG function in SQL to get this data quite easily.
This query groups the data by employee id and name, and then calculates the average hours worked for these groups. In addition, the WHERE clause ensures that we are only considering work hours from the last month (August 2022 in this case).
To practice a related interactive Amazon Server Utilization Time Question which is similar because it involves calculating total time spent.
SQL QUESTION 7: What's the difference between a unique and non-unique index?
Unique indexes help ensure that there are no duplicate key values in a table, maintaining data integrity. They enforce uniqueness whenever keys are added or changed within the index.
To define a unique index in PostgreSQL, you can use the following syntax:
To define a non-unique index in PostgreSQL, you can use the following syntax:
Non-unique indexes on the other hand, are used to improve query performance by maintaining a sorted order of frequently used data values, but they do not enforce constraints on the associated table.
SQL Question 8: Calculate the Average Cost of Products per Department
Assume that L3Harris wants to monitor its factory production costs. You are provided with a database that includes two tables. One table () lists all the products manufactured by the company, with their respective costs. The other table () lists all the departments where these products are produced. You are asked to write a SQL query to calculate the average cost of products per department.
Example Input:
| product_id | department_id | cost |
|---|---|---|
| 800 | 8 | $120,000 |
| 701 | 3 | $50,000 |
| 240 | 2 | $90,000 |
| 650 | 3 | $130,000 |
| 721 | 2 | $80,000 |
| 450 | 8 | $110,000 |
Example Input:
| department_id | department_name |
|---|---|
| 2 | Avionics |
| 3 | Communication Systems |
| 8 | Defense Systems |
Example Output:
| department | avg_cost |
|---|---|
| Avionics | $85,000 |
| Communication Systems | $90,000 |
| Defense Systems | $115,000 |
Answer:
Using the above SQL command, we first join the and tables on the department_id field. We then apply the GROUP BY command on the department_name field and calculate the average cost ( field) for each group. This answers the question of what the average cost of products is per department.
SQL QUESTION 9: What does do?
The COALESCE() function returns the first non-NULL value from a list of values. This function is often used to replace a NULL with some default value, so that you can then take a or of some column without NULLs messing things up.
For example, suppose you ran a customer satisfaction survey for L3Harris and had statements like "I'd buy from L3Harris again". In the survey, customers would then answer how strongly they agreed with a statement on a scale of 1 to 5 (strongly disagree, disagree, neutral, agreee, strongly agree).
Because attention spans are short, many customers skipped many of the questions, and thus our survey data might be filled with NULLs:
| customer_id | question_id | agree_scale |
|---|---|---|
| 101 | 1 | 4 |
| 101 | 2 | 5 |
| 202 | 1 | 4 |
| 202 | 2 | NULL |
| 303 | 1 | 5 |
| 303 | 2 | NULL |
Before doing further analytics on this customer survey data, you could replace the NULLs in the column with the value of (because that corresponds to the default 'neutral' answer) using the function:
This would result in the following:
| customer_id | question_id | agree_scale |
|---|---|---|
| 101 | 1 | 4 |
| 101 | 2 | 5 |
| 202 | 1 | 4 |
| 202 | 2 | 3 |
| 303 | 1 | 5 |
| 303 | 2 | 3 |
How To Prepare for the L3Harris SQL Interview
The best way to prepare for a SQL interview, besides making sure you have strong SQL fundamentals, is to practice a ton of real SQL questions that were asked in recent job interviews.
In addition to solving the earlier L3Harris SQL interview questions, you should also solve the 200+ SQL questions from real Data Science & Analytics interviews which come from companies like Google, Microsoft and Silicon Valley startups.
Each SQL question has multiple hints, full answers and best of all, there is an interactive coding environment so you can right in the browser run your SQL query and have it checked.
To prep for the L3Harris SQL interview you can also be wise to solve SQL questions from other defense & aerospace contractors like:
However, if your SQL coding skills are weak, forget about jumping right into solving questions – strengthen your SQL foundations with this free SQL tutorial.
This tutorial covers things like joins and handling timestamps – both of these pop up frequently in SQL interviews at L3Harris.
L3Harris Technologies Data Science Interview Tips
What Do L3Harris Data Science Interviews Cover?
For the L3Harris Data Science Interview, besides SQL questions, the other types of questions to prepare for are:
- Probability & Stats Questions
- Coding Questions in Python or R
- Data Case Study Questions
- ML Modelling Questions
- Behavioral Interview Questions
How To Prepare for L3Harris Data Science Interviews?
To prepare for L3Harris Data Science interviews read the book Ace the Data Science Interview because it's got:
- 201 interview questions taken from tech companies like Google & Microsoft
- a crash course covering SQL, Product-Sense & ML
- over 900+ reviews on Amazon & 4.5-star rating