9 Kaspi kz SQL Interview Questions (Updated 2024)

Updated on

February 29, 2024

At Kaspi.kz, SQL is used often for extracting and analyzing customer transaction data, and managing the company's financial database for more efficient decision-making. Unsurprisingly this is why Kaspi kz asks SQL questions in interviews for Data Analytics, Data Science, and Data Engineering jobs.

To help you ace the Kaspi kz SQL interview, this blog covers 9 Kaspi kz SQL interview questions – can you solve them?

9 Kaspi.kz SQL Interview Questions

SQL Question 1: Identify VIP Users at Kaspi

As a data analyst at Kaspi kz, you're tasked with identifying key users of our platform. We define "VIP Users" as those who've made more than 400 transactions in the last month. Write an SQL query to extract a list of VIP Users.

We've provided the following table, , for your analysis.

Example Input:
transaction_iduser_idtransaction_dateamount
617112306/08/2022 00:00:005000.00
780226506/10/2022 00:00:006985.00
529312306/18/2022 00:00:005000.00
635219207/26/2022 00:00:006985.00
451712307/05/2022 00:00:006985.00

Answer:


This SQL query identifies VIP users by checking the total number of transactions each user has made in the past month. If the count exceeds 400, the user id is returned as a VIP user. The use of the clause specifies this condition after the clause groups the transactions by user_id.

To solve a related customer analytics question on DataLemur's free online SQL code editor, try this Microsoft SQL Interview problem: Microsoft SQL Interview Question: Super Cloud Customer

SQL Question 2: Find the Monthly Average Purchase Amount per Customer

Kaspi.kz is a large e-commerce platform in Kazakhstan. We are interested in analyzing our customer's behavior to understand the structure of our revenue. Write a SQL query that would return the average amount of purchase per customer for each month.

Example Input:
sales_idcustomer_iddateprice_kz
112301/01/20221000
212301/15/20222000
326501/20/20224500
436202/05/20227000
519203/01/20223000
698102/28/20221000
719203/15/20225000
836202/25/20223000
Example Output:
monthcustomer_idavg_purchase_kz
01/20221231500.00
01/20222654500.00
02/20223625000.00
02/20229811000.00
03/20221924000.00

Answer:

Here is a SQL query that uses the AVG() window function to find the average purchase per customer for each month:


This query will return the month, customer_id, and the average amount of purchase for each customer for each month. The 'PARTITION BY' clause in the OVER clause of the AVG window function will divide the data into partitions or groups. This function will be applied to each partition separately and reinitialized for each partition.

This query will help us understand how average spending changes for each customer each month and if there are any trends in our revenue over time.

To practice a related window function SQL problem on DataLemur's free interactive SQL code editor, try this Amazon BI Engineer interview question: Amazon SQL Interview Question

SQL Question 3: Why is database normalization a good idea?

Normalization can reduce redundancy and improve performance and flexibility in a database.

By dividing larger tables into smaller, more specific ones linked by foreign keys, it is often possible to improve query speed. Note: it is not always the case that query speed will be improved due to costly nature of joins. Still, we think normalization is a good idea because it can also increase flexibility by making it easier to alter individual tables without affecting others, which can be useful for adapting Kaspi kz's database to ever-changing business needs.

Kaspi kz SQL Interview Questions

SQL Question 4: Retail Business Analytics

Kaspi kz is a large retail organization and they are interested in understanding the shopping habits of their customers more closely. They want to track their customers' purchases and they are interested in finding the top 5 customers who made the most purchases in the past month, the total amount of money spent, and the number of unique products they purchased. How would you model the customer, product and orders tables to solve this problem?

Here's the sample tables:

Example Input:
customer_idnameemail
101John DoeJohn.Doe@gmail.com
102Jane SmithJane.Smith@gmail.com
103David JohnsonDavid.Johnson@gmail.com
104Susan WilliamsSusan.Williams@gmail.com
105Michael BrownMichael.Brown@gmail.com
Example Input:
product_idproduct_nameprice
501iPhone 12$699
502Macbook Pro$1299
503iWatch$399
504iPod$199
505iPad$799
Example Input:
order_idcustomer_idproduct_idquantityorder_date
2010110250112022-11-01
2010210550212022-11-01
2010310150322022-11-02
2010410450432022-11-03
2010510350512022-11-03

