At NCR Voyix/Atleos, SQL is used often for extracting and analyzing ATM transaction data as well as managing and manipulating financial databases for pattern detection. So, it shouldn't surprise you that NCR frequently asks SQL coding questions during interviews for Data Science and Data Engineering positions.
So, to help you study for the NCR SQL interview, we've collected 11 NCR Corporation SQL interview questions – able to answer them all?
As an analyst of NCR, your task is to identify the top 10 customers who spent the most in the year 2022. Assume that every purchase transaction of a customer is stored in a "sales" table which contains the following fields: user_id, purchase_date (YYYY-MM-DD), and purchase_amount. Please provide the SQL query you would use in PostgreSQL.
user_id | purchase_date | purchase_amount |
---|---|---|
1 | 2022-05-17 | 100.25 |
2 | 2022-03-20 | 200.40 |
1 | 2022-07-30 | 50.12 |
2 | 2022-09-22 | 300.80 |
3 | 2022-08-11 | 75.75 |
4 | 2022-08-14 | 80.95 |
In PostgreSQL, you would use the following query to solve this problem:
The query calculates the total amount spent per user within the year of 2022 by using the SUM() function and filters the data for that specific year via the WHERE clause. It then orders the data by total_spent in descending order and limits the result to the top 10 customers, which are the main contributors to the company.
To practice a related customer analytics question on DataLemur's free interactive coding environment, try this recently asked Microsoft SQL interview question:
NCR Corporation is a company specializing in consumer transaction technologies. Imagine you are working with their sales dataset, provided in a table called 'sales'. Each row in this dataset represents a transaction.
The 'sales' table has the following fields:
Write a SQL query that calculates the total revenue and the average revenue for each product per month. Total revenue is calculated as the sum of units sold times the price per unit. Average revenue is the average of total revenue for each product for that month.
The result should be displayed in three columns: 'month', 'product', 'total_revenue', 'average_revenue'.
sale_id | product_id | transaction_date | units_sold | price_per_unit |
---|---|---|---|---|
1411 | 1001 | 06/02/2022 | 5 | 200 |
2782 | 2001 | 06/10/2022 | 3 | 150 |
3293 | 1001 | 06/10/2022 | 4 | 200 |
2352 | 1001 | 07/10/2022 | 2 | 200 |
4517 | 2001 | 07/11/2022 | 7 | 150 |
month | product | total_revenue | average_revenue |
---|---|---|---|
6 | 1001 | 1800 | 900 |
6 | 2001 | 450 | 450 |
7 | 1001 | 400 | 400 |
7 | 2001 | 1050 | 1050 |
Here we use the function to calculate the total revenue for each product per month. The window function is used with the clause to calculate the average revenue for each product per month. The function is used to derive the month from the 'transaction_date' column. The output is ordered first by month, then by product ID.
p.s. Window functions show up pretty frequently during SQL interviews, so practice the 27+ window function questions on DataLemur
Normal forms are guidelines that are used to help design a relational database in a way that minimizes redundancy and ensures the integrity of the data. The 3 most commonly use normal forms are the 1st, 2nd, and 3rd normal forms. Here's a brief explanation of each:
1st Normal Form (1NF) is all about keeping it simple - each column should only have one value and there should be no repeating groups of data.
2nd Normal Form (2NF) is about organization - your database should already be in 1NF and all the non-key columns should depend on the primary key. This means that each non-key column should be completely dependent on the entire primary key, not just part of it.
3rd Normal Form (3NF) is about independence - if your database is already in 2NF, then all the non-key columns should not depend on each other. They should be self-sufficient and not rely on other non-key columns.
NCR Corporation is interested in analyzing the purchase behavior of its customers. In particular, they would like to find all customers who have purchased at least once in the last 30 days, but not any time in the last week, and have spent more than $500 in their lifetime.
purchase_id | customer_id | purchase_date | amount |
---|---|---|---|
101 | 1254 | 2022/05/15 | 100.00 |
102 | 8547 | 2022/03/22 | 50.00 |
103 | 1254 | 2022/04/03 | 200.00 |
104 | 9685 | 2022/06/11 | 150.00 |
105 | 1254 | 2021/01/02 | 250.00 |
106 | 8547 | 2022/07/27 | 75.00 |
107 | 1254 | 2022/07/27 | 300.00 |
108 | 8532 | 2022/07/28 | 400.00 |
Here's a possible PostgreSQL query for this question:
This query first filters the purchases table to include only those rows where the purchase date is between 30 and 7 days ago. It then groups these purchases by customer id, and sums up the amount spent on these purchases. The HAVING clause then filters out any customers who have spent $500 or less. This gives us a list of customers who satisfy all of the required conditions. The final result is a list of customer IDs and the total amount spent.
Here's some strategies that can generally speed up a slow SQL query:
While there's an entire industry dedicated to database performance tuning , for Data Analyst and Data Science interviews at NCR Corporation, knowing the above basics should be more than enough to ace the SQL interview! Data Engineers on the other hand should know more about the command and understand a bit more about a query-execution plan.
As a data analyst at NCR Corporation, you are tasked with finding the Average Transaction Amount in different ATMs of various cities for a given month. NCR Corporation is a leading technology and omnichannel solutions company which produces ATMs.
Below, you are provided with two tables.
The table is as follows:
transaction_id | atm_id | transaction_date | transaction_amount |
---|---|---|---|
1001 | 10 | 2021/07/01 | 200.00 |
1002 | 20 | 2021/07/01 | 150.00 |
1003 | 10 | 2021/07/02 | 250.00 |
1004 | 20 | 2021/07/02 | 300.00 |
1005 | 20 | 2021/07/03 | 200.00 |
The table looks like this:
atm_id | atm_city |
---|---|
10 | Atlanta |
20 | Columbus |
For these tables, compose a SQL query that provides a result as follows:
atm_city | avg_transaction_amount |
---|---|
Atlanta | 225.00 |
Columbus | 216.67 |
In PostgreSQL syntax, the above problem can be solved by the below query:
This SQL query joins the two tables on the column 'atm_id' and then groups the resulting table by 'atm_city'. For each group, the query calculates the average transaction amount. The WHERE clause is used to filter the transactions for a given month and year.
To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for needing to aggregate with regards to each unique item or this Stripe Repeated Payments Question which is similar for analyzing the frequency of specific transactions.
A primary key is a special type of column or set of columns in a database table that is used to identify each row in the table. It helps to ensure that all the rows are distinct and prevents null values. Additionally, primary keys play a crucial role in maintaining the accuracy of the data by making sure that there are no duplicate rows. They are also used to establish relationships between tables.
To create a primary key in a SQL database, you can use the constraint. For example, in the table , the column is the primary key.
Suppose you are a data analyst at NCR Corporation, a company that deals with software, hardware, and portfolio services for banks, restaurants, and retailers globally. You just received a task to analyze the click-through rates and conversions of digital ads for one of their products – a recently launched self-service kiosk.
To do this, you need to calculate the click-through and conversion rates for each advertisement over the past month. Click-through rate is defined as the number of clicks on the ad divided by the number of total ad impressions. Conversion rate is calculated as the number of times the product is added to the cart after viewing the ad divided by the total number of ad clicks.
Consider the tables below:
impression_id | ad_id | date | views |
---|---|---|---|
1 | 100 | 01/08/2022 | 6500 |
2 | 101 | 01/08/2022 | 4800 |
3 | 102 | 01/08/2022 | 3700 |
4 | 100 | 02/08/2022 | 5500 |
5 | 101 | 02/08/2022 | 6600 |
click_id | ad_id | date | clicks |
---|---|---|---|
1 | 100 | 01/08/2022 | 1024 |
2 | 101 | 01/08/2022 | 720 |
3 | 102 | 01/08/2022 | 555 |
4 | 100 | 02/08/2022 | 875 |
5 | 101 | 02/08/2022 | 1320 |
conversion_id | ad_id | date | conversions |
---|---|---|---|
1 | 100 | 01/08/2022 | 612 |
2 | 101 | 01/08/2022 | 360 |
3 | 102 | 01/08/2022 | 275 |
4 | 100 | 02/08/2022 | 438 |
5 | 101 | 02/08/2022 | 660 |
This query first takes the , , and tables and joins them together on the and fields. It then calculates the and for each ad on each date, based on the definitions given in the question. The results are ordered by the and the .
To solve a similar problem about calculating rates, solve this SQL interview question from TikTok within DataLemur's interactive coding environment:
NCR Corporation, a company offering software and services for Point-of-sale systems (POS), needs to understand its revenue breakdown. The sales department wants to know the total revenue generated per product.
They have a table where each row represents a transaction. Here is some sample data from their table:
transaction_id | product_id | sales_date | price | quantity |
---|---|---|---|---|
1 | 100 | 01/08/2022 | 1000 | 5 |
2 | 101 | 01/08/2022 | 200 | 3 |
3 | 100 | 02/08/2022 | 1000 | 2 |
4 | 102 | 02/08/2022 | 500 | 7 |
5 | 101 | 03/08/2022 | 200 | 1 |
The question is: Write a SQL query in PostgreSQL to find out the total revenue generated by each product.
The output should include a column for the and a column for the total revenue (). The total revenue for a product should be the sum of the multiplied by the for each transaction of that product.
The PostgreSQL query to solve the problem is as follows -
product_id | total_revenue |
---|---|
100 | 7000 |
101 | 800 |
102 | 3500 |
This query multiplies the price and quantity for each transaction to calculate the revenue from that transaction. It then sums these revenues per product to calculate the total revenue per product. The clause ensures that this calculation is applied to each product separately.
The CHECK constraint is used to enforce rules on the data in a specific column. If a row is inserted or updated with data that does not follow the CHECK constraint's rule, the operation will fail. The CHECK constraint is often used with other constraints, such as NOT NULL or UNIQUE, to ensure that data meets certain conditions. You may want to use a CHECK constraint in your database if you want to maintain the quality and reliability of your data by ensuring that it meets specific criteria.
For example, you might use a CHECK constraint to ensure that a column contains only positive numbers, or that a date is within a certain range.
NCR Corporation sells a range of tech products globally. For every sale, they record the unit sale price and the discount given. They are now interested in computing the total sales, total discounts and net sales (total_sales - total_discounts) for each product for the year 2022. The calculations should round to two decimal places. They also want to see the square root of the total net sales and the remaining balance when this result is divided by the number of records present in the database. Assume their database schema is as follows:
sales_id | product_id | sale_date | unit_price | quantity | discount |
---|---|---|---|---|---|
1 | 10 | 01/02/2022 | 500 | 4 | 50 |
2 | 20 | 02/03/2022 | 250 | 3 | 25 |
3 | 10 | 03/04/2022 | 450 | 2 | 40 |
4 | 30 | 04/05/2022 | 1000 | 1 | 100 |
5 | 20 | 05/06/2022 | 300 | 5 | 75 |
They expect the output in the following format:
product_id | total_sales | total_discount | net_sales | sqrt_net_sales | balance |
---|---|---|---|---|---|
10 | xxxx.xx | xxxx.xx | xxxx.xx | xxxx.xx | xxxx.xx |
20 | xxxx.xx | xxxx.xx | xxxx.xx | xxxx.xx | xxxx.xx |
30 | xxxx.xx | xxxx.xx | xxxx.xx | xxxx.xx | xxxx.xx |
This query first calculates the total_sales, total_discounts, and net_sales for each product in the year 2022. It then computes the square root of net_sales and the remainder when this sqrt_net_sales is divided by the total number of records in the sales table.
The 2 most similar questions from the question bank are:
Here's the required markdown:
To practice a very similar question try this interactive Wayfair Y-on-Y Growth Rate Question which is similar for calculations involving yearly sales or this Amazon Highest-Grossing Items Question which is similar for identifying products based on sales values.
The best way to prepare for a NCR Corporation SQL interview is to practice, practice, practice. In addition to solving the above NCR Corporation SQL interview questions, you should also solve the 200+ SQL coding questions which come from companies like Google, Microsoft and Silicon Valley startups.
Each interview question has multiple hints, detailed solutions and crucially, there is an interactive coding environment so you can right online code up your query and have it executed.
To prep for the NCR Corporation SQL interview you can also be helpful to solve SQL problems from other tech companies like:
But if your SQL foundations are weak, don't worry about diving straight into solving questions – improve your SQL foundations with this DataLemur SQL Tutorial.
This tutorial covers topics including math functions and filtering data with WHERE – both of these pop up frequently in SQL interviews at NCR Corporation.
In addition to SQL interview questions, the other topics tested in the NCR Corporation Data Science Interview are:
The best way to prepare for NCR Corporation Data Science interviews is by reading Ace the Data Science Interview. The book's got: