9 Fortune Brands SQL Interview Questions (Updated 2024)
Fortune Brands employees use SQL for extracting raw data from different databases, such as customer transaction records and website analytics, as well as analyzing customer behavior trends, like purchase frequency and product preferences, in the home and security industry. That is why Fortune Brands asks SQL questions in interviews for Data Science, Analytics, and & Data Engineering jobs.
So, to help you practice, we've curated 9 Fortune Brands Innovations SQL interview questions – can you solve them?
9 Fortune Brands Innovations SQL Interview Questions
SQL Question 1: Calculate the Monthly Average Sales Per Product
You are a data analyst at Fortune Brands. The sales department needs monthly reports to understand the average sales per product. The task is to write a SQL query that calculates the monthly average sales for each product. Output should include the month, product_id and the average sales for that product for the month. Assume you fetch data from a table named that has the following structure:
Example Input:
| sale_id | sale_date | product_id | sale_value |
|---|---|---|---|
| 1001 | 2021-01-15 | 001 | 200.00 |
| 1002 | 2021-01-20 | 002 | 300.00 |
| 1003 | 2021-02-15 | 001 | 250.00 |
| 1004 | 2021-02-20 | 002 | 350.00 |
| 1005 | 2021-02-25 | 002 | 400.00 |
Example Output:
| month | product | avg_sale_value |
|---|---|---|
| 1 | 001 | 200.00 |
| 1 | 002 | 300.00 |
| 2 | 001 | 250.00 |
| 2 | 002 | 375.00 |
Answer:
Here is the PostgreSQL query to solve the problem:
In the query above, the clause in the window function is used to divide the table into smaller sets or partitions by and . Then, the average sale value of each product per month is calculated within its own partition. In other words, the window function operates on each window (in our case it's product per month) and computes the average sale value per window. This table is then ordered by month and product_id for easy reading.
p.s. Window functions show up super frequently during SQL interviews, so practice the 27+ window function questions on DataLemur
SQL Question 2: Top Three Salaries
Given a table of Fortune Brands employee salary information, write a SQL query to find the top 3 highest earning employees in each department.
Fortune Brands 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 |
Write a SQL query for this interview question directly within the browser on DataLemur:
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 code above is confusing, you can find a step-by-step solution with hints here: Top 3 Department Salaries.
SQL Question 3: What's the SQL command do, and can you give an example?
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, let's use to find all of Fortune Brands's Facebook video ads with more than 10k views that are also being run on YouTube:
SQL Question 4: Customer Purchase Patterns
Fortune Brands wants to analyze purchasing habits of its customers. Specifically, they are interested in knowing the number of unique products each customer has purchased, the total quantity of purchases, and the total amount spent by each customer.
They have 2 tables and :
Example Input:
| customer_id | first_name | last_name |
|---|---|---|
| 1 | John | Doe |
| 2 | Jane | Doe |
| 3 | Mary | Smith |
Example Input:
| purchase_id | customer_id | product_id | quantity | unit_price |
|---|---|---|---|---|
| 1 | 1 | 50001 | 1 | 150.00 |
| 2 | 1 | 69852 | 2 | 500.00 |
| 3 | 2 | 50001 | 3 | 150.00 |
| 4 | 3 | 69852 | 1 | 500.00 |
| 5 | 2 | 69852 | 2 | 500.00 |
The expected output should look something like this:
| customer_id | number_of_unique_products | total_quantity | total_amount_spent |
|---|---|---|---|
| 1 | 2 | 3 | 1150.00 |
| 2 | 2 | 5 | 1150.00 |
| 3 | 1 | 1 | 500.00 |
Answer:
Here is a PostgreSQL query which can be used to solve this problem:
This query groups rows in the table by , then aggregates within each group to calculate the required values. The function counts unique products purchased by each customer. The function calculates the total quantity of all products purchased by each customer. The function calculates the total amount spent by each customer.
SQL Question 5: In SQL, Are NULL values the same as a zero or blank space?
A value represents a missing or unknown value. It is different from a zero or a blank space, which are actual values.
It's important to handle values properly in SQL because they can cause unexpected results if not treated correctly. For example, comparisons involving values always result in . Also values are not included in calculations. For example, will ignore values in the column.
SQL Question 6: Calculate Average Revenue Per Sales Region
As a business analyst at Fortune Brands, your role involves analyzing the company's sales data. The company has customers across different regions and sales for different products. You are tasked to filter customer records and calculate the average revenue per sales region for each product.
Consider the and tables with the following schema:
Example Input:
| sale_id | customer_id | product_id | sale_date | revenue |
|---|---|---|---|---|
| 101 | 501 | 8501 | 06/02/2022 | 120.50 |
| 102 | 212 | 9402 | 07/09/2022 | 80.00 |
| 103 | 501 | 9402 | 07/15/2022 | 90.00 |
| 104 | 376 | 8501 | 07/20/2022 | 200.00 |
| 105 | 212 | 9402 | 08/01/2022 | 70.00 |
Example Input:
| customer_id | region |
|---|---|
| 501 | North America |
| 212 | Europe |
| 376 | Asia |
You are asked to write a SQL query that returns the following columns:
- region (from the customers table)
- product_id (from the sales table)
- average revenue for the product per region
Sort the results by region in ascending order and round the average revenue to 2 decimal places.
Answer:
This PostgreSQL query works by joining the and tables on the column. It then groups the result by and to calculate the average revenue per product for each sales region. The function is used to limit the average revenue to 2 decimal places and results are ordered by region in ascending order.
SQL Question 7: What does do, and when would you use this function?
The function can take in multiple paramaters, and returns the first input paramater that is not null. If all arguments are null, the COALESCE function will return null too.
Suppose you have a table of Fortune Brands salespeople and the number of deals they closed. If a salesperson didn't close any deals, the data from the 3rd-party CRM system exports a NULL value.
| sales_person | closed_deals |
|---|---|
| Jason Wright | NULL |
| Drew Jackson | 3 |
| Chris Ho | NULL |
| Adam Cohen | 2 |
| Samantha Perez | 4 |
To change these NULLs to zeros, you can use the function in the following way:
This would result in the following data:
| sales_person | closed_deals |
|---|---|
| Jason Wright | 0 |
| Drew Jackson | 3 |
| Chris Ho | 0 |
| Adam Cohen | 2 |
| Samantha Perez | 4 |
SQL Question 8: Calculate Average Sales for Each Product Category
As a data analyst at Fortune Brands, your task is to calculate the average sales for each product category for the past year. The company has multiple product categories and each category has multiple products. Sales are recorded in a separate table and you will need to use the function to calculate the required average.
You are given two tables - and . The table contains columns for , and . The table contains columns for , , and .
Example Input:
| product_id | product_name | product_category |
|---|---|---|
| 1 | ProductA | Category1 |
| 2 | ProductB | Category1 |
| 3 | ProductC | Category2 |
| 4 | ProductD | Category2 |
| 5 | ProductE | Category3 |
Example Input:
| sale_id | product_id | sale_date | sale_amount |
|---|---|---|---|
| 1 | 1 | 06/08/2021 | 1500 |
| 2 | 2 | 06/10/2021 | 2000 |
| 3 | 3 | 06/18/2021 | 1000 |
| 4 | 1 | 07/26/2021 | 1500 |
| 5 | 2 | 07/05/2021 | 2000 |
| 6 | 4 | 07/10/2021 | 500 |
| 7 | 5 | 07/20/2021 | 2000 |
Answer:
This query first joins the and tables on the column. Then it filters the sales records for the past year using the clause. Finally, it calculates the average sales amount for each product category using the function and groups the result by .
You would replace the '01/01/2021' and '12/31/2021' with the start and end date for the period you are interested in.
To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for requiring category-wise metric calculation or this Amazon Average Review Ratings Question which is similar for the use of AVG function across product categories.
SQL Question 9: Analyze the Click-Through-Rate for Fortune Brands Digital Ad Campaigns
Fortune Brands wants to analyze the success of their digital ad campaigns through the click-through-rate (CTR). They have two tables. The table has information about each ad including the ad_id and ad_name. The table keeps a log of each click on an ad including the ad_id, user_id, and the click_date.
Calculate the click-through-rate (CTR) for each ad in the 'ads' table. CTR is calculated as (total clicks / total ads displayed) * 100%.
Example Input:
| ad_id | ad_name |
|---|---|
| 1 | Kitchen Products |
| 2 | Home Security |
| 3 | Bathroom Products |
Example Input:
| click_id | ad_id | user_id | click_date |
|---|---|---|---|
| 101 | 1 | 123 | 06/08/2022 00:00:00 |
| 102 | 2 | 265 | 06/10/2022 00:00:00 |
| 103 | 1 | 362 | 06/11/2022 00:00:00 |
| 104 | 3 | 128 | 07/16/2022 00:00:00 |
| 105 | 2 | 981 | 07/20/2022 00:00:00 |
Answer:
We can obtain the required result by joining the ads and clicks table based on ad_id and applying aggregate function count on a group by based on ad_id.
In the query above, we are iterating over each ad in the ads table and counting the number of times that ad_id appears in the clicks table to get the total number of clicks for that ad. Then, to compute the click-through-rate, we divide the number of clicks by the total number of ads, multiply by 100 to get a percentage.
To practice a related SQL interview question on DataLemur's free interactive SQL code editor, solve this Meta SQL interview question:
Fortune Brands SQL Interview Tips
Assuming that you've already got basic SQL skills, the next best tip we have to prepare for the Fortune Brands SQL interview is to solve as many practice SQL interview questions as you can! Besides solving the above Fortune Brands SQL interview questions, you should also solve the 200+ SQL exercises on DataLemur which come from companies like Google, Facebook, Microsoft and Amazon.
Each SQL question has hints to guide you, step-by-step solutions and most importantly, there is an online SQL coding environment so you can easily right in the browser your query and have it executed.
To prep for the Fortune Brands SQL interview it is also wise to practice SQL problems from other consumer good companies like:
- Masco SQL questions
- Newell Brands SQL questions
- Mohawk Industries SQL questions
- Risdeo SQL questions
Explore the world of home and security with Fortune Brands' press releases and updates!
But if your SQL coding skills are weak, forget about diving straight into solving questions – refresh your SQL knowledge with this interactive SQL tutorial.
This tutorial covers SQL topics like aggregate window functions and filtering data with boolean operators – both of these pop up often in Fortune Brands SQL assessments.
Fortune Brands Innovations Data Science Interview Tips
What Do Fortune Brands Data Science Interviews Cover?
In addition to SQL interview questions, the other topics covered in the Fortune Brands Data Science Interview include:
- Probability & Stats Questions
- Python or R Coding Questions
- Product Data Science Interview Questions
- ML Interview Questions
- Behavioral Interview Questions centered on Fortune Brands values & principles
How To Prepare for Fortune Brands Data Science Interviews?
To prepare for Fortune Brands Data Science interviews read the book Ace the Data Science Interview because it's got:
- 201 interview questions taken from companies like Microsoft, Google & Amazon
- a refresher on Python, SQL & ML
- over 1000+ 5-star reviews on Amazon
Also focus on the behavioral interview – prepare for that with this guide on behavioral interview questions.