11 American Axle & Manufacturing SQL Interview Questions (Updated 2025)

American Axle & Manufacturing employees write SQL queries daily for analyzing manufacturing process data for efficiency improvements, and managing supply chain information. So, it shouldn't surprise you that American Axle & Manufacturing frequently asks SQL problems in interviews for Data Science and Data Engineering positions.

So, to help you study, here’s 11 American Axle & Manufacturing SQL interview questions – able to answer them all?

11 American Axle & Manufacturing SQL Interview Questions

SQL Question 1: Identify Top Purchasing Customers

As a data analyst for American Axle & Manufacturing, you are tasked to identify the company's 'power users'. In this context, power users are those who have purchased the most products over the last 12 months. The goal is to calculate, for each user, their total amount spent, the number of unique products bought, and the maximum single transaction amount in the last 12 months.

Here are the input tables:

purchase_logs Example Input:

products Example Input:

For the SQL question, write a PostgreSQL query to solve this problem.

Answer:

SELECT 
    pl.user_id,
    SUM(pl.amount) AS total_spent,
    COUNT(DISTINCT pl.product_id) AS number_of_unique_products,
    MAX(pl.amount) AS max_single_transaction
FROM 
    purchase_logs pl
WHERE 
    pl.purchase_date >= (CURRENT_DATE - INTERVAL '1 year')
GROUP BY 
    pl.user_id
ORDER BY 
    total_spent DESC;

This SQL query aggregates the purchase_logs data for the last 12 months ('1 year' before the current date) at the user level. It calculates the total amount spent (total_spent), the number of unique products bought (number_of_unique_products), and the maximum amount spent in a single transaction (max_single_transaction) for each user. The results are ordered in descending order by the total amount spent to help identify the 'power users' who spent the most.

To solve a similar power-user data analysis problem question on DataLemur's free interactive SQL code editor, try this Microsoft Teams Power User SQL Interview Question:

SQL Question 2: Employees Earning More Than Their Boss

Given a table of American Axle & Manufacturing employee salary data, write a SQL query to find employees who earn more money than their direct manager.

American Axle & Manufacturing employees Example Input:

Example Output:

This is the output because Olivia Smith earns $8,000, surpassing her manager, William Davis who earns 7,800.

You can solve this problem and run your code right in DataLemur's online SQL environment:

Answer:

First, we perform a SELF-JOIN where we treat the first employee table (mgr) as the managers' table and the second employee table (emp) as the employees' table. Then we use a WHERE clause to filter the results, ensuring we only get employees whose salaries are higher than their manager's salary.

SELECT
  emp.employee_id AS employee_id,
  emp.name AS employee_name
FROM employee AS mgr
INNER JOIN employee AS emp
  ON mgr.employee_id = emp.manager_id
WHERE emp.salary > mgr.salary;

If the code above is confusing, you can find a step-by-step solution here: Employees Earning More Than Managers.

Check out the American Axle career page!

SQL Question 3: What are the main differences between foreign and primary keys in a database?

To clarify the distinction between a primary key and a foreign key, let's examine employee data from American Axle & Manufacturing's HR database:

american_axle_manufacturing_employees:

+------------+------------+------------+------------+ | employee_id| first_name | last_name | manager_id | +------------+------------+------------+------------+ | 1 | Aubrey | Graham | 3 | | 2 | Marshal | Mathers | 3 | | 3 | Dwayne | Carter | 4 | | 4 | Shawn | Carter | | +------------+------------+------------+------------+

In this table, employee_id serves as the primary key. It uniquely identifies each employee and cannot be null.

manager_id functions as a foreign key, linking to the employee_id of the employee's manager. This establishes a relationship between American Axle & Manufacturing employees and their managers, allowing for easy querying to find an employee's manager or see which employees report to a specific manager.

The american_axle_manufacturing_employees table may also have multiple foreign keys that reference primary keys in other tables. For example, department_id and location_id foreign keys could be used to connect each employee to their respective department and location.

SQL Question 4: Analysis of Production Defects

In the role of a Data Analyst at American Axle & Manufacturing, a company that designs, engineers and manufactures driveline and metal forming technologies, part of your role is to analyze the production line and identify defects trends for early warning and remediation.

Working with the Production database, you are asked to write a SQL query to determine the average defect rate for each product by month over the whole dataset. To accomplish this task, you will use SQL Window Functions in your query. The defect rate is the sum of defects for the product in a given month divided by sum of units produced for the same product and month.

The production table has a row for each unit produced, and includes a boolean column is_defective that is true if the unit was defective, and false otherwise.

production Example Input:

Expected Output:

Answer:

Here is the SQL query to get the result.

SELECT 
    EXTRACT(MONTH FROM production_date) as month,
    product_id as product,
    CAST(SUM(CASE WHEN is_defective = true THEN 1 ELSE 0 END) AS DOUBLE PRECISION) / COUNT(*) as avg_defect_rate
