8 NEC SQL Interview Questions (Updated 2024)

Updated on

March 1, 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.

1Jim
2Michael
3Pam
4Dwight
Example Input:
product_idproduct_nameprice
1NEC Monitor\$800
2NEC Laptop\$1200
3NEC Phone\$600
4NEC TV\$1500
Example Input:
purchase_iduser_idproduct_idquantitypurchase_date
1111003/15/2022
222304/20/2022
333604/30/2022
444205/15/2022
512206/10/2022
623507/10/2022
734108/18/2022
841108/30/2022

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_iduser_idsubmit_dateproduct_idstars
61711232018-06-08500014
78022652018-06-10698524
52933622018-06-18500013
63521922018-07-26698523
45179812018-07-05698522
Example Output:
mthproductavg_stars
6500013.50
6698524.00
7698522.50

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_idnamelocation
1001ShinjiTokyo
1002AsukaYokohama
1003ReiOsaka
1004KaworuTokyo

Example Input:

order_idcustomer_idpurchase_amountpurchase_datereturn_date
500110016002018-06-05null
500210024002019-08-072019-08-09
500310037002020-12-15null
500410013002021-09-19null
500510048002022-03-01null

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.

001p15000250
002p24000300
003p17000350
004p35500275
005p26000450
Example Input:
cart_idproduct_idclick_id
101p1210
102p2230
103p3250
104p2410
105p1220

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.

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.

1. 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.

2. 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.

3. 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.

4. 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_idcustomer_idpurchase_dateproduct_idquantityprice
4567984509/01/2022 00:00:0089001335
1239847509/10/2022 00:00:00340022150
9215682409/15/2022 00:00:0089001135
3417984510/20/2022 00:00:00340024150
7213847510/25/2022 00:00:0089001535
Example Input:
product_idproduct_name
89001ProductA
34002ProductB
Expected Output:
monthproduct_nameavg_revenue_per_customer
09ProductA105.00
09ProductB300.00
10ProductA175.00
10ProductB600.00

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:

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