# 10 Nintendo SQL Interview Questions (Updated 2024)

Updated on

February 14, 2024

At Nintendo, SQL is used often for analyzing user gameplay data for insights on player behavior and managing transactional database systems for accurate digital and physical product sales tracking. That's why Nintendo frequently asks SQL problems during interviews for Data Analytics, Data Science, and Data Engineering jobs.

As such, to help you practice for the Nintendo SQL interview, we've curated 10 Nintendo SQL interview questions – can you solve them?

## 10 Nintendo SQL Interview Questions

### SQL Question 1: Monthly Average Review Score for Each Game

You work as a data analyst at Nintendo. Recently, your manager asked you to analyze the monthly average review score for each game sold by the company. The dataset available to you consists of a table named "reviews". The "reviews" table has 5 columns: "review_id" (an unique identifier for each review), "user_id" (the ID of the user who wrote the review), "submit_date" (the date the review was submitted), "product_id" (the ID of the product being reviewed), "stars" (the number of stars the product was given, ranging from 1 to 5).

You need to aggregate this data to find the monthly average review score for each product_id for the year 2022. The score should be rounded to two decimal places.

##### Example Input:
review_iduser_idsubmit_dateproduct_idstars
61711232022-06-08500014
78022652022-06-10698524
52933622022-06-18500013
63521922022-07-26698523
45179812022-07-05698522
##### Expected Output:
monthproduct_idavg_stars
6500013.50
6698524.00
7698522.50

In this SQL query, we are utilizing PostgreSQL's EXTRACT function to grab the MONTH and YEAR from the column. We are calculating the average using AVG function and rounding it to two decimal places. We GROUP BY the derived and to get the average score per month for each product. The resulting data is sorted by and .

p.s. Window functions show up super often during SQL interviews, so practice the 27+ window function questions on DataLemur

### SQL Question 2: Nintendo Games Sales Analysis

Given a database that contains information on the sales of games developed by Nintendo, the database comprises of three tables:

1. A table that contains information on each game (game_id, game_name, release_date).
2. A table that captures the sales of each game (record_id, game_id, units_sold, sale_date, region).
3. A table that indicates demographic info of each region (region_id, region_name, population).

Design the SQL query to:

• Identify the top 5 selling games for the year 2020.
• Break down sales by game and region for the top 5 games.

Sample database:

##### Example Input:
game_idgame_namerelease_date
001Super Mario Odyssey10/27/2017
002The Legend of Zelda: Breath of the Wild03/03/2017
003Pokemon Let's Go11/16/2018
004Luigi's Mansion 310/31/2019
005Animal Crossing: New Horizons03/20/2020
##### Example Input:
record_idgame_idunits_soldsale_dateregion_id
10010015000001/03/202001
20020027500002/10/202002
300300312000004/10/202002
40040048500006/20/202003
500500526500003/30/202001
##### Example Input:
region_idregion_namepopulation
01North America579000000
02Europe747600000
03Asia4601375000

This set of answers helps to understand the performance of Nintendo games sold in 2020. The first query returns the top 5 selling games in 2020. The second gives a breakdown by region for each of these top 5 games.

### SQL Question 3: What does do, and when would you use this function?

The COALESCE() function returns the first non-NULL value from a list of values. This function is often used to replace a NULL with some default value, so that you can then take a or of some column without NULLs messing things up.

For example, suppose you ran a customer satisfaction survey for Nintendo and had statements like "I'd buy from Nintendo again". In the survey, customers would then answer how strongly they agreed with a statement on a scale of 1 to 5 (strongly disagree, disagree, neutral, agreee, strongly agree).

Because attention spans are short, many customers skipped many of the questions, and thus our survey data might be filled with NULLs:

customer_idquestion_idagree_scale
10114
10125
20214
2022NULL
30315
3032NULL

Before doing further analytics on this customer survey data, you could replace the NULLs in the column with the value of (because that corresponds to the default 'neutral' answer) using the function:

This would result in the following:

customer_idquestion_idagree_scale
10114
10125
20214
20223
30315
30323

