9 Nykaa SQL Interview Questions (Updated 2024)
At Nykaa, SQL does the heavy lifting for analyzing customer purchase patterns and managing inventory data for beauty and fashion products. So, it shouldn't surprise you that Nykaa frequently asks SQL coding questions in interviews for Data Science and Data Engineering positions.
Thus, to help you prepare for the Nykaa SQL interview, here’s 9 Nykaa SQL interview questions – able to answer them all?
9 Nykaa SQL Interview Questions
SQL Question 1: Identify Whale Users for Nykaa
Nykaa is an online retail company that sells beauty and wellness products. The business considers a "Whale User" to be a user who makes purchases frequently (say more than 4 orders a month) and whose total transaction value is high (say, more than $1,000 a month). Write a SQL query that identifies these Whale Users.
Example Input:
| order_id | user_id | order_date | total_amount |
|---|---|---|---|
| 2769 | 12 | 06/08/2022 | 220 |
| 3412 | 45 | 06/10/2022 | 500 |
| 9894 | 12 | 06/11/2022 | 255 |
| 3463 | 33 | 06/15/2022 | 450 |
| 1775 | 45 | 06/24/2022 | 1200 |
| 3932 | 12 | 06/25/2022 | 190 |
| 2113 | 45 | 06/30/2022 | 400 |
Example Input:
| user_id | user_name |
|---|---|
| 12 | Alice |
| 33 | Bob |
| 45 | Charlie |
Answer:
This query starts by grouping by and then, using the clause, filters out users who haven't made more than 4 orders and whose total transaction value is less than 1000 in the current month. Next, the query uses a join to derive the user_name for those user_ids from the table. The result will be a list of whale users' ids and names who fulfill both conditions (number of orders and transaction value).
To practice a related customer analytics question on DataLemur's free interactive coding environment, try this Microsoft Teams Power User SQL Interview Question:
SQL Question 2: Calculate Monthly Average Ratings
Nykaa is a multi-brand beauty retailer selling cosmetic and wellness products. As a data analyst, you are tasked with analyzing product reviews. You are to provide a breakdown of the average ratings a product receives each month.
Example Input:
| review_id | user_id | submit_date | product_id | stars |
|---|---|---|---|---|
| 1234 | 78 | 2022-01-15 | 001 | 5 |
| 2345 | 89 | 2022-01-20 | 002 | 4 |
| 3456 | 90 | 2022-01-30 | 001 | 3 |
| 4567 | 91 | 2022-02-01 | 001 | 2 |
| 5678 | 92 | 2022-02-05 | 002 | 5 |
| 6789 | 93 | 2022-02-25 | 001 | 4 |
| 7890 | 94 | 2022-02-28 | 002 | 3 |
| 8901 | 95 | 2022-03-01 | 001 | 4 |
| 9012 | 96 | 2022-03-15 | 002 | 5 |
| 0123 | 97 | 2022-03-30 | 001 | 2 |
The output should give the product id, the month, and the average rating for that month.
Example Output:
| product_id | month | avg_rating |
|---|---|---|
| 001 | 1 | 4.00 |
| 002 | 1 | 4.00 |
| 001 | 2 | 3.00 |
| 002 | 2 | 4.00 |
| 001 | 3 | 3.00 |
| 002 | 3 | 5.00 |
Answer:
This PostgreSQL query uses the , , , and SQL functions to calculate and display the average rating per product per month. The function is used to compute the average rating, while the function is utilized to get the month part from the . The results are then grouped by and and presented in ascending order based on the and .
To solve a related window function SQL problem on DataLemur's free interactive coding environment, solve this Google SQL Interview Question:
SQL Question 3: DBMS transactions are expected to follow the ACID properties. What are they, and what does each property mean?
ACID refers to the four key properties that are essential to the reliable and correct execution of database transactions. These properties are:
Atomicity: ensures that a transaction is treated as a single operation, and either all of the changes are made or none of them are! Basically, the database version of a "-FULL SEND-"
Consistency: ensures that the data is in a consistent state before and after a transaction is completed. For example, if wiring money to a friendly Nigerian prince whose fallen on hard times, consistency ensures that the total value of funds lost in my account is the same amount that's gained in the prince's account!
Isolation: ensures that the intermediate state of a transaction is invisible to other transactions. Back to the wiring-the-prince-some-money example, isolation ensures that another transaction sees the transferred funds in my account OR the princes, but not in both accounts at the same time
Durability: ensures that once a transaction has been completed successfully, the changes made by the transaction are permanent and cannot be undone, even in the event of a system failure. Basically, no taksies backsies (even if your system has a meltdown!).
SQL Question 4: Product Popularity
Nykaa, a leading beauty and wellness e-commerce platform, is tempting to analyze how well their diversified range of products are performing. To do this, you need to design a database schema that models , and . Nykaa is mainly interested in finding out the top 5 popular products, determined by the amount of unique users purchasing each product. Design the database tables considering these needs, create sample data, and write a SQL query to find the top 5 products accompanied by the number of unique users who purchased them.
Example Input:
| user_id | user_name | join_date | |
|---|---|---|---|
| 1 | John Doe | john_doe@example.com | 2021-08-01 |
| 2 | Jane Doe | jane_doe@example.com | 2021-09-01 |
| 3 | Sam Smith | sam_smith@example.com | 2022-01-01 |
Example Input:
| product_id | product_name | price |
|---|---|---|
| 100 | Lipstick A | 500 |
| 200 | Eye Pencil B | 300 |
| 300 | Foundation C | 1000 |
Example Input:
| purchase_id | user_id | product_id | purchase_date |
|---|---|---|---|
| 1 | 1 | 100 | 2022-01-15 |
| 2 | 2 | 200 | 2022-02-18 |
| 3 | 1 | 200 | 2022-03-15 |
| 4 | 3 | 300 | 2022-04-18 |
| 5 | 2 | 300 | 2022-05-15 |
Answer:
The above query joins the and tables on , and then groups by and to get the count of unique users who have made purchases. The results are then ordered by the count of unique user purchases in descending order to get the top 5 popular products. It returns the product id and name, along with the number of unique users who have made purchases.
SQL Question 5: Can you describe the concept of a database index and the various types of indexes?
An index in a database is a data structure that helps to quickly find and access specific records in a table.
For example, if you had a database of Nykaa customers, you could create a primary index on the column.
Having a primary index on the column can speed up performance in several ways. For example, if you want to retrieve a specific customer record based on their , the database can use the primary index to quickly locate and retrieve the desired record. The primary index acts like a map, allowing the database to quickly find the location of the desired record without having to search through the entire table.
Additionally, a primary index can also be used to enforce the uniqueness of the column, ensuring that no duplicate values are inserted into the table. This can help to prevent errors and maintain the integrity of the data in the table.
SQL Question 6: Nykaa Customer Behavior Analysis
You are a data analyst at Nykaa, an online cosmetic and wellness retailer. Management wants to know the behavior of the customers who have made repeated purchases in the last year but have not made any purchase in the last 3 months. Additionally, they are interested in customers who have their total purchase amount greater than $1000.
Create a SQL query to extract the required details. The major filter clauses being:
- The customer has made purchases in the last year
- The customer has not made any purchase in the last 3 months
- The total purchase amount of the customer is greater than '1000'
The table is given below:
Table:
| order_id | customer_id | order_date | product_id | amount |
|---|---|---|---|---|
| 101 | 501 | 08/13/2021 | 60001 | 200 |
| 102 | 552 | 03/19/2022 | 65052 | 150 |
| 103 | 712 | 07/09/2021 | 60001 | 250 |
| 104 | 501 | 12/11/2021 | 65052 | 300 |
| 105 | 552 | 01/05/2022 | 60001 | 400 |
| 106 | 501 | 07/26/2021 | 60001 | 350 |
Answer:
This query first filters out the records where the order date lies between the last year and the last 3 months. It then groups the records by customer_id and calculates the total purchase amount of each customer. Lastly, it uses the HAVING clause to retain only those records where the total amount of purchase is greater than 1000.
SQL Question 7: What is the process for finding records in one table that do not exist in another?
To find records in one table that aren't in another, you can use a and check for values in the right-side table.
Here's an example using two tables, Nykaa employees and Nykaa managers:
This query returns all rows from Nykaa employees where there is no matching row in managers based on the column.
You can also use the operator in PostgreSQL and Microsoft SQL Server to return the records that are in the first table but not in the second. Here is an example:
This will return all rows from employees that are not in managers. The operator works by returning the rows that are returned by the first query, but not by the second.
Note that isn't supported by all DBMS systems, like in MySQL and Oracle (but have no fear, since you can use the operator to achieve a similar result).
SQL Question 8: Nykaa's Click-Through Rate Analysis
Nykaa is an Indian retail seller of beauty, wellness, and fashion products. As a data analyst for Nykaa, you are tasked to analyze the click-through rates (CTR) of their digital ads to help improve their marketing strategy.
Here's your task: Calculate the click-through rate for each ad. Click-through rate (CTR) is calculated as the number of users who clicked on the ad divided by the number of total ad impressions.
Use the following tables:
:
| ad_id | impression_date | views |
|---|---|---|
| 101 | 02/02/2022 | 5000 |
| 102 | 02/05/2022 | 6500 |
| 103 | 02/10/2022 | 8000 |
| 104 | 02/15/2022 | 7500 |
| 105 | 02/20/2022 | 7000 |
:
| click_id | ad_id | click_date | user_id |
|---|---|---|---|
| 111 | 101 | 02/03/2022 | 301 |
| 112 | 101 | 02/03/2022 | 302 |
| 113 | 102 | 02/06/2022 | 303 |
| 114 | 102 | 02/06/2022 | 304 |
| 115 | 103 | 02/11/2022 | 305 |
The ad IDs in both the tables correspond to the same ad.
Answer:
Here is the SQL query in PostgreSQL that you can use to answer the question:
This query calculates the total ad impressions for each ad, the total clicks each ad got, and then calculates the clickthrough rate (ctr) using these two values. The ctr is calculated as the number of clicks divided by the total impressions multiplied by 100 to get a percentage. The LEFT JOIN ensures that even ads with no clicks are included in the result.
To solve a similar problem about calculating rates, solve this TikTok SQL question within DataLemur's online SQL code editor:
SQL Question 9: Analyze customers' purchase and review data
Assume a company named Nykaa runs an online store that sells beauty products. They have two tables, one is and another is . In the table, each row represents a unique customer with fields: customer_id, first_name, and last_name. In the table, each row describes a product purchased by a customer, with fields: purchase_id, customer_id, product_id, purchase_date, and review_stars (a rating given by the customer for the purchased product).
The task is to write a SQL query that brings together these two tables to show the first and last names of customers along with the IDs and review_stars of products they purchased.
Example Input:
| customer_id | first_name | last_name |
|---|---|---|
| 101 | Emma | Johnson |
| 102 | Olivia | Williams |
| 103 | Ava | Jones |
Example Input:
| purchase_id | customer_id | product_id | purchase_date | review_stars |
|---|---|---|---|---|
| 1001 | 101 | 2001 | 07/01/2022 | 5 |
| 1002 | 102 | 2002 | 07/02/2022 | 4 |
| 1003 | 101 | 2003 | 07/03/2022 | 3 |
Example Output:
| first_name | last_name | product_id | review_stars |
|---|---|---|---|
| Emma | Johnson | 2001 | 5 |
| Olivia | Williams | 2002 | 4 |
| Emma | Johnson | 2003 | 3 |
Answer:
This query performs an INNER JOIN operation on the two tables using the field. It then selects the and columns from the table and the and columns from the table, producing a result that shows every customer's full name along with the product IDs and review stars for the products they have purchased.
Since join questions come up routinely during SQL interviews, try this Spotify JOIN SQL question:
Nykaa SQL Interview Tips
The best way to prepare for a SQL interview, besides making sure you have strong SQL fundamentals, is to practice a ton of real SQL questions that were asked in recent job interviews.
Besides solving the earlier Nykaa SQL interview questions, you should also solve the 200+ FAANG SQL Questions on DataLemur which come from companies like Amazon, Microsoft, Meta, and smaller tech companies.
Each SQL question has multiple hints, detailed solutions and most importantly, there's an interactive coding environment so you can right online code up your query and have it checked.
To prep for the Nykaa SQL interview it is also useful to solve SQL questions from other tech companies like:
In case your SQL coding skills are weak, don't worry about going right into solving questions – go learn SQL with this SQL interview tutorial.
This tutorial covers topics including filtering data with WHERE and 4 types of JOINS – both of which show up frequently during SQL job interviews at Nykaa.
Nykaa Data Science Interview Tips
What Do Nykaa Data Science Interviews Cover?
Besides SQL interview questions, the other topics covered in the Nykaa Data Science Interview are:
- Stats Interview Questions
- Python Pandas or R Coding Questions
- Analytics and Product-Metrics Questions
- ML Interview Questions
- Behavioral Interview Questions
How To Prepare for Nykaa Data Science Interviews?
The best way to prepare for Nykaa Data Science interviews is by reading Ace the Data Science Interview. The book's got:
- 201 Interview Questions from Microsoft, Amazon & startups
- A Crash Course covering Python, SQL & ML
- Great Reviews (900+ reviews, 4.5-star rating)