10 Leggett & Platt SQL Interview Questions (Updated 2024)
At Leggett & Platt, SQL is essential for analyzing manufacturing data, including production metrics and quality control, for efficiency improvements, as well as for maintaining inventory databases, such as tracking component inventory levels and supplier performance, for accurate component tracking. For this reason, Leggett & Platt asks SQL problems in interviews for Data Analytics, Data Science, and Data Engineering jobs.
So, to help you study, we've curated 10 Leggett & Platt SQL interview questions – can you solve them?
10 Leggett & Platt SQL Interview Questions
SQL Question 1: Identify the Biggest Spenders
Leggett & Platt is a diversified manufacturer that designs and produces various engineered components and products. One of their key customer metrics is based on the amount of money a customer spends over time. Management wants to identify top spending customers in order to monitor customer health, provide special treatment, and drive customer loyalty.
You are given a database with two tables. The first table "users", contains details of customers and the second table "orders", stores order details.
Create a SQL query to find the top 10 users who have spent the most amount of money with the firm.
The table schema is:
Example Input:
| user_id | first_name | last_name | signup_date | |
|---|---|---|---|---|
| 1 | John | Doe | jdoe@example.com | 2010-12-01 |
| 2 | Jane | Smith | jsmith@example.com | 2012-05-20 |
| 3 | Matt | Jones | mjones@example.com | 2011-07-24 |
| 4 | Nancy | Johnson | njohnson@example.com | 2019-06-30 |
| 5 | Luke | Green | lgreen@example.com | 2020-04-10 |
The table schema is:
Example Input:
| order_id | user_id | order_date | product_name | quantity | price_each |
|---|---|---|---|---|---|
| 1001 | 1 | 2022-05-20 | Metal Bed Frame | 2 | 120.00 |
| 1002 | 2 | 2022-05-21 | Adjustable Foundation | 1 | 700.00 |
| 1003 | 3 | 2022-05-22 | Hybrid Mattress | 2 | 1500.00 |
| 1004 | 4 | 2022-05-23 | Comforter Set | 3 | 75.00 |
| 1005 | 5 | 2022-05-23 | Pillows | 4 | 40.00 |
Answer:
The query starts by joining the users table with the orders table on the user_id column, it then groups the data by the user and sums the total money spent by each user. The total amount spent by each user is calculated by multiplying the price of each product by the quantity of products bought, the results are then ordered in a descending order and only the top 10 results are returned.
To practice another SQL customer analytics question where you can code right in the browser and have your SQL code instantly graded, try this Walmart SQL Interview Question:
SQL Question 2: Top 3 Department Salaries
Imagine you had a table of Leggett & Platt employee salary data. Write a SQL query to find the top 3 highest earning employees within each department.
Leggett & Platt Example Input:
| employee_id | name | salary | department_id |
|---|---|---|---|
| 1 | Emma Thompson | 3800 | 1 |
| 2 | Daniel Rodriguez | 2230 | 1 |
| 3 | Olivia Smith | 2000 | 1 |
| 4 | Noah Johnson | 6800 | 2 |
| 5 | Sophia Martinez | 1750 | 1 |
| 8 | William Davis | 6800 | 2 |
| 10 | James Anderson | 4000 | 1 |
Example Input:
| department_id | department_name |
|---|---|
| 1 | Data Analytics |
| 2 | Data Science |
Example Output:
| department_name | name | salary |
|---|---|---|
| Data Analytics | James Anderson | 4000 |
| Data Analytics | Emma Thompson | 3800 |
| Data Analytics | Daniel Rodriguez | 2230 |
| Data Science | Noah Johnson | 6800 |
| Data Science | William Davis | 6800 |
Code your solution to this problem and run your code right in DataLemur's online SQL environment:
Answer:
We use the DENSE_RANK() window function to generate unique ranks for each employee's salary within their department, with higher salaries receiving lower ranks. Then, we wrap this up in a CTE and filter the employees with a ranking of 3 or lower.
If the solution above is hard to understand, you can find a step-by-step solution with hints here: Top 3 Department Salaries.
SQL Question 3: How can you select unique records from a table?
The keyword added to a statement can be used to get records without duplicates.
For example, say you had a table of Leggett & Platt customers:
| name | city |
|---|---|
| Akash | SF |
| Brittany | NYC |
| Carlos | NYC |
| Diego | Seattle |
| Eva | SF |
| Faye | Seattle |
Suppose you wanted to figure out which cities the customers lived in, but didn't want duplicate results, you could write a query like this:
Your result would be:
| city |
|---|
| SF |
| NYC |
| Seattle |
SQL Question 4: Calculate Sales Growth per Region
As a data analyst at Leggett & Platt, one of your tasks is to analyze the sales growth rate for each region on a monthly basis. Write a SQL query to calculate the month-over-month sales growth rate for each region.
(Note: The sales growth rate is calculated as (Current Month Sales - Previous Month Sales) / Previous Month Sales)
Suppose you have the following two tables:
Example Input:
| sale_id | region_id | sale_date | sale_amount |
|---|---|---|---|
| 5123 | 1 | 01/10/2022 | 50000 |
| 6342 | 1 | 02/12/2022 | 55000 |
| 7642 | 2 | 02/15/2022 | 25000 |
| 8812 | 2 | 03/08/2022 | 26000 |
| 9352 | 1 | 03/25/2022 | 60000 |
Example Input:
| region_id | region_name |
|---|---|
| 1 | North America |
| 2 | Europe |
Example Output:
| mth | region_name | growth_rate |
|---|---|---|
| 2 | North America | 10% |
| 3 | North America | 9.09% |
| 3 | Europe | 4.00% |
Answer:
To answer this question, we use a combination of Window functions and aggregate functions. We first use the SUM() function with the OVER() clause to get the total sales for each month for each region. We then use the LAG() function to get the total sales for the previous month. After that, we calculate the growth rate by subtracting the previous month's sales from the current month's sales, divided by the previous month's sales. We multiply the result by 100 and round it to two decimal places to get the growth rate as a percentage.
To practice a related window function SQL problem on DataLemur's free interactive SQL code editor, solve this Google SQL Interview Question:
SQL Question 5: Could you describe the function of UNION in SQL
The operator merges the output of two or more statements into a single result set. The two SELECT statements within the UNION must have the same number of columns and the data types of the columns are all compatible.
For example, if you were a Data Analyst on the marketing analytics team at Leggett & Platt, this statement would return a combined result set of both Leggett & Platt's Google and Facebook ads that have more than 300 impressions:
SQL Question 6: Select Customers Based on Purchase Behavior
As a data analyst in Leggett & Platt, your task is to filter the customer records based on their purchase behavior and demographic information. You must filter the data such that we only get details of customers who are older than 30, have purchased more than 5 "Bedding" products and whose total purchases exceed $500.
To start, consider these tables:
Example Input:
| customer_id | first_name | last_name | age |
|---|---|---|---|
| 101 | John | Doe | 34 |
| 102 | Jane | Smith | 28 |
| 103 | James | Brown | 32 |
| 104 | Julia | Roberts | 35 |
| 105 | Jack | Daniels | 21 |
Example Input:
| purchase_id | customer_id | product_category | purchase_date | price |
|---|---|---|---|---|
| 2001 | 101 | Bedding | 07/12/2022 | $200 |
| 2002 | 101 | Furniture | 07/14/2022 | $350 |
| 2003 | 102 | Bedding | 07/15/2022 | $150 |
| 2004 | 103 | Bedding | 07/16/2022 | $100 |
| 2005 | 103 | Bedding | 07/17/2022 | $150 |
| 2006 | 103 | Bedding | 07/18/2022 | $200 |
| 2007 | 104 | Bedding | 07/19/2022 | $400 |
| 2008 | 105 | Bedding | 07/20/2022 | $100 |
You are required to return the first name, last name and total purchases of the filtered customers.
Answer:
This query first joins and tables based on the common field. Then it filters the records with the clause, selecting only those customers who are above 30 years of age and have bought Bedding products. It then groups the records by customer, using the clause. Finally, the clause adds additional filter conditions post grouping – ensuring we're only considering customers with more than 5 purchases and total purchases greater than $500. The result of this query would be the first name, last name and total purchases of the customers matching these conditions.
SQL Question 7: Can you define what a database index is, and give some examples of different types of indexes?
A database index is a data structure that provides a quick lookup of data in a column or columns of a table.
There are several types of indexes that can be used in a database:
- Primary index: a unique identifier is used to access the row directly.
- Unique index: used to enforce the uniqueness of the indexed columns in a table.
- Composite index: created on multiple columns of a table, is used to speed up the search process for multiple columns
- Clustered index: determines the physical order of the data in a table
SQL Question 8: Average Cost Per Item Produced
Leggett & Platt, a diversified manufacturer that designs and produces various engineered components and products, wants to achieve operational efficiencies. One way of doing so is by tracking the average cost of production per item across different product categories. Your task is to determine the average cost per item for each product category for the current year.
Example Input:
| production_id | product_name | product_category | production_date | production_cost | items_produced |
|---|---|---|---|---|---|
| 1001 | 'Copper Coil' | 'Electrical Components' | '02/05/2022' | 10000 | 5000 |
| 1002 | 'Steel Spring' | 'Mechanical Components' | '02/20/2022' | 15000 | 4000 |
| 1003 | 'Copper Coil' | 'Electrical Components' | '03/15/2022' | 12000 | 6000 |
| 1004 | 'Steel Spring' | 'Mechanical Components' | '04/20/2022' | 13000 | 3800 |
Example Output:
| product_category | avg_cost_per_item |
|---|---|
| 'Electrical Components' | 0.0183 |
| 'Mechanical Components' | 0.0219 |
Answer:
In this query, we calculate the average cost per item produced in each product category by first computing the cost per item for each production record (), then averaging these costs within each category. We only consider production records from the current year by filtering on the in the clause. Finally, we group by to get one result per category. The cast ensures that the division operation results in a decimal rather than an integer.
To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for evaluating and comparing product categories or this Alibaba Compressed Mean Question which is similar for performing aggregate analysis on items.
SQL Question 9: Calculating Monthly Revenue from Leggett & Platt's Product Sales
You have been given two tables, one - that has information related to customers, and a second one, - that has information related to product orders. Your task is to write a SQL query that provides the monthly revenue from product orders for each type of . A can be thought of as a category that a particular customer falls under, as stored in the table. Revenue is calculated as a product of and .
Assume the following sample data:
Example Input:
| CustomerID | CustomerName | CustomerSegment |
|---|---|---|
| 1 | John Doe | Retail |
| 2 | Jane Doe | Commercial |
| 3 | Bob Smith | Industrial |
| 4 | Alice Johnson | Retail |
| 5 | Charlie Brown | Commercial |
Example Input:
| OrderID | CustomerID | OrderDate | ProductCode | Quantity | PriceEach |
|---|---|---|---|---|---|
| 1 | 1 | 2022-01-02 | A1 | 5 | 100.00 |
| 2 | 1 | 2022-01-03 | B1 | 3 | 250.00 |
| 3 | 2 | 2022-02-04 | C1 | 2 | 500.00 |
| 4 | 3 | 2022-03-05 | D1 | 1 | 1000.00 |
| 5 | 4 | 2022-01-06 | E1 | 4 | 50.00 |
| 6 | 5 | 2022-02-07 | F1 | 8 | 20.00 |
Answer:
This PostgreSQL query joins the table with the table based on the condition that the CustomerID in both tables is the same. The function is used to extract the month and year from the OrderDate for grouping. For each month and customer segment, the monthly revenue is calculated by summing up the product of quantity and price for each product ordered. The results are then ordered by month and monthly revenue in descending order.
Because joins come up routinely during SQL interviews, practice this Spotify JOIN SQL question:
SQL Question 10: What's the difference between a one-to-one vs. a one-to-many relationship between two entities? Give examples.
In database schema design, a one-to-one relationship between two entities is characterized by each entity being related to a single instance of the other. An example of this is the relationship between a US citizen and their social-security number (SSN) - each citizen has one SSN, and each SSN belongs to one person.
On the other hand, a one-to-many relationship is when one entity can be associated with multiple instances of the other entity. A teacher's relationship with their classes is an example of this - a teacher can teach many classes, but each class is only associated with one teacher.
Preparing For The Leggett & Platt SQL Interview
Assuming that you've already got basic SQL skills, the next best tip we have to prepare for the Leggett & Platt SQL interview is to solve as many practice SQL interview questions as you can! Besides solving the above Leggett & Platt SQL interview questions, you should also solve the 200+ DataLemur interview questions which come from companies like FAANG tech companies and tech startups.
Each DataLemur SQL question has multiple hints, fully explained answers along with a discussion board to see how others solved it and most importantly, there's an interactive SQL code editor so you can instantly run your SQL query and have it checked.
To prep for the Leggett & Platt SQL interview you can also be a great idea to practice SQL questions from other consumer good companies like:
Learn how Leggett & Platt is driving growth, innovation, and excellence with their latest press releases!
In case your SQL coding skills are weak, forget about jumping right into solving questions – improve your SQL foundations with this free SQL tutorial.
This tutorial covers SQL topics like math functions like ROUND()/CEIL() and grouping by multiple columns – both of these show up routinely during Leggett & Platt interviews.
Leggett & Platt Data Science Interview Tips
What Do Leggett & Platt Data Science Interviews Cover?
Besides SQL interview questions, the other types of problems covered in the Leggett & Platt Data Science Interview include:
- Probability & Statistics Questions
- Python or R Programming Questions
- Product Analytics Questions
- ML Modelling Questions
- Behavioral Questions centered on Leggett & Platt values & principles
How To Prepare for Leggett & Platt Data Science Interviews?
To prepare for Leggett & Platt Data Science interviews read the book Ace the Data Science Interview because it's got:
- 201 interview questions taken from Google, Microsoft & tech startups
- a refresher covering Python, SQL & ML
- over 1000+ 5-star reviews on Amazon
Don't forget about the behavioral interview – prepare for it with this Behavioral Interview Guide for Data Scientists.