9 3M SQL Interview Questions (Updated 2024)

3M employees use SQL to analyze manufacturing data, allowing them to pinpoint areas for improving production efficiency and enhancing product quality. They also use it for checking customer databases to predict sales trends, allowing them to make informed decisions about product development and marketing strategies, which is why 3M asks SQL coding questions during interviews for Data Science, Analytics, and Data Engineering jobs.

Thus, to help you prep, here's 9 3M SQL interview questions – can you answer each one?

9 3M SQL Interview Questions

SQL Question 1: Identify Power Users at 3M

3M is a multinational conglomerate company that produces over 60,000 products. In our imaginary scenario, let's say they started an online platform where users can purchase their products directly. You are asked to identify the power users of their platform. Power users are determined by two factors: Their purchase frequency and the amount spent. A power user is categorised as someone who has made more than 50 purchases in the last month and has spent more than a total of $1000.

Example Input:

Example Input:

Answer:

This SQL query will join the users table with the purchases table on user_id and then filter for purchases made in the last month. It then groups by username and selects users who have made more than 50 purchases and spent more than $1000 in the last month, thus identifying the power users at 3M.

To work on a similar customer analytics SQL question where you can code right in the browser and have your SQL query automatically checked, try this Walmart SQL Interview Question:

Dive into 3M's insights on machine learning and artificial intelligence, showcasing innovative applications that are shaping the future! Learning about 3M's advancements can help you appreciate the transformative impact of technology on improving processes and creating new opportunities.

SQL Question 2: Top 3 Department Salaries

Given a table of 3M employee salary information, write a SQL query to find the top 3 highest earning employees within each department.

3M Example Input:

Example Input:

Example Output:

Write a SQL query for this problem 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 solution above is tough, you can find a detailed solution here: Top 3 Department Salaries.

SQL Question 3: When would you use the constraint?

A is a field in a table that references the of another table. It creates a link between the two tables and ensures that the data in the field is valid.

For example, if you have a table of 3M customers and an orders table, the customer_id column in the orders table could be a that references the id column (which is the primary key) in the 3M customers table.

The constraint helps maintain the integrity of the data in the database by preventing the insertion of rows in the table that do not have corresponding entries in the table. It also enforces the relationship between the two tables and prevents data from being deleted from the table if it is still being referenced in the table.

SQL Question 4: Sales Analysis using Window Function

As a data analyst for 3M, your task is to analyze the monthly sales of different products. Here are the details:

1. The table has the following columns: (int), (int), (date), (int).
2. All products sold are from 3M.
3. Your goal is to write a SQL query using a window function to compute the total monthly sales quantity of each product.
4. The result should be ordered by and then by .

Sample Input:

Sample Output:

Answer:

This SQL query works by partitioning our table by both and month of . Then, by using the window function on , it calculates the total monthly sales quantity of each product. Finally, the resultset is ordered by and month.

Pro Tip: Window functions are a frequent SQL interview topic, so practice all the window function problems on DataLemur

SQL Question 5: What's the difference between relational and NoSQL databases?

A non-relational (NoSQL) database is any database that does not use the typical tabular format of rows and columns like in relational databases.

While knowing the four different types of NoSQL databases is beyond the scope of most Data Analyst & Data Science interviews, Data Engineers at 3M should vaguely refresh these concepts:

• Document Databases – this database is designed for storing and querying retrieving document data (where each key is associated with a flexible document)
• Key-Value Stores – these databases uses keys where each key is associated with only one value in a collection (similar to a Python dictionary data structure!)
• Wide-Column Stores – this database uses tables, rows, and columns, but unlike a relational database, the names and format of the columns can vary from row to row within the same table. Remember: "-With great flexibility comes great responsiblity-" – Batman if he was learning about NoSQL databases!
• Graph Stores – represents data in terms of the graph data structure, with nodes and edges between entities

SQL Question 6: Filter and Record Average Star Ratings for 3M Products

As part of the product development team at 3M, you are tasked with assessing customer satisfaction for your products based on the star ratings provided in the reviews submitted by the users.

For each product, you are required to filter and record average star ratings on a monthly basis. Only consider reviews where the submit date is in the year 2022 and the user_id is even. Additionally, only include products that have received more than three reviews in that month.

