8 NEC SQL Interview Questions (Updated 2025)

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 users, products, and purchases with the following structures and data, write a PostgreSQL query that identifies these customers.

users Example Input:

products Example Input:

purchases Example Input:

Answer:

WITH customer_spending AS (
    SELECT
        u.user_id,
        u.username,
        SUM(p.price * pp.quantity) AS total_spent
    FROM
        users u
    JOIN
        purchases pp ON u.user_id = pp.user_id
    JOIN
        products p ON pp.product_id = p.product_id
    WHERE
        pp.purchase_date >= CURRENT_DATE - INTERVAL '6 months'
    GROUP BY
        u.user_id,
        u.username
)
SELECT
    cs.user_id,
    cs.username
FROM
    customer_spending cs
WHERE
    cs.total_spent > 5000;

This query first creates a subquery customer_spending that calculates the total amount each customer has spent in the last 6 months. It then selects from customer_spending the user_id and username 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 reviews table storing product reviews made by their customers. Each row records the id of the review (review_id), the id of the user who gave the review (user_id), the date the review was submitted (submit_date), the id of the product that was reviewed (product_id), and the number of stars given (stars, 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 (mth), the product id (product_id), and the average number of stars (avg_stars) for that product in that month.

reviews Example Input:

Example Output:

Answer:

SELECT 
    EXTRACT(MONTH FROM submit_date) AS mth,
    product_id,
    AVG(stars) as avg_stars
FROM 
    reviews
GROUP BY 
    mth, product_id
ORDER BY 
    mth ASC, product_id ASC;

This PostgreSQL SQL query first extracts the month from the submit_date field using the EXTRACT(MONTH FROM submit_date) function. Then for each distinct month (mth) and product_id pair, it calculates the average rating or star given to the product in the considered month by applying AVG(stars). The GROUP BY clause is used to create a separate group for each distinct mth and product_id pair, and the AVG(stars) 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 GROUP BY, and then seeing which groups have more than one occurence:

SELECT 
    x, 
    y, 
    COUNT(*) as occurrences
FROM nec_table
GROUP BY
    x, 
    y
HAVING 
    occurrences> 1;

Another way is by using the EXISTS operator:

SELECT *
FROM   nec_table t1
WHERE  EXISTS
       (
              SELECT 1
              FROM   nec_table t2
              WHERE  t1.column_name = t2.column_name
              AND    t1.id <> t2.id
       );

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 customers 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'.

customers Example Input:

orders Example Input:

Answer:

SELECT c.customer_id, c.name, c.location
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.purchase_date > current_date - interval '5 years'
  AND o.purchase_amount > 500
  AND (o.return_date < current_date - interval '3 years' OR o.return_date IS NULL)
  AND c.location IN ('Tokyo', 'Osaka')
GROUP BY c.customer_id;

The query joins customers and orders on customer_id 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 WHERE clause to apply these conditions and GROUP BY to group the results by customer_id.

SQL Question 5: What does INTERSECT 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 INTERSECT operator!

For example, let's use INTERSECT to find all of NEC's Facebook video ads that are also being run on YouTube:

SELECT ad_creative_id
FROM nec_facebook_ads
WHERE type=video

INTERSECT

SELECT ad_creative_id
FROM nec_youtube_ads

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.

digital_ads Example Input:

carts Example Input:

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:

WITH clicks AS (
  SELECT
    product_id,
    SUM(clicks) as total_clicks,
    SUM(views) as total_views
  FROM
    digital_ads
  GROUP BY
    product_id
),
adds_to_cart AS (
  SELECT
    product_id,
    COUNT(*) as total_add_to_cart
  FROM
    carts
  GROUP BY
    product_id
)

SELECT
  clicks.product_id,
  (clicks.total_clicks::float / clicks.total_views) as CTR,
  (adds_to_cart.total_add_to_cart::float / clicks.total_clicks) as conversion_rate
FROM
  clicks
LEFT JOIN
  adds_to_cart
ON
  clicks.product_id = adds_to_cart.product_id;

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 INNER JOIN between the Orders and Customers tables would return only rows where the customer_id in the Orders table matches the customer_id in the Customers table.

2. LEFT JOIN: A LEFT JOIN 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 RIGHT JOIN 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 FULL OUTER JOIN 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: sales and products. The sales table contains customer purchase information and the products table contains product information.

sales Example Input:

products Example Input:

Expected Output:

Answer:

SELECT 
    EXTRACT(month FROM sales.purchase_date) AS month,
    products.product_name,
    SUM(sales.price * sales.quantity) / COUNT(DISTINCT sales.customer_id) AS avg_revenue_per_customer
FROM 
    sales 
JOIN 
    products ON sales.product_id = products.product_id 
GROUP BY 
    month, 
    products.product_name;

This query joins the sales and products tables on the product_id field and extracts the month from purchase_date. It then groups by both the month and product_name, and calculates the average revenue by summing the products of quantity and price, dividing it by the number of distinct customer_ids using COUNT(DISTINCT customer_id).

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:

• Samsung SQL questions

• Sony SQL questions

• Hitachi SQL questions

• Panasonic SQL questions

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