Answer:


The SQL query provided retrieves each customer's ID and name, counts the number of unique products they've ordered from the last month, and sums the total they've spent for the last month. The customers are then sorted by their total spent in a descending order, which means we'll get the customers who spent the most at the top. Finally, we limit the output to the top 5 to meet the company's requirements.

SQL Question 5: Could you describe a self-join and provide a scenario in which it would be used?

A self-join is a type of JOIN where a table is joined to itself. To execute a self-join, you must include the table name twice in the FROM clause and assign a different alias to each instance. You can then join the two copies of the table using a JOIN clause, and use a WHERE clause to specify the relationship between the rows.

For instance, suppose you had website visitor data for Kaspi kz, exported from the company's Google Analytics account. To assist the web-dev team, you needed to analyze pairs of pages for UX or navigational issues. As part of that analysis, you wanted to generate all pairs of URLs, but needed to exclude pairs where both URLs were the same since that is not a valid pair."

You could use the following self-join:


This query returns the url of each page () along with the url of the page that referred to it (). The self-join is performed using the field, which specifies the id of the page that referred the visitor to the current page, and avoids any pages that referred themself (aka data anomalies).

Sure, here is an example of a question for Kaspi.kz in a SQL interview focusing on click-through rates:

SQL Question 6: Compute the Click-Through-Rates

As an analyst at Kaspi.kz, a leading fintech company in Kazakhstan, you're tasked with understanding the click-through rates(CTR) of ads for your digital products. Your digital product team wants to understand the rate of success for users to move from viewing a product's details to adding the product to their cart.

Given two tables - 'product_views' and 'product_add_to_cart', can you calculate the click-through rate (CTR: the number of users who add a product to their cart after viewing it) for each product ?

Example Input:
view_iduser_idproduct_idview_date
311123106/08/2022 00:00:00
785734206/10/2022 00:00:00
679123106/18/2022 00:00:00
1013490307/26/2022 00:00:00
670314107/05/2022 00:00:00
Example Input:
add_iduser_idproduct_idadd_date
2910123106/08/2022 00:00:00
6809734206/11/2022 00:00:00
6421314107/06/2022 00:00:00
Expected Output:
product_idCTR
166.67%
2100%
30%

Answer:


This query joins the 'product_views' and 'product_add_to_cart' tables on the user_id and product_id. The counts of users who viewed and added each product to the cart are then used to calculate the CTR for each product. The ROUND function is used to limit the CTR to two decimal places and display it as a percentage.

To solve a related SQL interview question on DataLemur's free online SQL code editor, attempt this Meta SQL interview question: Meta SQL interview question

SQL Question 7: What are SQL constraints, and can you give some examples?

Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during the creation of the table or after creating using the ALTER TABLE command. The constraints are:

Say you were storing sales analytyics data from Kaspi kz's CRM inside a database. Here's some example constraints you could use:

  • PRIMARY KEY constraint: You might use a PRIMARY KEY constraint to ensure that each record in the database has a unique identifier. For example, you could use the "opportunity_id" field as the primary key in the "opportunities" table.

  • FOREIGN KEY constraint: You might use a FOREIGN KEY constraint to link the data in one table to the data in another table. For example, you could use a foreign key field in the "opportunities" table to reference the "account_id" field in the "accounts" table.

  • NOT NULL constraint: You might use a NOT NULL constraint to ensure that a field cannot contain a NULL value. For example, you could use a NOT NULL constraint on the "opportunity_name" field in the "opportunities" table to ensure that each opportunity has a name.

  • UNIQUE constraint: You might use a UNIQUE constraint to ensure that the data in a field is unique across the entire table. For example, you could use a UNIQUE constraint on the "email" field in the "contacts" table to ensure that each contact has a unique email address.

  • CHECK constraint: You might use a CHECK constraint to ensure that the data in a field meets certain conditions. For example, you could use a CHECK constraint to ensure that the "deal_probability" field in the "opportunities" table is a value between 0 and 100.

  • DEFAULT constraint: You might use a DEFAULT constraint to specify a default value for a field. For example, you could use a DEFAULT constraint on the "stage" field in the "opportunities" table to set the default value to "prospecting"

