8 NEC SQL Interview Questions (Updated 2024)
At NEC Corp, SQL is used often for analyzing customer data to enhance telecommunications solutions, and managing databases for secure information storage in their cybersecurity division. That's why NEC almost always evaluates jobseekers on SQL questions in interviews for Data Science and Data Engineering positions.
As such, to help you ace the NEC SQL interview, we've collected 8 NEC Corp SQL interview questions – how many can you solve?
8 NEC Corp SQL Interview Questions
SQL Question 1: Identify VIP Customers
NEC wants to identify their VIP customers, classified as users who have purchased more than $5,000 worth of products in the last 6 months. Given a database with tables , , and with the following structures and data, write a PostgreSQL query that identifies these customers.
Example Input:
| user_id | username |
|---|---|
| 1 | Jim |
| 2 | Michael |
| 3 | Pam |
| 4 | Dwight |
Example Input:
| product_id | product_name | price |
|---|---|---|
| 1 | NEC Monitor | $800 |
| 2 | NEC Laptop | $1200 |
| 3 | NEC Phone | $600 |
| 4 | NEC TV | $1500 |
Example Input:
| purchase_id | user_id | product_id | quantity | purchase_date |
|---|---|---|---|---|
| 1 | 1 | 1 | 10 | 03/15/2022 |
| 2 | 2 | 2 | 3 | 04/20/2022 |
| 3 | 3 | 3 | 6 | 04/30/2022 |
| 4 | 4 | 4 | 2 | 05/15/2022 |
| 5 | 1 | 2 | 2 | 06/10/2022 |
| 6 | 2 | 3 | 5 | 07/10/2022 |
| 7 | 3 | 4 | 1 | 08/18/2022 |
| 8 | 4 | 1 | 1 | 08/30/2022 |
Answer:
This query first creates a subquery that calculates the total amount each customer has spent in the last 6 months. It then selects from the and of all customers who spent over $5,000 in this time period. These users are the VIP customers for NEC.
To practice a similar VIP customer analysis question on DataLemur's free interactive coding environment, try this Microsoft Azure Cloud SQL Interview Question:
SQL Question 2: Calculate Average Product Ratings per Month
Consider NEC, a company that produces and sells various electronics. They have a table storing product reviews made by their customers. Each row records the id of the review (), the id of the user who gave the review (), the date the review was submitted (), the id of the product that was reviewed (), and the number of stars given (, on a scale of 1-5 ).
Write a SQL query to calculate the average product rating for each product per month. The output should have a row per product per month, with columns for the month (), the product id (), and the average number of stars () for that product in that month.
Example Input:
| review_id | user_id | submit_date | product_id | stars |
|---|---|---|---|---|
| 6171 | 123 | 2018-06-08 | 50001 | 4 |
| 7802 | 265 | 2018-06-10 | 69852 | 4 |
| 5293 | 362 | 2018-06-18 | 50001 | 3 |
| 6352 | 192 | 2018-07-26 | 69852 | 3 |
| 4517 | 981 | 2018-07-05 | 69852 | 2 |
Example Output:
| mth | product | avg_stars |
|---|---|---|
| 6 | 50001 | 3.50 |
| 6 | 69852 | 4.00 |
| 7 | 69852 | 2.50 |
Answer:
This PostgreSQL SQL query first extracts the month from the field using the function. Then for each distinct month () and pair, it calculates the average rating or star given to the product in the considered month by applying . The clause is used to create a separate group for each distinct and pair, and the function is then applied to each group. The results are ordered in ascending order of the month and the product_id.
p.s. Window functions show up super frequently during SQL interviews, so practice the 27+ window function questions on DataLemur
SQL Question 3: List a few of the ways you find duplicate records in a table in SQL.
One way to find duplicate records in a table is by using , and then seeing which groups have more than one occurence:
Another way is by using the operator:
SQL Question 4: Filter Customers Based on Conditions
NEC, being a major IT, hardware, and software provider, deals with a vast number of customers globally. A main endeavor for NEC has been to filter their customer database to identify prospective customers for their specific product launches. The task is to write a SQL query to filter down the records database to comprise of only the customers who have made purchases over $500 at least once in the last 5 years, have not returned any item in the last 3 years and are located in either 'Tokyo' or 'Osaka'.
Example Input:
| customer_id | name | location |
|---|---|---|
| 1001 | Shinji | Tokyo |
| 1002 | Asuka | Yokohama |
| 1003 | Rei | Osaka |
| 1004 | Kaworu | Tokyo |
Example Input:
| order_id | customer_id | purchase_amount | purchase_date | return_date |
|---|---|---|---|---|
| 5001 | 1001 | 600 | 2018-06-05 | null |
| 5002 | 1002 | 400 | 2019-08-07 | 2019-08-09 |
| 5003 | 1003 | 700 | 2020-12-15 | null |
| 5004 | 1001 | 300 | 2021-09-19 | null |
| 5005 | 1004 | 800 | 2022-03-01 | null |
Answer:
The query joins and on and filters to include only the customers who meet the three conditions: (a) in the last 5 years, they made at least one purchase worth more than $500, (b) they have not make any returns in the last 3 years, and (c) they are situated in either 'Tokyo' or 'Osaka'. We use the clause to apply these conditions and to group the results by .
SQL Question 5: What does do, and when would you use this SQL command?
If you have two tables and want to retrieve only the rows that are present in both tables, just use the operator!
For example, let's use to find all of NEC's Facebook video ads that are also being run on YouTube:
SQL Question 6: Analysis of NEC Digital Ads Click-Through-Rates
NEC company has been doing a series of online digital ads campaigns with the aim of driving more people to view and ultimately purchase their products. Each digital ad contains a link, which leads to a product page on the NEC website. NEC is currently interested in understanding the Click-Through-Rate (CTR) of these digital ads i.e., what proportion of ad views lead to link click, and furthermore how many clicks move forward to add the product to a cart for eventual purchase.
Example Input:
| ad_id | product_id | views | clicks |
|---|---|---|---|
| 001 | p1 | 5000 | 250 |
| 002 | p2 | 4000 | 300 |
| 003 | p1 | 7000 | 350 |
| 004 | p3 | 5500 | 275 |
| 005 | p2 | 6000 | 450 |
Example Input:
| cart_id | product_id | click_id |
|---|---|---|
| 101 | p1 | 210 |
| 102 | p2 | 230 |
| 103 | p3 | 250 |
| 104 | p2 | 410 |
| 105 | p1 | 220 |
This data contains two tables: "digital_ads" that records the digital ad activities, and "carts" that records when a product is added to a cart following a digital ad click.
The task is to write a PostgreSQL query that calculates:
- the Click-Through-Rate (CTR) for each product, defined as total clicks / total views,
- the conversion rate for each product, defined as total add-to-carts / total clicks.
Answer:
This query first sums up the total views and clicks for each product in a table "clicks", then counts the total number of add-to-carts for each product in another table "adds_to_cart". It then joins these two tables on 'product_id' and calculates the CTR and the conversion rate for each product from the sums and counts. Please note that we use a LEFT JOIN to include products that may have clicks but no add-to-carts.
To solve a similar SQL interview question on DataLemur's free online SQL code editor, try this SQL interview question asked by Facebook:
SQL Question 7: What are the various types of joins used in SQL?
In SQL, a join retrieves rows from multiple tables and combines them into a single result set.
Four JOIN types can be found in SQL. For an example of each one, suppose you had a table of NEC orders and NEC customers.
-
INNER JOIN: Rows from both tables are retrieved when there is a match in the shared key or keys. An between the Orders and Customers tables would return only rows where the in the Orders table matches the in the Customers table.
-
LEFT JOIN: A retrieves all rows from the left table (in this case, the Orders table) and any matching rows from the right table (the Customers table). If there is no match in the right table, NULL values will be returned for the right table's columns.
-
RIGHT JOIN: A combines all rows from the right table (in this case, the Customers table) and any matching rows from the left table (the Orders table). If there is no match in the left table, NULL values will be displayed for the left table's columns.
-
FULL OUTER JOIN: A combines 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 displayed for the columns of the non-matching table.
SQL Question 8: Average Revenue Per Customer for Each Product
As an NEC analyst, you're tasked with calculating the average revenue from each customer for every product for better resource allocation. You have a data source with two tables: and . The table contains customer purchase information and the table contains product information.
Example Input:
| sales_id | customer_id | purchase_date | product_id | quantity | price |
|---|---|---|---|---|---|
| 4567 | 9845 | 09/01/2022 00:00:00 | 89001 | 3 | 35 |
| 1239 | 8475 | 09/10/2022 00:00:00 | 34002 | 2 | 150 |
| 9215 | 6824 | 09/15/2022 00:00:00 | 89001 | 1 | 35 |
| 3417 | 9845 | 10/20/2022 00:00:00 | 34002 | 4 | 150 |
| 7213 | 8475 | 10/25/2022 00:00:00 | 89001 | 5 | 35 |
Example Input:
| product_id | product_name |
|---|---|
| 89001 | ProductA |
| 34002 | ProductB |
Expected Output:
| month | product_name | avg_revenue_per_customer |
|---|---|---|
| 09 | ProductA | 105.00 |
| 09 | ProductB | 300.00 |
| 10 | ProductA | 175.00 |
| 10 | ProductB | 600.00 |
Answer:
This query joins the and tables on the field and extracts the month from . It then groups by both the month and , and calculates the average revenue by summing the products of and , dividing it by the number of distinct s using .
NEC SQL Interview Tips
The key to acing a NEC SQL interview is to practice, practice, and then practice some more!
In addition to solving the above NEC SQL interview questions, you should also solve the 200+ SQL questions on DataLemur which come from companies like Microsoft, Google, Amazon, and tech startups.
Each DataLemur SQL question has hints to guide you, step-by-step solutions and crucially, there's an online SQL coding environment so you can right online code up your query and have it checked.
To prep for the NEC SQL interview it is also useful to practice interview questions from other tech companies like:
But if your SQL foundations are weak, forget about diving straight into solving questions – improve your SQL foundations with this free SQL tutorial.
This tutorial covers things like joins and filtering data with WHERE – both of these show up frequently during SQL job interviews at NEC.
NEC Corp Data Science Interview Tips
What Do NEC Data Science Interviews Cover?
Beyond writing SQL queries, the other types of problems to practice for the NEC Data Science Interview are:
- Probability & Stats Questions
- Python or R Coding Questions
- Product Data Science Interview Questions
- Machine Learning Questions
- Resume-Based Behavioral Questions
How To Prepare for NEC Data Science Interviews?
To prepare for NEC Data Science interviews read the book Ace the Data Science Interview because it's got:
- 201 interview questions taken from FAANG (FB, Apple, Amazon, Netflix, Google)
- a refresher on SQL, AB Testing & ML
- over 900+ reviews on Amazon & 4.5-star rating