9 Dun & Bradstreet SQL Interview Questions (Updated 2024)
Data Science, Data Engineering and Data Analytics employees at Dun & Bradstreet use SQL to analyze company credit data to identify creditworthiness and payment patterns of businesses. That is why Dun & Bradstreet asks prospective hires SQL interview problems.
Thus, to help you study, we've curated 9 Dun & Bradstreet SQL interview questions – can you solve them?
9 Dun & Bradstreet SQL Interview Questions
SQL Question 1: Identifying the VIP Customers for Dun & Bradstreet
Dun & Bradstreet is a company that provides commercial data, analytics, and insights for businesses. Let's assume they record their customer's interactions with their database - every data request a customer makes is logged with its unique ID, the customer ID, the type of data requested, and the timestamp of the request.
From this, they define VIP customers as those that have made at least 50 data requests in the past month.
Given the following sample tables of , write a SQL query to identify the VIP customers for the past month.
Example Input:
| request_id | customer_id | data_type | timestamp |
|---|---|---|---|
| 222 | 101 | Company Profile | 2022-10-10 15:12:42 |
| 333 | 201 | Market Insight | 2022-10-05 11:02:15 |
| 444 | 101 | Company Profile | 2022-09-25 08:18:32 |
| 555 | 301 | Data Integration | 2022-09-22 14:56:48 |
| 666 | 201 | Company Profile | 2022-09-15 09:07:26 |
Answer:
This query first filters out requests from the past month. It then aggregates requests by the customer_id and counts the number of requests each customer has made. The clause is used to filter out those customers who made at least 50 requests within this period, identifying them as VIP customers.
To work on another SQL customer analytics question where you can solve it interactively and have your SQL query automatically checked, try this Walmart SQL Interview Question:
SQL Question 2: Employee Salaries Higher Than Their Manager
Imagine there was a table of Dun & Bradstreet employee salary data. Write a SQL query to find the employees who earn more than their direct manager.
Dun & Bradstreet Example Input:
| employee_id | name | salary | department_id | manager_id |
|---|---|---|---|---|
| 1 | Emma Thompson | 3800 | 1 | |
| 2 | Daniel Rodriguez | 2230 | 1 | 10 |
| 3 | Olivia Smith | 8000 | 1 | 8 |
| 4 | Noah Johnson | 6800 | 2 | 8 |
| 5 | Sophia Martinez | 1750 | 1 | 10 |
| 8 | William Davis | 7000 | 2 | NULL |
| 10 | James Anderson | 4000 | 1 | NULL |
Example Output:
| employee_id | employee_name |
|---|---|
| 3 | Olivia Smith |
This is the output because Olivia Smith earns $8,000, surpassing her manager, William Davis who earns 7,800.
Try this interview question interactively on DataLemur:
Answer:
First, we perform a SELF-JOIN where we treat the first table () as the managers' table and the second table () as the employees' table. Then we use a clause to filter the results, ensuring we only get employees whose salaries are higher than their manager's salary.
If the code above is hard to understand, you can find a detailed solution with hints here: Employees Earning More Than Their Boss.
SQL Question 3: What's a stored procedure?
Stored procedures are a lot like functions in programming. They're used to encapsulate and organize business logic into one unit of code, and they can accept multiple input parameters and return multiple output values.
For a concrete example, suppose you were a Data Scientist on the Marketing Analytics team at Dun & Bradstreet. A task you would encounter freqently would be to calculate the conversion rate for Dun & Bradstreet's ads over a certain time period, for which you would write the following stored procedure:
To use this conversion rate stored procedure, you could call it like this:
SQL Question 4: Calculate the Average Monthly Credit Score for Each Company
Dun & Bradstreet is a company that provides commercial data, analytics, and insights for businesses. They have a credit score to rate the creditworthiness of each company. A high credit score indicates a higher creditworthiness.
Please write a SQL query to calculate the average monthly credit score for each company.
Example Input:
| period | company_id | credit_score |
|---|---|---|
| 2022-01-01 | 1 | 700 |
| 2022-01-02 | 2 | 600 |
| 2022-01-03 | 1 | 800 |
| 2022-02-01 | 1 | 700 |
| 2022-02-02 | 2 | 650 |
| 2022-02-03 | 1 | 750 |
Expected Output:
| company_id | month | avg_credit_score |
|---|---|---|
| 1 | 1 | 750 |
| 1 | 2 | 725 |
| 2 | 1 | 600 |
| 2 | 2 | 650 |
Answer:
This query first creates a Common Table Expression (CTE) called 'monthly_scores'. It extracts the month from the period and calculates the average credit score for each company per month by using the AVG window function PARTITION BY company_id and month. The DISTINCT is used in the main query to eliminate duplicate rows and then the results are ordered by company_id and month.
Pro Tip: Window functions are a popular SQL interview topic, so practice all the window function problems on DataLemur
SQL Question 5: Can you explain the purpose of the constraint?
A is like a secret code that unlocks the door to another table. It's a field in one table that points to the (the master key) in another table. This helps keep the data in your database organized and tidy, because it won't let you add new rows to the table unless they have the correct secret code (a corresponding entry in the table).
It's also like a special bond between the two tables - if you try to delete the data, the will be like "Whoa, hold on! I still need that information!" and prevent the deletion from happening.
SQL Question 6: Filter Customers Based on Credit Range
At Dun & Bradstreet, our database contains information about businesses that covers a wide range of details. One of those key details is their credit score. An analyst has asked for a list of businesses that have a credit score above 650 and they are active. They also want to see those with an outstanding payment status. These are represented by three boolean values in our database: , (with being active and being inactive) and 'outstanding_payment' (with being having outstanding payment and being no outstanding payment). Can you write an SQL query which returns the company names, ids and their credit scores for this requirement?
Example Input:
| customer_id | company_name | credit_score | is_active | outstanding_payment |
|---|---|---|---|---|
| 101 | Acme Corp. | 700 | 1 | 0 |
| 102 | Beta Corp. | 580 | 0 | 1 |
| 103 | Gamma Corp. | 680 | 1 | 1 |
| 104 | Delta Corp. | 640 | 1 | 1 |
| 105 | Epsilon Corp. | 660 | 0 | 0 |
Example Output:
| customer_id | company_name | credit_score |
|---|---|---|
| 101 | Acme Corp. | 700 |
| 103 | Gamma Corp. | 680 |
Answer:
This query simply selects the , , and from the table where their credit score is above , they are marked as active (), and they have some outstanding payment. As such, it returns only those customers who meet all of these conditions.
SQL Question 7: How do the SQL commands and differ?
No, in 99% of normal cases a and do NOT produce the same result.
You can think of more as set addition when it combines the results of two tables.
You can think of a more as set multiplication, producing all combos of table 1 with combos of table 2.
SQL Question 8: Average number of business credit scores per industry
Considering billing reports of Dun & Bradstreet, a company that provides commercial data, analytics and insights for businesses. Let's say, they want to know the average credit score for each industry in their database. The credit score is a measure of a business's creditworthiness, typically ranging from 1 to 100.
Below are the sample tables:
Example Input:
| company_id | company_name | industry |
|---|---|---|
| 5601 | ABC Corp | Manufacturing |
| 6721 | XYZ Corp | Healthcare |
| 7802 | DEF Corp | Manufacturing |
| 6234 | LMN Corp | IT Services |
| 8192 | OPQ Corp | Healthcare |
Example Input:
| score_id | company_id | credit_score |
|---|---|---|
| 0021 | 5601 | 75 |
| 0035 | 6721 | 94 |
| 0076 | 7802 | 88 |
| 0012 | 6234 | 90 |
| 0081 | 8192 | 85 |
You are required to write an SQL query to find the average credit score for each industry.
Answer:
Here is a solution with a PostgreSQL query:
This query first joins the table with the table on the field. It then uses the clause to partition the data by industry, and the function to find the average credit score per industry.
Please note that it's good practice to provide an alias for computed columns like . This query designates as . This makes the output easier to understand.
To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for requiring queries on aggregated data grouped by certain categories or this Amazon Average Review Ratings Question which is similar for needing to calculate average values grouped by distinct items.
SQL Question 9: Revenue Analysis Using Math Functions
Dun & Bradstreet is a company that specializes in wide range of business activities. One of their business units deals with selling online courses to students. For the current year, they have the following revenue data available for analysis. The management is interested in detailed insights about their revenue.
Given a table which represents sales of courses at Dun & Bradstreet, represented with columns: (course identifier), (student identifier), (price of the course), (discount given on the course, expressed as a percentage of the course price), and (date of purchase),
Write a SQL query that calculates the total revenue, total discounted amount, revenue after discount and average revenue per purchase for each day. Round the result values to two decimal places.
Example Input:
| course_id | student_id | course_price | discount_per_cent | purchase_date |
|---|---|---|---|---|
| 105 | 1123 | 300 | 5 | 06/08/2022 00:00:00 |
| 121 | 2650 | 200 | 10 | 06/10/2022 00:00:00 |
| 112 | 3629 | 400 | 8 | 06/18/2022 00:00:00 |
| 105 | 1927 | 300 | 7 | 07/26/2022 00:00:00 |
| 109 | 3481 | 350 | 6 | 07/05/2022 00:00:00 |
Answer:
The SQL query groups data by purchase day. It then uses the SUM() and AVG() functions to get the total revenue, total discounted amount, revenue after discount and average revenue per sale for each day. All these calculations also use the ROUND() function to limit the result to two decimal places. The use of arithmetic operators like *, / and - can also be observed to make calculations.
The two most similar questions are:
-
"Y-on-Y Growth Rate" by Wayfair: This queries about the growth rate in sales for a company, similar to Dun & Bradstreet looking to evaluate their revenue.
-
"Highest-Grossing Items" by Amazon: This involves writing a SQL query that identifies top revenue generating products within each category, similar to the analysis required for Dun & Bradstreet's course sales.
Here is the markdown you requested:
To practice a very similar question try this interactive Wayfair Y-on-Y Growth Rate Question which is similar for its focus on calculating growth rate in sales, or this Amazon Highest-Grossing Items Question which is similar for its requirement to find top revenue-generating products.
Preparing For The Dun & Bradstreet SQL Interview
The best way to prepare for a SQL interview, besides making sure you have strong SQL fundamentals, is to practice a ton of real SQL questions that were asked in recent job interviews. Besides solving the earlier Dun & Bradstreet SQL interview questions, you should also solve the 200+ SQL questions from real Data Science & Analytics interviews which come from companies like FAANG tech companies and tech startups.
Each problem on DataLemur has multiple hints, step-by-step solutions and best of all, there is an interactive SQL code editor so you can right in the browser run your SQL query answer and have it graded.
To prep for the Dun & Bradstreet SQL interview it is also a great idea to solve interview questions from other financial services companies like:
But if your SQL skills are weak, don't worry about diving straight into solving questions – go learn SQL with this free SQL tutorial.
This tutorial covers things like using LIKE and handling strings – both of these come up routinely during SQL interviews at Dun & Bradstreet.
Dun & Bradstreet Data Science Interview Tips
What Do Dun & Bradstreet Data Science Interviews Cover?
In addition to SQL interview questions, the other types of questions covered in the Dun & Bradstreet Data Science Interview are:
- Probability & Statistics Questions
- Python or R Programming Questions
- Product-Sense Questions
- ML Modelling Questions
- Behavioral & Resume-Based Questions
Read about the power of Dun & Bradstreet's data, insights, and AI-driven platforms in driving business success!
How To Prepare for Dun & Bradstreet Data Science Interviews?
To prepare for the Dun & Bradstreet Data Science interview make sure you have a strong understanding of the company's values and company principles – this will be clutch for acing the behavioral interview. For the technical Data Science interviews, prepare by reading Ace the Data Science Interview. The book's got:
- 201 Interview Questions from FAANG & startups
- A Crash Course covering SQL, Product-Sense & ML
- Great Reviews (1000+ reviews, 4.5-star rating)