8 Accenture SQL Interview Questions (Updated 2024)
Data Scientists, Analysts, and Data Engineers at Accenture use SQL to analyze and extract customer purchase history and transactional data from massive databases, and join it with demographic data to provide comprehensive insights on consumer behavior. That's why Accenture asks SQL questions during interviews for data jobs.
So, to help you prepare, here's 8 Accenture SQL interview questions – can you answer each one?
8 Accenture SQL Interview Questions
SQL Question 1: Subject Matter Experts
You are tasked with identifying Subject Matter Experts (SMEs) at Accenture based on their work experience in specific domains. An employee qualifies as an SME if they meet either of the following criteria:
- They have 8 or more years of work experience in a single domain.
- They have 12 or more years of work experience across two different domains.
Write a query to return the employee IDs of all the subject matter experts at Accenture.
Table:
| Column Name | Type |
|---|---|
| employee_id | integer |
| domain | string |
| years_of_experience | integer |
Example Input:
| employee_id | domain | years_of_experience |
|---|---|---|
| 101 | Digital Transformation | 9 |
| 102 | Supply Chain | 6 |
| 102 | IoT | 7 |
| 103 | Change Management | 4 |
| 104 | DevOps | 5 |
| 104 | Cloud Migration | 5 |
| 104 | Agile Transformation | 5 |
Example Output:
| employee_id |
|---|
| 101 |
| 102 |
Answer:
p.s. you can practice Accenture SQL Interview Question on our intereractive platform!
SQL Question 2: Fill Missing Client Data
When accessing Accenture's retailer client's database, you observe that the category column in products table contains null values.
Write a query that returns the updated product table with all the category values filled in, taking into consideration the assumption that the first product in each category will always have a defined category value.
Table
| Column Name | Type |
|---|---|
| product_id | integer |
| category | varchar |
| name | varchar |
Example Input
| product_id | category | name |
|---|---|---|
| 1 | Shoes | Sperry Boat Shoe |
| 2 | Adidas Stan Smith | |
| 3 | Vans Authentic | |
| 4 | Jeans | Levi 511 |
| 5 | Wrangler Straight Fit | |
| 6 | Shirts | Lacoste Classic Polo |
| 7 | Nautica Linen Shirt |
Example Output
| product_id | category | name |
|---|---|---|
| 1 | Shoes | Sperry Boat Shoe |
| 2 | Shoes | Adidas Stan Smith |
| 3 | Shoes | Vans Authentic |
| 4 | Jeans | Levi 511 |
| 5 | Jeans | Wrangler Straight Fit |
| 6 | Shirts | Lacoste Classic Polo |
| 7 | Shirts | Nautica Linen Shirt |
Answer:
To see an alternative answer try solving this same Accenture SQL Interview Question on our interactive coding environment!
SQL Question 3: What is a SQL constraint?
The constraint makes sure that all values in a column are distinct. It is often paired with other constraints, like NOT NULL, to ensure that the data follows certain rules.
For example, say you were an analyst on the marketing team at Accenture, and had access to a database on marketing campaigns:
In this example, the constraint is applied to the "campaign_name" field to ensure that each campaign has a unique name. This helps to ensure the integrity of the data in the table and prevents errors that could occur if two campaigns had the same name.
SQL Question 4: Average Project Duration
At Accenture, you've been appointed as a data analyst. You're handed a dataset of all the company's projects within the last year, including their start and end dates. Your task is to find the average duration (in days) of all completed projects.
For the purpose of this question, let's assume there are no ongoing projects; all projects have a valid end date. Let's also assume the date format is 'MM/DD/YYYY'.
Below is example data in table :
Example Input:
| project_id | start_date | end_date |
|---|---|---|
| 101 | 01/01/2022 | 04/01/2022 |
| 102 | 02/15/2022 | 05/15/2022 |
| 103 | 04/01/2022 | 07/30/2022 |
| 104 | 05/10/2022 | 07/10/2022 |
| 105 | 09/15/2022 | 12/01/2022 |
Answer:
This PostgreSQL SQL statement will calculate the average project duration in days. The type casting is used to convert the date columns (which are strings) into timestamp types. The difference between two timestamps will yield an interval type, from which we extract the number of days with the function. Finally, computes the average of all these day differences.
To practice a very similar question try this interactive Facebook Average Post Hiatus (Part 1) Question which is similar for calculating the duration in days between two events or this Amazon Server Utilization Time Question which is similar for calculating total time durations.
SQL Question 5: How do relational and non-relational databases differ?
While both types of databases are used to store data (obviously), there's some key differences in how they store and organize data.
Relational databases try to represent the world into neat little tables, with rows and columns. Non-relational (NoSQL) databases use a variety of data models to represent data, including document, key-value, columnar, and graph storage formats.
While the exact types of NoSQL databases is beyond the scope of a Data Analyst and Data Scientist SQL interview at Accenture, it's good to know that companies generally choose to use NoSQL databases:
- when dealing with unstructured or semi-structured data
- when the database needs to be scaled horizontally easily
- when the data is non-relational (like storing social network data which makes more sense in a graph format)
SQL Question 6: Calculate the Click Through Conversion Rate
As a data analyst at Accenture, you are tasked to analyze the effectiveness of digital marketing campaigns. Specifically, Accenture is interested in knowing the click-through conversion rate, which is defined as the number of users who viewed a product and later added it to the cart. Using the provided tables and , calculate the click-through conversion rate.
Example Input:
| view_id | user_id | view_date | product_id |
|---|---|---|---|
| 1001 | 123 | 06/08/2022 00:00:00 | 20001 |
| 2015 | 265 | 06/10/2022 00:00:00 | 22552 |
| 3036 | 362 | 06/18/2022 00:00:00 | 20001 |
| 4879 | 265 | 07/26/2022 00:00:00 | 22552 |
| 5623 | 981 | 07/05/2022 00:00:00 | 22552 |
Example Input:
| cart_id | user_id | add_to_cart_date | product_id |
|---|---|---|---|
| 2123 | 123 | 06/08/2022 00:00:00 | 20001 |
| 3856 | 362 | 06/21/2022 00:00:00 | 20001 |
| 4987 | 265 | 07/30/2022 00:00:00 | 22552 |
Answer:
This query first calculates the total view and total add-to-cart counts for each product using two separate CTEs ( and ). It then combines the two CTEs based on the and calculates the click-through conversion rate as the ratio of to , expressed as a percentage.
To practice a related SQL problem on DataLemur's free interactive SQL code editor, try this SQL interview question asked by Facebook:
SQL Question 7: What's the purpose of the constraint?
A is a column or set of columns in a table that references the primary key of another table. It is used to link the two tables together, and to ensure that the data in the foreign key column is valid.
The constraint helps to ensure the integrity of the data in the database by preventing the insertion of rows in the foreign key table that do not have corresponding entries in the primary key table. It also helps to enforce the relationship between the two tables, and can be used to ensure that data is not deleted from the primary key table if there are still references to it in the foreign key table.
For example, if you have a table of Accenture 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 Accenture customers table.
SQL Question 8: Average project cost per year
As a part of Accenture, a global professional services company, you are required to keep tracks of various projects carried out throughout the year and their respective costs. Write a SQL query to find out the average project cost per year.
Example Input:
| project_id | year | project_name | cost |
|---|---|---|---|
| 101 | 2021 | "Project Alpha" | 30000 |
| 102 | 2021 | "Project Beta" | 50000 |
| 103 | 2021 | "Project Gamma" | 15000 |
| 104 | 2022 | "Project Delta" | 45000 |
| 105 | 2022 | "Project Epsilon" | 35000 |
| 106 | 2022 | "Project Zeta" | 27000 |
Example Output:
| year | avg_cost |
|---|---|
| 2021 | 31666.67 |
| 2022 | 35666.67 |
Answer:
This SQL query groups the projects by the year and calculates the average project cost for each year. GROUP BY statement is paired with AVG, an aggregate function to calculate the average. The result will display the average cost per project for each year.
Accenture SQL Interview Tips
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. Beyond just solving the earlier Accenture SQL interview questions, you should also solve the 200+ DataLemur interview questions which come from companies like Meta, Google and consulting and professional service companies like Accenture.
Each exercise has hints to guide you, step-by-step solutions and best of all, there's an online SQL coding environment so you can easily right in the browser your query and have it checked.
To prep for the Accenture SQL interview you can also be wise to practice interview questions from other consulting and professional service companies like:
In case your SQL foundations are weak, forget about going right into solving questions – improve your SQL foundations with this DataLemur SQL tutorial.
This tutorial covers things like working with string/text data and filtering groups with HAVING – both of which pop up frequently during SQL job interviews at Accenture.
Accenture Data Science Interview Tips
What Do Accenture Data Science Interviews Cover?
In addition to SQL interview questions, the other question categories tested in the Accenture Data Science Interview are:
- Statistics Interview Questions
- Python or R Coding Questions
- Product-Sense Questions
- ML Modelling Questions
- Behavioral Interview Questions
Discover how Accenture is harnessing the power of AI and data to drive business transformation!
How To Prepare for Accenture Data Science Interviews?
To prepare for the Accenture Data Science interview have a firm understanding of the company's values and mission – this will be key to acing the behavioral interview. For the technical Data Science interviews, get ready by reading Ace the Data Science Interview. The book's got:
- 201 Interview Questions from Facebook, Google, & Amazon
- A Crash Course on SQL, AB Testing & ML
- Amazing Reviews (1000+ 5-star reviews on Amazon)