# 9 Fortune Brands SQL Interview Questions (Updated 2024)

Updated on

August 11, 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_idsale_dateproduct_idsale_value
10012021-01-15001200.00
10022021-01-20002300.00
10032021-02-15001250.00
10042021-02-20002350.00
10052021-02-25002400.00
##### Example Output:
monthproductavg_sale_value
1001200.00
1002300.00
2001250.00
2002375.00

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_idnamesalarydepartment_id
1Emma Thompson38001
2Daniel Rodriguez22301
3Olivia Smith20001
4Noah Johnson68002
5Sophia Martinez17501
8William Davis68002
10James Anderson40001

#### Example Input:

department_iddepartment_name
1Data Analytics
2Data Science

#### Example Output:

department_namenamesalary
Data AnalyticsJames Anderson4000
Data AnalyticsEmma Thompson3800
Data AnalyticsDaniel Rodriguez2230
Data ScienceNoah Johnson6800
Data ScienceWilliam Davis6800

Write a SQL query for this interview question directly within the browser on DataLemur:

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_idfirst_namelast_name
1JohnDoe
2JaneDoe
3MarySmith

#### Example Input:

purchase_idcustomer_idproduct_idquantityunit_price
11500011150.00
21698522500.00
32500013150.00
43698521500.00
52698522500.00

#### The expected output should look something like this:

customer_idnumber_of_unique_productstotal_quantitytotal_amount_spent
1231150.00
2251150.00
311500.00

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_idcustomer_idproduct_idsale_daterevenue
101501850106/02/2022120.50
102212940207/09/202280.00
103501940207/15/202290.00
104376850107/20/2022200.00
105212940208/01/202270.00
##### Example Input:
customer_idregion
501North America
212Europe
376Asia

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.

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_personclosed_deals
Jason WrightNULL
Drew Jackson3
Chris HoNULL
Samantha Perez4

To change these NULLs to zeros, you can use the function in the following way:

This would result in the following data:

sales_personclosed_deals
Jason Wright0
Drew Jackson3
Chris Ho0
Samantha Perez4

### 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_idproduct_nameproduct_category
1ProductACategory1
2ProductBCategory1
3ProductCCategory2
4ProductDCategory2
5ProductECategory3
##### Example Input:
sale_idproduct_idsale_datesale_amount
1106/08/20211500
2206/10/20212000
3306/18/20211000
4107/26/20211500
5207/05/20212000
6407/10/2021500
7507/20/20212000

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:
1Kitchen Products
2Home Security
3Bathroom Products
##### Example Input:
101112306/08/2022 00:00:00
102226506/10/2022 00:00:00
103136206/11/2022 00:00:00
104312807/16/2022 00:00:00
105298107/20/2022 00:00:00

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:

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.