### SQL Question 4: Filter Customer Records for Nintendo Game Users

Nintendo needs to filter down their customer records to obtain a list of users who have purchased their products, specifically gamers who play "Super Mario" and "Legend of Zelda". Additionally, they want to obtain the total spend of these gamers based on all their purchases.

For this exercise, let's suppose we have two tables – and . The table contains information about each purchase including user id, product name, and the cost, and the table stores information about each user such as user id and user name.

##### Example Input
user_idproduct_namecost
1001Super Mario59.99
1023Legend of Zelda59.99
1001Animal Crossing59.99
1045Super Mario59.99
1001Legend of Zelda59.99
##### Example Input:
user_iduser_name
1001John Doe
1023Jane Doe
1045Harry Potter

We want to filter down this data to a list of users who bought "Super Mario" or "Legend of Zelda" and the summation of their total purchases.

The expected output should resemble:

##### Example Output:
user_nametotal_spent
John Doe179.97
Jane Doe59.99
Harry Potter59.99

We could extract the desired information using a combination of SQL , , and :

This query joins the purchases and customer tables on user id, filters data to include only purchases of "Super Mario" or "Legend of Zelda", then group results by user name. The function calculates the total amount spent by each user.

### SQL Question 5: What do foreign key's do?

A foreign key is a field in a database table that links to the primary key of another table, establishing a connection between the two tables. For example, let's analyze Nintendo's Google Ads campaigns data:

:

+------------+------------+------------+------------+ | ad_id | campaign_id| keyword | click_count| +------------+------------+------------+------------+ | 1 | 201 | Nintendo reviews | 120 | | 2 | 202 | Nintendo pricing | 150 | | 3 | 101 | buy Nintendo | 65 | | 4 | 101 | Nintendo alternatives | 135 | +------------+------------+------------+------------+

is a foreign key that connects to the of the corresponding Google Ads campaign. This establishes a relationship between the ads and their campaigns, enabling easy querying to find which ads belong to a specific campaign or which campaigns a specific ad belongs to.

The table may also have multiple foreign keys that reference primary keys in other tables. For example, and foreign keys could be used to link each ad to its ad group and the Google Ads account that the campaigns belong to, respectively.

### SQL Question 6: Calculate Click-Through-Rate for Nintendo Game Ads

Nintendo, a renowned video game company, releases digital ads for its video games. For each of these ads, Nintendo tracks the number of times an ad was served (impressions) and the number of times it was clicked (clicks). They want to calculate the Click-Through-Rate (CTR) for each game ad, which is the ratio of clicks to impressions.

Calculate the CTR for each game ad for the month of August.

##### Example Input:
101300108/01/2022 00:00:005000450
102300208/02/2022 00:00:004500300
103300108/03/2022 00:00:005200500
104300308/04/2022 00:00:003000250
105300208/05/2022 00:00:004800350
##### Example Output:
game_idctr
30010.095522388
30020.069444444
30030.083333333

This query first filters the rows where the is in August. Then, for each game (indicated by ), it sums up the total and total and calculates the ratio between them, which represents the CTR. This calculation is done within each group of rows that share the same , as indicated by the clause. The makes sure the division is not integer division.

To solve a related SQL problem on DataLemur's free online SQL code editor, try this Facebook SQL Interview question:

### SQL Question 7: What's a database view?

Database views are virtual tables based on the results of a SQL statement. They're just like vanilla tables, except views allow you to create simplified versions of tables or hide sensitive data from certain users.

In PostgreSQL, you can create a view by using the command. Here's an example for the table:

### SQL Question 8: Customer Records Filter with Pattern Matching

As a Data Analyst for Nintendo, you have been tasked with analyzing the customer database . Specifically, you are requested to find all of the active customers from the United States that are Nintendo Switch users. Customers are considered active if their field is set to . We know that the info field contains the main console used by each customer and for Nintendo Switch users it would contain the string .

Use the SQL keyword to perform your data filter and return the , , and fields.

