8 Krafton SQL Interview Questions (Updated 2024)
At Krafton, SQL is crucial for analyzing game performance metrics and managing user data for improved gameplay experience. That's why Krafton often tests SQL problems in interviews for Data Analytics, Data Science, and Data Engineering jobs.
As such, to help you prepare for the Krafton SQL interview, we've curated 8 Krafton SQL interview questions – able to solve them?
8 Krafton SQL Interview Questions
SQL Question 1: Analyzing Player's In-game Spendings
Krafton, being a gaming company, needs to understand its player's in-game purchasing behavior to make data-driven decisions. You are given a task to analyze the average in-game spending of each player per month. For this task you need to take into account only the first purchase of each player for each day.
Assuming you have a transactions table with sample data:
Example Input:
| transaction_id | player_id | transaction_date | amount_spent |
|---|---|---|---|
| 1001 | 123 | 06/08/2022 00:00:00 | 5.0 |
| 2010 | 123 | 06/08/2022 02:00:00 | 2.5 |
| 2002 | 265 | 06/10/2022 00:00:00 | 7.0 |
| 3003 | 362 | 06/18/2022 00:00:00 | 2.0 |
| 4004 | 192 | 06/05/2022 00:00:00 | 6.0 |
| 1005 | 192 | 06/05/2022 01:00:00 | 5.0 |
You are required to write a SQL query to calculate the average daily first purchase of each player per month. The output should be sorted by month.
Example Output:
| Month | Player_id | Avg_First_Daily_Purchase |
|---|---|---|
| 5 | 192 | 6.00 |
| 6 | 123 | 3.75 |
| 6 | 265 | 7.00 |
| 6 | 362 | 2.00 |
Answer:
In the solution, we first create a subquery using a window function to get the first daily purchase of every player. Then we group by month and player_id to find the average first daily purchase for each player per month.
To solve a related window function SQL problem on DataLemur's free online SQL code editor, solve this Google SQL Interview Question:
SQL Question 2: Calculate the Click-through Conversion Rates for Krafton Digital Ads
Suppose you are a Data Analyst at Krafton, a South Korean video game company. Your marketing team is currently running multiple digital ads on various platforms, and they want to analyze the clickthrough conversion rates. Particularly, they are tracking each user's journey, from the moment they view an ad, click the ad to navigate to the product, and then add the product to their cart.
This journey is recorded in two tables: 'ad_clicks' and 'cart_additions'. The 'ad_clicks' table records every ad click activity, while the 'cart_additions' table records whenever a product is added to the cart.
Example Input:
| click_id | user_id | ad_id | click_time | product_id |
|---|---|---|---|---|
| 101 | 732 | 1 | 09/01/2021 08:00:00 | 52 |
| 102 | 654 | 2 | 09/02/2021 09:00:00 | 23 |
| 103 | 732 | 1 | 09/03/2021 10:00:00 | 52 |
| 104 | 510 | 3 | 09/04/2021 08:00:00 | 37 |
| 105 | 912 | 2 | 09/05/2021 06:00:00 | 23 |
Example Input:
| add_id | user_id | add_time | product_id |
|---|---|---|---|
| 201 | 510 | 09/04/2021 08:15:00 | 37 |
| 202 | 654 | 09/02/2021 09:20:00 | 23 |
| 203 | 732 | 09/03/2021 10:30:00 | 52 |
| 204 | 912 | 09/05/2021 07:00:00 | 23 |
| 205 | 678 | 09/01/2021 10:00:00 | 52 |
Answer:
This SQL query joins the 'ad_clicks' and 'cart_additions' tables on 'user_id' and 'product_id', and ensures that a product is added to the cart after the ad is clicked (a.click_time <= ca.add_time). It then aggregates the data by 'ad_id', and calculates the clickthrough conversion rate (the ratio of total 'cart_additions' to total 'ad_clicks') for each ad. The results are sorted by 'ad_id'.
To solve a similar problem about calculating rates, try this SQL interview question from TikTok on DataLemur's interactive SQL code editor:
SQL Question 3: What's the difference between a foreign and primary key?
A primary key is a column (or set of columns) in a table that uniquely identifies each row in the table. It cannot contain null values and must be unique across all rows in the table.
A foreign key is a column (or set of columns) in a table that references the primary key of another table. It is used to establish a relationship between the two tables. A foreign key can contain null values, and multiple rows in the referencing table can reference the same row in the referenced table.
For example, consider a database with two tables: and . The Krafton customers table might have a primary key column called , while the Krafton orders table might have a foreign key column called that references the column in the table. This establishes a relationship between the two tables, such that each row in the orders table corresponds to a specific Krafton customer.
SQL Question 4: Find the most popular game by sales per region for Krafton
Krafton, being a global video game developer, sells games across multiple regions. They want to know the most popular game i.e., the game that has sold the most number of units across each region. For this analysis, we'll need to write a SQL query that brings back the game title and the total units sold for the game that has sold the most units in each region.
Example Input:
| sale_id | game_id | sale_date | region | units_sold |
|---|---|---|---|---|
| 1 | 1001 | 06/11/2021 | Asia | 2500 |
| 2 | 1002 | 06/11/2021 | Asia | 3500 |
| 3 | 1001 | 06/12/2021 | Europe | 1500 |
| 4 | 1002 | 06/13/2021 | North America | 4000 |
| 5 | 1001 | 06/14/2021 | North America | 2000 |
Example Input:
| game_id | title |
|---|---|
| 1001 | PlayerUnknown's Battlegrounds |
| 1002 | TerraGenesis - Space Settlers |
Example Output:
| region | most_popular_game | units_sold |
|---|---|---|
| Asia | TerraGenesis - Space Settlers | 3500 |
| Europe | PlayerUnknown's Battlegrounds | 1500 |
| North America | TerraGenesis - Space Settlers | 4000 |
Answer:
This query gets the maximum number of units sold in each region from the 'sales' table, then joins it back to the 'sales' table to get the corresponding game_id and finally joins to the 'games' table to get the title of the game. The result is the most popular game and the units sold for each region.
SQL Question 5: What are the similarities and differences between a clustered and non-clustered index?
Here's an example of a clustered index on the column of a table of Krafton payments table:
This creates a clustered index on the column, which will determine the physical order of the data rows in the table.
Here is an example of a non-clustered index on the column of the same table:
This will create a non-clustered index on the column, which will not affect the physical order of the data rows in the table.
In terms of query performance, a clustered index is usually faster for searches that return a large number of records, while a non-clustered index is faster for searches that return a small number of records. However, updates to a clustered index are slower, as they require the data rows to be physically rearranged, while updates to a non-clustered index are faster, as they only require the index data structure to be updated.
SQL Question 6: Analyzing Customer Buying Habits
The company Krafton wants to understand the buying habits of their customers. They are especially interested in the amount of money customers spend on different video games and on different dates.
Two tables capture relevant sales information: table that has columns , , , , and , and the table that houses , , and columns.
Create a SQL query that will fetch the customer id, total amount spent by the customer, date of purchase, and the associated game category for each purchase.
Example Table:
| sale_id | customer_id | game_id | date_of_purchase | amount_spent |
|---|---|---|---|---|
| 1 | 8901 | 100 | 2022-06-07 | 25.00 |
| 2 | 8902 | 101 | 2022-07-23 | 20.00 |
| 3 | 8901 | 100 | 2022-07-25 | 25.00 |
| 4 | 8903 | 102 | 2022-08-14 | 30.00 |
| 5 | 8904 | 101 | 2022-08-20 | 20.00 |
Example Table:
| game_id | game_name | game_category |
|---|---|---|
| 100 | PUBG | Action |
| 101 | Tera | MMORPG |
| 102 | BTS World | Simulation |
Answer:
The query starts by joining the and tables based on the game_id. It then groups the records by customer_id, date_of_purchase, and game_category. The SUM function for amount_spent under each group helps us understand the total amount each customer spent on a certain date for a specific game category.
Because joins come up routinely during SQL interviews, take a stab at this Snapchat Join SQL question:
SQL Question 7: Can you describe the meaning of database normalization in layman's terms?
To normalize a database, tables are divided into smaller, more specialized ones and relationships between them are defined via primary and foreign keys. This minimizes redundancy, making the database more flexible, scalable, and easier to maintain. Normalization also helps to ensure the accuracy of the data by reducing the likelihood of inconsistencies and errors.
SQL Question 8: Game Statistics Calculations
Krafton, a game development company, has two tables and . The table contains information about the user including their , and . The table records each game that the player participates in, including the , , , and .
Assuming the current year is 2022, Krafton wants to find the average and rounded score for each player, the square root of each player's highest score, and the absolute difference between each player's highest and lowest score. Also, Krafton wants to calculate the player's age and list players who are teenagers (between 13 and 19 years old inclusive) and have played at least one game in the last year.
Example Input:
| player_id | birth_year | registration_date |
|---|---|---|
| 1 | 2005 | 2018-01-01 |
| 2 | 2010 | 2019-06-15 |
| 3 | 2008 | 2018-05-20 |
| 4 | 2007 | 2017-09-30 |
| 5 | 2000 | 2018-01-01 |
Example Input:
| game_id | player_id | date_played | score |
|---|---|---|---|
| 101 | 1 | 2022-08-01 | 200 |
| 102 | 1 | 2022-08-02 | 350 |
| 103 | 1 | 2022-08-03 | 150 |
| 104 | 2 | 2022-08-01 | 100 |
| 105 | 3 | 2022-08-01 | 300 |
| 106 | 4 | 2022-08-02 | 200 |
| 107 | 5 | 2022-08-03 | 150 |
Answer:
This query joins and based on , groups the results by player, and calculates the average score (rounded to the nearest integer), the square root of the max score, and the absolute difference between the max and min scores. The clause filters the players who are ages 13 to 19 inclusive, and played at least one game in 2022.
Preparing For The Krafton SQL Interview
The key to acing a Krafton SQL interview is to practice, practice, and then practice some more!
Beyond just solving the above Krafton SQL interview questions, you should also solve the 200+ SQL questions on DataLemur which come from companies like FAANG tech companies and tech startups.
Each interview question has hints to guide you, detailed solutions and best of all, there is an online SQL coding environment so you can easily right in the browser your query and have it executed.
To prep for the Krafton SQL interview you can also be helpful to solve SQL problems from other tech companies like:
In case your SQL query skills are weak, don't worry about jumping right into solving questions – refresh your SQL knowledge with this SQL tutorial for Data Analytics.
This tutorial covers SQL concepts such as joining multiple tables and transforming strings with CONCAT()/LOWER()/TRIM() – both of these come up often during Krafton interviews.
Krafton Data Science Interview Tips
What Do Krafton Data Science Interviews Cover?
In addition to SQL query questions, the other types of problems to prepare for the Krafton Data Science Interview are:
- Stats Interview Questions
- Coding Questions in Python or R
- Product-Sense and Business Case Questions
- Machine Learning and Predictive Modeling Questions
- Behavioral Based Interview Questions
How To Prepare for Krafton Data Science Interviews?
The best way to prepare for Krafton Data Science interviews is by reading Ace the Data Science Interview. The book's got:
- 201 Interview Questions from FAANG, tech startups, and Wall Street
- A Refresher covering Product Analytics, SQL & ML
- Great Reviews (900+ reviews, 4.5-star rating)