Example Input:

Example Output:

Answer:

This query filters the reviews from the year 2022 and from users with an even . It calculates the average star rating per product per month, while only including products that received more than three reviews in a given month. The clause groups the reviews by month and then by product. The clause filters out products that have less than four reviews in a given month.

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

A join in SQL combines rows from two or more tables based on a shared column or set of columns.

Four types of JOINs exist in SQL. To demonstrate each one, say you had a table of 3M orders and 3M customers.

INNER JOIN: When there is a match in the shared key or keys, rows from both tables are retrieved. In this example, an between the and tables would retrieve rows where the in the table matches the in the table.

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

RIGHT JOIN: A retrieves all rows from the right table (in this case, the table) and any matching rows from the left table (the 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 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: Average Sales per Product Category

As a data analyst at 3M, a multinational conglomerate corporation known for its research and development of industrial and consumer products, you are tasked with finding the average sales per product category across different regions.

Data is organized across two tables: and .

Example Input:

Example Input:

We want to obtain the average units sold per product category, and the output should include the category name and the average units sold, in descending order.

Example Output:

Answer:

In this query, we first join the two tables using the field that is common to both. Then we group the data by (which represents the product categories) and calculate the average for each group. Finally, we order by in descending order to get the categories with the highest average sales at the top.

To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for identification of high performing products or this Wayfair Y-on-Y Growth Rate Question which is similar for sales-related analysis.

SQL Question 9: Compute the click-through-rate for 3M digital campaigns

3M Corporation launched several digital marketing campaigns. The marketing team needs a report to understand the efficacy of these campaigns, particularly the click-through-rate (CTR). The CTR is defined as the number of unique users who clicked an ad divided by the number of unique users to whom the ad was shown. Note that users might see the same ad multiple times in the campaign.

You are provided with two tables. The table logs every instance in which an ad was served to a user. The table logs every instance in which a user clicked on an ad.

Example Input:

Example Input:

Write a PostgreSQL query to compute the CTR for each ad in July 2022.

Answer:

This query first creates two subqueries: and , aggregating the count of distinct users for each ad from and tables, respectively, for the month of July in 2022.

The main query then left joins onto on , ensuring all ads that were displayed (even if not clicked) are included. It calculates the CTR by dividing the number of clicks by the number of displays, using the function to treat null values (i.e., if an ad was displayed but not clicked) as zeroes in computing the CTR.

To solve a similar SQL problem on DataLemur's free interactive SQL code editor, try this Facebook SQL Interview question:

Preparing For The 3M SQL Interview

The best way to prepare for a 3M SQL interview is to practice, practice, practice. In addition to solving the earlier 3M SQL interview questions, you should also solve the 200+ DataLemur interview questions which come from companies like tech companies and industrial chemical companies like 3M.

Each problem on DataLemur has multiple hints, step-by-step solutions and most importantly, there's an interactive coding environment so you can instantly run your query and have it graded.

To prep for the 3M SQL interview you can also be wise to solve SQL problems from other industrial chemical companies like:

• Sherwin-Williams SQL questions
• Dow Chemical SQL questions
• DuPont SQL questions
• Air Products SQL questions

However, if your SQL foundations are weak, don't worry about jumping right into solving questions – go learn SQL with this free SQL tutorial.

This tutorial covers SQL topics like removing NULLs and joining a table to itself – both of these show up routinely in SQL interviews at 3M.

3M Data Science Interview Tips

What Do 3M Data Science Interviews Cover?

Besides SQL interview questions, the other question categories to practice for the 3M Data Science Interview include:

• Statistics Interview Questions
• Coding Questions in Python or R
• Product Analytics Questions
• Machine Learning Questions
• Behavioral Questions based on 3M company values

How To Prepare for 3M Data Science Interviews?

To prepare for 3M Data Science interviews read the book Ace the Data Science Interview because it's got:

• 201 interview questions taken from companies like Google, Tesla, & Goldman Sachs
• a crash course on Stats, SQL & ML
• over 1000+ 5-star reviews on Amazon

Don't forget about the behavioral interview – prepare for it using this guide on behavioral interview questions.