##### Example Input:
customer_idnamestatuscountryinfo
1001John DoeactiveUnited StatesSwitch user since 2018
1002Jane SmithinactiveUnited StatesSwitch user since 2019
1004Patricia WilliamsactiveUnited StatesGamecube user
1005Robert JonesactiveUnited StatesSwitch user since 2020

This query selects the relevant fields from the table where the field is , the field is , and the field contains the string . The operator is a wildcard that matches any sequence of characters, meaning it will return a match even if there are other characters before or after in the field.

### SQL Question 9: Calculate Discounted Product Prices

Given a table named that stores data about Nintendo's game plays, with columns , , , and ; and another table named having columns , , and . Nintendo decides to offer a discount to users on games based on their play time. The discount is calculated as such: if a user's total play time is more than X hours, the discount for the user is SQRT(X) % of the original game price. Write a PostgreSQL query to calculate the final price for each user for each game.

##### Example Input:
session_iduser_idgame_idplay_time
2015410015.2
2025410023.6
2037810038.1
2049210034.5
2059210026.5
##### Example Input:
game_idgame_nameoriginal_price
1001Super Mario Odyssey59.99
1002The Legend of Zelda: Breath of the Wild69.99
1003Animal Crossing: New Horizons59.99

This query first joins the and table on the column. It then groups by , and to calculate the sum of of a user for each game. The discount is calculated by taking the square root of the sum of , and then the final price is calculated by subtracting the percentage discount from the original price of each game for each user. The final price is rounded to the nearest 2nd decimal place (cents).

To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for calculating based on quantities or this Stripe Repeated Payments Question which is similar for dealing with repeated transactions.

### SQL Question 10: Can you explain the distinction between cross join and natural join?

A cross join is a type of JOIN operation in SQL that creates a new table by combining each row from the first table with every row from the second table. It is also known as a cartesian join.

For example, say you worked on the Marketing Analytics team at Nintendo, and needed to understand what advertising copy (text) goes best with what advertising creative (the photo that gets posted with the advertisement copy).

Here is an example of a cross-join between a table of ad_copy and a table of ad_creative:

A natural join, on the other hand, is a type of JOIN that combines rows from two or more tables based on their common columns. It is called a "natural" join because it is based on the natural relationship that exists between the common columns in the tables being joined.

For an example of each one, say you had sales data exported from Nintendo's Salesforce CRM stored in a datawarehouse which had two tables: and .

An (which is a type of natural join) combines the two tables on the common

This query will return rows from the sales and tables that have matching customer id values. Only rows with matching values will be included in the results.

One main difference between cross joins and natural joins is that cross joins do not require any common columns between the tables being joined, while natural joins do. Another difference is that cross joins can create very large tables if the input tables have a large number of rows, while natural joins will only return a table with the number of rows equal to the number of matching rows in the input tables.

### Nintendo SQL Interview Tips

The key to acing a Nintendo SQL interview is to practice, practice, and then practice some more! In addition to solving the above Nintendo SQL interview questions, you should also solve the 200+ SQL questions on DataLemur which come from companies like Amazon, Microsoft, Meta, and smaller tech companies.

Each problem on DataLemur has multiple hints, step-by-step solutions and best of all, there's an interactive SQL code editor so you can easily right in the browser your query and have it graded.

To prep for the Nintendo SQL interview you can also be useful to practice SQL problems from other tech companies like:

In case your SQL foundations are weak, don't worry about diving straight into solving questions – strengthen your SQL foundations with this interactive SQL tutorial.

This tutorial covers SQL topics like finding NULLs and WHERE vs. HAVING – both of which show up frequently during Nintendo SQL assessments.

### Nintendo Data Science Interview Tips

#### What Do Nintendo Data Science Interviews Cover?

Besides SQL interview questions, the other types of problems to prepare for the Nintendo Data Science Interview are:

#### How To Prepare for Nintendo Data Science Interviews?

To prepare for Nintendo Data Science interviews read the book Ace the Data Science Interview because it's got:

• 201 interview questions taken from FAANG & startups
• a refresher covering Stats, ML, & Data Case Studies
• over 900+ reviews on Amazon & 4.5-star rating