FROM 
    production
GROUP BY 
    month, product
ORDER BY 
    month, product;

In the SQL query above, the EXTRACT(MONTH FROM production_date) function is used to group data by month. Then we calculated, for each product and each month, the number of defects (CASE WHEN is_defective = true THEN 1 ELSE 0 END) and divided it by the total number of units produced (COUNT(*)). The CAST(... AS DOUBLE PRECISION) function was used to ensure that the division result is a decimal number. This gives us the average defect rate for each product by month.

To practice another window function question on DataLemur's free interactive SQL code editor, try this Google SQL Interview Question:

SQL Question 5: What distinguishes a left join from a right join?

In SQL, both a left and right join is used to combine rows from different tables based on a shared key or set of keys, resulting in a single merged table of data. However, here's the difference:

LEFT (OUTER) JOIN: Retrieves all the records/rows from the left and the matched records/rows from the right table.

SELECT *
FROM Table_A A
LEFT JOIN Table_B B
ON A.col = B.col;

RIGHT (OUTER) JOIN: Retrieves all the records/rows from the right and the matched records/rows from the left table.

SELECT *
FROM Table_A A
RIGHT JOIN Table_B B
ON A.col = B.col;

SQL Question 6: Vehicle Production Management

American Axle & Manufacturing needs to manage its vehicle production. The company wants to be able to track the number of different type of vehicles produced each month and identify which plant produces the most of each vehicle type. Suppose you have two tables, Production and Plant.

Table Production records each vehicle produced, with fields including Vehicle_ID (a unique identifier for the vehicle), Type (the type of vehicle), Prod_Date (the date the vehicle was produced), and Plant_ID (the identifier of the plant where the vehicle was produced).