SQL Question 8: Extract Customers with an Email Address Domain

Kaspi kz has several customers who register with their email addresses. They want to identify customers who have used an email address with a specific domain for their registration, for example, only '@yahoo.com'.

Write a SQL query to filter customers with email addresses ending with a specified domain.

Example Input:
customer_idfirst_namelast_nameemail
001JohnDoejohn.doe@gmail.com
002JaneSmithjane.smith@yahoo.com
003DavidLeedavid.lee@yahoo.com
004EmmaJohnsonemma.johnson@outlook.com
005RobertBrownrobert.brown@gmail.com
Example Output:
customer_idfirst_namelast_nameemail
002JaneSmithjane.smith@yahoo.com
003DavidLeedavid.lee@yahoo.com

Answer:


The keyword in SQL is used in the clause to search for a specific pattern in a column. '%' is a wild card that may represent zero, one, or multiple characters. The SQL statement above selects all fields from the table where the field ends with '@yahoo.com', thus, returning customers who have used an yahoo.com email address for registration.

SQL Question 9: Average Transaction Amount On A Monthly Basis

Kaspi.kz is a large digital ecosystem with a leading position in e-commerce and fintech in Kazakhstan. As a data analyst at Kaspi, you are tasked with determining the average transaction amount for each of their mobile wallet transactions for each month in the year 2020.

You are given access to the table, which has the following structure:

Example Input:
transaction_iduser_idtransaction_datetransaction_amount
1231432101/15/2020 08:20:003450
1232523502/16/2020 12:45:001250
1233365403/21/2020 16:22:002310
1234958404/29/2020 10:00:009340
1235326505/19/2020 17:45:002250
1236852306/07/2020 20:30:007100
1237459307/24/2020 22:05:004350
1238547308/11/2020 07:00:003675
1239693809/01/2020 10:16:005200
1240284510/12/2020 14:20:004500
1241360111/03/2020 19:33:006800
1242529312/22/2020 21:40:003500

You are expected to output a table with two columns, and .

Expected Output:
mthavg_transaction_amount
13450
21250
32310
49340
52250
67100
74350
83675
95200
104500
116800
123500

Answer:


This query first filters the table for transactions that occurred in the year 2020. It then groups the transactions by month, calculated via the function. Finally, it calculates the average amount for each month and orders the output table by month.

Preparing For The Kaspi kz SQL Interview

The best way to prepare for a Kaspi kz SQL interview is to practice, practice, practice. In addition to solving the earlier Kaspi kz SQL interview questions, you should also solve the 200+ FAANG SQL Questions on DataLemur which come from companies like Google, Uber, and Microsoft. DataLemur Questions

Each exercise has multiple hints, full answers and best of all, there's an online SQL coding environment so you can instantly run your SQL query and have it executed.

To prep for the Kaspi kz SQL interview it is also wise to solve SQL problems from other tech companies like:

However, if your SQL skills are weak, forget about diving straight into solving questions – strengthen your SQL foundations with this interactive SQL tutorial.

DataLemur SQL tutorial

This tutorial covers SQL topics like filtering on multiple conditions using AND/OR/NOT and SUM/AVG window functions – both of these show up routinely during SQL job interviews at Kaspi kz.

Kaspi.kz Data Science Interview Tips

What Do Kaspi kz Data Science Interviews Cover?

Beyond writing SQL queries, the other types of questions to prepare for the Kaspi kz Data Science Interview are:

Kaspi kz Data Scientist

How To Prepare for Kaspi kz Data Science Interviews?

The best way to prepare for Kaspi kz Data Science interviews is by reading Ace the Data Science Interview. The book's got:

  • 201 Interview Questions from Google, Microsoft & tech startups
  • A Refresher covering Product Analytics, SQL & ML
  • Amazing Reviews (900+ reviews, 4.5-star rating)

Acing Data Science Interview

© 2024 DataLemur, Inc

Career Resources

Free 9-Day Data Interview Crash CourseFree SQL Tutorial for Data AnalyticsSQL Interview Cheat Sheet PDFUltimate SQL Interview GuideAce the Data Job Hunt Video CourseAce the Data Science InterviewBest Books for Data Analysts