logo

8 Krafton SQL Interview Questions (Updated 2024)

Updated on

March 2, 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_idplayer_idtransaction_dateamount_spent
100112306/08/2022 00:00:005.0
201012306/08/2022 02:00:002.5
200226506/10/2022 00:00:007.0
300336206/18/2022 00:00:002.0
400419206/05/2022 00:00:006.0
100519206/05/2022 01:00:005.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:

MonthPlayer_idAvg_First_Daily_Purchase
51926.00
61233.75
62657.00
63622.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: 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_iduser_idad_idclick_timeproduct_id
101732109/01/2021 08:00:0052
102654209/02/2021 09:00:0023
103732109/03/2021 10:00:0052
104510309/04/2021 08:00:0037
105912209/05/2021 06:00:0023
Example Input:
add_iduser_idadd_timeproduct_id
20151009/04/2021 08:15:0037
20265409/02/2021 09:20:0023
20373209/03/2021 10:30:0052
20491209/05/2021 07:00:0023
20567809/01/2021 10:00:0052

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: Signup Activation Rate SQL Question

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.

Krafton SQL Interview Questions

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_idgame_idsale_dateregionunits_sold
1100106/11/2021Asia2500
2100206/11/2021Asia3500
3100106/12/2021Europe1500
4100206/13/2021North America4000
5100106/14/2021North America2000
Example Input:
game_idtitle
1001PlayerUnknown's Battlegrounds
1002TerraGenesis - Space Settlers
Example Output:
regionmost_popular_gameunits_sold
AsiaTerraGenesis - Space Settlers3500
EuropePlayerUnknown's Battlegrounds1500
North AmericaTerraGenesis - Space Settlers4000

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_idcustomer_idgame_iddate_of_purchaseamount_spent
189011002022-06-0725.00
289021012022-07-2320.00
389011002022-07-2525.00
489031022022-08-1430.00
589041012022-08-2020.00

Example Table:

game_idgame_namegame_category
100PUBGAction
101TeraMMORPG
102BTS WorldSimulation

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: 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_idbirth_yearregistration_date
120052018-01-01
220102019-06-15
320082018-05-20
420072017-09-30
520002018-01-01
Example Input:
game_idplayer_iddate_playedscore
10112022-08-01200
10212022-08-02350
10312022-08-03150
10422022-08-01100
10532022-08-01300
10642022-08-02200
10752022-08-03150

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. DataLemur Questions

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.

DataLemur SQL Tutorial for Data Science

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:

Krafton Data Scientist

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)

Ace the DS Interview