Table Plant includes details about each plant, with fields including Plant_ID (the identifier of the plant), Name (the plant's name), and Location (where the plant is located).

Production Example Input:

Plant Example Input:

The task: Write a SQL query to display a monthly break down of each type of vehicles produced and the plant that produced the most of each type.

Answer:

WITH monthly_production AS (
SELECT 
    DATE_TRUNC('month', Prod_Date) AS Month,
    Type,
    Plant_ID,
    COUNT(*) AS Production_Count
FROM 
    Production 
GROUP BY 
    Month,
    Type,
    Plant_ID
),

max_production AS (
SELECT 
    Month,
    Type,
    MAX(Production_Count) AS Max_Production
FROM 
    monthly_production
GROUP BY 
    Month,
    Type
)

SELECT 
    mp.Month,
    mp.Type,
    p.Name as Plant_Name,
    mp.Production_Count
FROM 
    max_production mp
JOIN 
    monthly_production mp2 ON mp2.Month = mp.Month 
                           AND mp2.Type = mp.Type 
                           AND mp2.Production_Count = mp.Max_Production
JOIN 
    Plant p ON p.Plant_ID = mp2.Plant_ID;

This query first creates a CTE (monthly_production) to count the number of each type of vehicles produced at each plant, then creates another CTE (max_production) to find the maximum number of each type of vehicles produced each month. Finally, it joins monthly_production and Plant tables to get the plant that produced the most of each type for each month.

SQL Question 7: What are the different kinds of joins in SQL?

A join in SQL allows you to retrieve data from multiple tables and combine it into a single set of results.

In SQL, there are four different types of JOINs. To demonstrate each kind, imagine you had two database tables, an advertising campaigns table which had information on Google Ads keywords and how much was bid for each keyword, and a sales table, which has data on how many products were sold and which Google Ads keyword drove that sale.

INNER JOIN: An INNER JOIN retrieves rows from both tables where there is a match in the shared key or keys. For example, an INNER JOIN between the Advertising_Campaigns table and the Sales table could be performed using the keyword column as the shared key. This would retrieve only the rows where the keyword in the Advertising_Campaigns table matches the keyword in the Sales table.

LEFT JOIN: A LEFT JOIN retrieves all rows from the left table (in this case, the Advertising_Campaigns table) and any matching rows from the right table (the Sales table). If there is no match in the right table, NULL values will be returned for the right table's columns.

RIGHT JOIN: A RIGHT JOIN retrieves all rows from the right table (in this case, the Sales table) and any matching rows from the left table (the Advertising_Campaigns table). If there is no match in the left table, NULL values will be returned for the left table's columns.

FULL OUTER JOIN: A FULL OUTER JOIN retrieves all rows from both tables, regardless of whether there is a match in the shared key or keys. If there is no match, NULL values will be returned for the columns of the non-matching table.

SQL Question 8: Filter Customers Based on Region and Product Sales

You work as a data analyst at American Axle & Manufacturing, a leading provider of driveline and drivetrain systems and components for automotive manufacturers. Your team maintains a customer database that includes detailed information about each customer’s location, as well as their recent purchases.

Your task is to identify customers from the 'Southern' region who have purchased at least one Transmission System in the year 2021.

The data is stored in two tables, customers and purchases.

customers Example Input:

purchases Example Input:

Write a SQL query to filter the data according to the conditions.

Answer:

SELECT c.customer_id, c.name
FROM customers c
JOIN purchases p ON c.customer_id = p.customer_id
WHERE c.region = 'Southern' AND p.product = 'Transmission System' AND
      DATE_PART('year', p.purchase_date) = 2021;

This PostgreSQL query first joins the customers and purchases tables on the customer_id field. It then filters the joined table to include only the rows where the customer's region is 'Southern', the purchased product is 'Transmission System', and the year of the purchase is 2021. The DATE_PART function is used to extract the year from the purchase_date column. The resulting table includes the IDs and names of the customers who meet all the criteria.

SQL Question 9: Find Customers from Specific Region

American Axle & Manufacturing wants to target customers from the Midwestern United States for a direct marketing campaign. As an SQL expert, you are tasked with retrieving customer information who live in cities that are known to begin with 'Cha'. Write an SQL query to filter the relevant customer information from the Customers table.

Customers Example Input:

Example Output:

Answer:

SELECT * 
FROM Customers
WHERE city LIKE 'Cha%';

This query uses the SQL LIKE clause to find all records in the Customers table where the city name begins with 'Cha'. It uses the % wildcard to match any sequence of characters after the string 'Cha', effectively filtering for cities that start with this pattern. The result is a table of customers living in such cities, useful for targeting direct marketing efforts.

SQL Question 10: What are the similarities and differences between a clustered index and non-clustered index?

A clustered index is an index where the order of the rows in the database corresponds to the order of the rows in the index. Because of this, a table can only have one clustered index, but it can have multiple non-clustered indexes.

The main difference between the two is that the database tries to keep the data in the same order as the corresponding keys in the clustered index. This can improve the performance of most query operations, as it provides a linear-access path to the data stored in the database.

SQL Question 11: Analyze Customer Purchase History and Match with Product Info

You are provided with two tables. The first table, Customers, includes the list of all customers at American Axle & Manufacturing, along with the product_id of the items they've bought. The second table, Products, gives information about all the available products, including their name and category.

Your task is to write a SQL query to merge these two tables and find how many pieces are bought of each category by each customer. Sort your result by customer_id and category.

Customers Example Input:

Products Example Input:

Answer:

SELECT c.customer_id, p.category, SUM(c.quantity) as total_quantity
FROM Customers c
JOIN Products p ON c.product_id = p.product_id
GROUP BY c.customer_id, p.category
ORDER BY c.customer_id, p.category;

This SQL query joins the 'Customers' and 'Products' tables based on the matching 'product_id'. It then groups the data by 'customer_id' and 'category', summing up the quantity for each group, providing the total quantity purchased for each category by each customer. Sorting by 'customer_id' and 'category' provides an organized view of the results.

Since join questions come up frequently during SQL interviews, try this Snapchat SQL Interview question using JOINS:

Preparing For The American Axle & Manufacturing 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. Beyond just solving the above American Axle & Manufacturing SQL interview questions, you should also solve the 200+ SQL coding questions which come from companies like FAANG (FB, Apple, Amazon, Netflix, Google).

Each SQL question has hints to guide you, full answers and crucially, there is an interactive coding environment so you can right in the browser run your query and have it graded.

To prep for the American Axle & Manufacturing SQL interview you can also be wise to practice SQL problems from other automotive companies like:

• Autoliv SQL questions

• Dana Incorporated SQL questions

• Thor Industries SQL questions

• BorgWarner SQL questions

But if your SQL query skills are weak, don't worry about jumping right into solving questions – strengthen your SQL foundations with this free SQL for Data Analytics course.

This tutorial covers SQL concepts such as LEAD window function and math functions in SQL – both of which show up often in American Axle & Manufacturing SQL assessments.

American Axle & Manufacturing Data Science Interview Tips

What Do American Axle & Manufacturing Data Science Interviews Cover?

In addition to SQL query questions, the other types of problems to prepare for the American Axle & Manufacturing Data Science Interview are:

• Statistics & AB Testing Questions
• Coding Questions in Python or R
• Open-Ended Data Case Studies
• ML Modelling Questions
• Resume-Based Behavioral Questions

How To Prepare for American Axle & Manufacturing Data Science Interviews?

The best way to prepare for American Axle & Manufacturing Data Science interviews is by reading Ace the Data Science Interview. The book's got:

• 201 Interview Questions from Facebook, Google & startups
• A Crash Course on SQL, Product-Sense & ML
• Great Reviews (1000+ reviews, 4.5-star rating)