11 Procter & Gamble SQL Interview Questions (Updated 2024)
Procter & Gamble employees write SQL queries for analyzing customer behavior trends in market data to inform product development and marketing strategies. It is also essential for predicting future sales through regression models based on historical purchase records and seasonal fluctuations. That is the reason why Procter & Gamble asks SQL questions in interviews for Data Analyst, Data Science, and BI jobs.
To help you study for the Procter & Gamble SQL interview, we'll cover 11 Procter & Gamble SQL interview questions – scroll down to start solving them!
11 Procter & Gamble SQL Interview Questions
SQL Question 1: Identify the Power Users for Procter & Gamble
Given a database that maintains the purchase history of customers in a table and customer details in another table , write a SQL query to identify the top 5 customers who have made the most frequent purchases in 2022. For Procter & Gamble, these customers would be considered as power users.
The table has as the foreign key to and records each transaction's , , , and .
Example Input:
| customer_id | name | |
|---|---|---|
| 251 | John Doe | johndoe@example.com |
| 552 | Jane Smith | janesmith@example.com |
| 834 | Bob Johnson | bobjohnson@example.com |
| 933 | Alice Williams | alicewilliams@example.com |
| 1234 | Charlie Brown | charliebrown@example.com |
Example Input:
| purchase_id | customer_id | product_id | quantity | purchase_date |
|---|---|---|---|---|
| 6001 | 251 | 1001 | 3 | 2022-01-15 |
| 6002 | 552 | 1045 | 2 | 2022-02-10 |
| 6003 | 834 | 1089 | 5 | 2022-03-20 |
| 6004 | 251 | 1001 | 1 | 2022-03-25 |
| 6005 | 933 | 1045 | 2 | 2022-04-10 |
| 6006 | 552 | 1089 | 1 | 2022-04-15 |
| 6007 | 251 | 1010 | 2 | 2022-05-15 |
| 6008 | 1234 | 1001 | 3 | 2022-06-15 |
Answer:
It joins customer_details with purchase_history on and counts the number of purchases for each customer for the year 2022 by extraction of year from the . It finally orders the customers by their purchase frequency in descending and limits the query results to the top 5.
To practice a related customer analytics question on DataLemur's free interactive SQL code editor, try this recently asked Microsoft SQL interview question:
SQL Question 2: Top Department Salaries
Given a table of Procter & Gamble employee salary information, write a SQL query to find the top three highest paid employees within each department.
Procter & Gamble 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 |
Try this interview question interactively 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 with hints here: Top 3 Department Salaries.
SQL Question 3: How can you determine which records in one table are not present in another?
To locate records in one table that are absent from another, you can use a and then look for values in the right-side table.
For example, say you exported Procter & Gamble's CRM (Customer Relationship Management) database, and had a table of sales leads, and a second table of companies.
Here's an example of how a query can find all sales leads that are not associated with a company:
This query returns all rows from the sales leads table, along with any matching rows from the companies table. If there is no matching row in the companies table, values will be returned for all of the right table's columns. The clause then filters out any rows where the column is , leaving only sales leads that are NOT associated with a company.
SQL Question 4: Calculate average monthly product ratings
At Procter & Gamble, you have been given a task to analyze customer reviews. Specifically, you need to determine the average star rating for each product on a monthly basis. Your task is to write a SQL query to achieve this.
Example Input:
| review_id | user_id | submit_date | product_id | stars |
|---|---|---|---|---|
| 101 | 101 | 2022-01-14 | 201 | 4 |
| 102 | 102 | 2022-02-18 | 202 | 5 |
| 103 | 103 | 2022-01-20 | 201 | 3 |
| 104 | 104 | 2022-02-23 | 202 | 4 |
| 105 | 105 | 2022-03-28 | 203 | 2 |
| 106 | 102 | 2022-03-18 | 202 | 5 |
| 107 | 101 | 2022-01-15 | 201 | 2 |
Example Output:
| mth | product_id | avg_stars |
|---|---|---|
| 1 | 201 | 3.00 |
| 2 | 202 | 4.50 |
| 3 | 202 | 5.00 |
| 3 | 203 | 2.00 |
Answer:
This SQL query uses the and features to calculate the monthly average star rating for each product. It starts by extracting the month from the field (using the function). Then it groups the reviews by month and product (using ), so we get one row per product per month. Finally, it averages the field within each group (using ) to find the average rating for each product in each month.
To practice another window function question on DataLemur's free interactive SQL code editor, try this Amazon SQL Interview Question:
SQL Question 5: What's the difference between and ?
The clause is used to filter the groups created by the clause. It's similar to the clause, but it is used to specify conditions on the groups created by the clause, rather than on the individual rows of the table.
For example, say you were analyzing salaries for analytics employees at Procter & Gamble:
This query retrieves the total salary for each Analytics department at Procter & Gamble and groups the rows by the specific department (i.e. "Marketing Analytics", "Business Analytics", "Sales Analytics" teams).
The clause then filters the groups to include only Procter & Gamble departments where the total salary is greater than $1 million.
SQL Question 6: Average Rating of Procter & Gamble Products
Procter & Gamble (P&G) is an American multinational consumer goods corporation with various types of products ranging from personal health/consumer health to personal care and hygiene. Suppose we are given two tables, and . The products table has information about each product, including a unique , and the . The reviews table, on the other hand, contains customer reviews for each product, including the , , , , and (the star rating given by the user for a particular product).
Our goal is to create an SQL query that computes the average star rating for each product in the month of August 2022.
Example Input:
| product_id | product_name |
|---|---|
| 50001 | P&G Shampoo |
| 69852 | P&G Toothpaste |
Example Input:
| review_id | user_id | submit_date | product_id | stars |
|---|---|---|---|---|
| 6171 | 123 | 08/02/2022 00:00:00 | 50001 | 4 |
| 7802 | 265 | 08/10/2022 00:00:00 | 69852 | 4 |
| 5293 | 362 | 08/18/2022 00:00:00 | 50001 | 3 |
| 6352 | 192 | 08/26/2022 00:00:00 | 69852 | 3 |
| 4517 | 981 | 08/05/2022 00:00:00 | 69852 | 2 |
Answer:
The above SQL query first joins the table with the table on . It then filters the reviews to only include those submitted in August 2022. The query then groups the results by month and product, and finally computes the average star rating for each group.
SQL Question 7: What's the difference between a left and right join?
A join in SQL allows you to retrieve data from multiple tables and combine it into a single set of results.
To demonstrate the difference between left vs. right join, 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.
: A LEFT JOIN retrieves all rows from the left table (in this case, the table) and any matching rows from the right table (the Sales table). If there is no match in the right table, values will be returned for the right table's columns.
: 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 table). If there is no match in the left table, values will be returned for the left table's columns.
SQL Question 8: Filter Customers Based on Spend Amount and Location
As a Data Analyst at Procter & Gamble, you have been entrusted with the task of identifying customers who belong to a specific age group, live in 'Ohio' and have spent more than $1000 on the company's products in the last one year. Your focus primarily lies on the 'customers' and 'orders' databases. Write a SQL query to filter out such customers.
Here are the sample tables:
Example Input:
| customer_id | firstName | lastName | age | state |
|---|---|---|---|---|
| 101 | Jane | Doe | 30 | Ohio |
| 102 | John | Smith | 35 | Ohio |
| 103 | Mary | Johnson | 40 | California |
| 104 | James | Williams | 45 | Ohio |
| 105 | Patricia | Brown | 50 | Ohio |
Example Input:
| order_id | customer_id | product_id | order_date | amount |
|---|---|---|---|---|
| 201 | 101 | 501 | 06/15/2022 | 600 |
| 202 | 102 | 502 | 06/20/2022 | 500 |
| 203 | 103 | 503 | 07/25/2022 | 2000 |
| 204 | 104 | 504 | 03/30/2022 | 1500 |
| 205 | 105 | 505 | 08/10/2022 | 1100 |
Answer:
This SQL query will join the 'customers' and 'orders' tables on customer_id and then filter based on the conditions provided in the WHERE clause - age between 18 and 35, state as 'Ohio', and order date in the last year. The GROUP BY clause is used to group the results by customer details. The HAVING clause is then applied to check if the total amount spent by each customer is more than $1000.
SQL Question 9: Determine Average Sales Per Region for Procter & Gamble Products
Given a database of Procter & Gamble product sales across different regions, write an SQL query to determine the average number of units sold per region.
Here's an example of how the table might look:
Example Input:
| Sale_id | Region | Product | Units_Sold | Sale_Date |
|---|---|---|---|---|
| 1 | North America | Tide | 100 | 2022-01-01 |
| 2 | Europe | Crest | 200 | 2022-01-02 |
| 3 | Asia | Gillette | 150 | 2022-01-03 |
| 4 | North America | Pampers | 250 | 2022-01-04 |
| 5 | Europe | Tide | 125 | 2022-01-05 |
| 6 | Asia | Crest | 175 | 2022-01-06 |
We want to find the average units sold per region. Your answer should look something like this:
Example Output:
| Region | Average_Units_Sold |
|---|---|
| North America | 175 |
| Europe | 162.5 |
| Asia | 162.5 |
Answer:
This query calculates the average sales per region by grouping the records by the region and then applying the AVG function on the field. The GROUP BY clause in SQL is used to arrange identical data into groups, and the AVG function then calculates the average of these group values. This way, we get the average number of units sold in each region.
To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for calculating top products or this Wayfair Y-on-Y Growth Rate Question which is similar for sales analysis.
SQL Question 10: 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 Procter & Gamble 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 11: Analyzing Click-Through-Rates for P&G Digital Marketing Campaigns
In its digital marketing campaigns, Procter & Gamble (P&G) measures the effectiveness of its ads by the click-through rate. They also measure the conversion rate of visitors who view a product and then proceed to add it to their cart.
Using the and tables below, can you write a PostgreSQL query to calculate the following for each campaign:
- The click-through rate (CTR) defined as the number of clicks divided by the number of ad impressions.
- The conversion rate defined as the number of visitors who add a product to their cart divided by the number of clicks.
Example Input:
| click_id | user_id | campaign_id | click_time |
|---|---|---|---|
| 76491 | 169 | 305 | 06/08/2022 00:00:00 |
| 87652 | 422 | 698 | 06/10/2022 00:00:00 |
| 69423 | 764 | 305 | 06/18/2022 00:00:00 |
| 67452 | 239 | 698 | 07/26/2022 00:00:00 |
| 95317 | 825 | 305 | 07/05/2022 00:00:00 |
Example Input:
| conversion_id | user_id | campaign_id | conversion_time |
|---|---|---|---|
| 2394 | 169 | 305 | 06/08/2022 00:00:00 |
| 3485 | 764 | 305 | 06/18/2022 00:00:00 |
| 6876 | 422 | 698 | 06/10/2022 00:00:00 |
| 9274 | 239 | 698 | 07/26/2022 00:00:00 |
Answer:
This query first calculates the number of clicks and conversions for each campaign using two CTEs. It then calculates the click-through rate and the conversion rate on the joined results from the CTEs. For the click-through rate, we take the total number of clicks and divide it by the number of unique ad impressions (represented here by the distinct count of ), and for the conversion rate, we take the total number of conversions and divide it by the total number of clicks. The results are ordered by .
To solve another question about calculating rates, try this TikTok SQL Interview Question on DataLemur's interactive SQL code editor:
Procter & Gamble SQL Interview Tips
The best way to prepare for a Procter & Gamble SQL interview is to practice, practice, practice. In addition to solving the above Procter & Gamble SQL interview questions, you should also solve the 200+ SQL questions from real Data Science & Analytics interviews which come from companies like Microsoft, Google, and Meta
Each interview question has hints to guide you, full answers and crucially, there is an interactive SQL code editor so you can right in the browser run your SQL query and have it executed.
To prep for the Procter & Gamble SQL interview you can also be useful to solve SQL questions from other consumer good companies like:
- Spectrum Brands SQL questions
- Energizer SQL questions
- Central Garden & Pet SQL questions
- Tempur Sealy SQL questions
Discover how Procter & Gamble has been harnessing the power of data to drive business success!
However, if your SQL foundations are weak, don't worry about diving straight into solving questions – strengthen your SQL foundations with this SQL tutorial for Data Analytics.
This tutorial covers things like Subquery vs. CTE and SQL joins with practice exercises – both of which show up often in SQL job interviews at Procter & Gamble.
Procter & Gamble Data Science Interview Tips
What Do Procter & Gamble Data Science Interviews Cover?
Beyond writing SQL queries, the other topics to practice for the Procter & Gamble Data Science Interview are:
- Probability & Statistics Questions
- Python or R Coding Questions
- Product Analytics Questions
- ML Interview Questions
- Behavioral Interview Questions
How To Prepare for Procter & Gamble Data Science Interviews?
To prepare for the Procter & Gamble Data Science interview have a firm understanding of the company's culture and values – this will be clutch for acing the behavioral interview. For technical interviews prepare by reading Ace the Data Science Interview. The book's got:
- 201 Interview Questions from FAANG tech companies
- A Refresher on Stats, ML, & Data Case Studies
- Amazing Reviews (1000+ reviews, 4.5-star rating)