11 IndiaMART SQL Interview Questions (Updated 2024)

At IndiaMART, SQL does the heavy lifting for "querying and analyzing sales data, and managing supplier and product databases." That's the reason behind why IndiaMART typically asks SQL coding questions during interviews for Data Science and Data Engineering positions.

Thus, to help you study for the IndiaMART SQL interview, we've collected 11 IndiaMART SQL interview questions – can you answer each one?

11 IndiaMART SQL Interview Questions

SQL Question 1: Identify Top Users On IndiaMART

For the hypothetical company "IndiaMART", assuming they are an online marketplace dealing in various kinds of goods and services, and they value the users who make the most purchases or place the most orders the highest, you are to write a query to identify the top 10 users with the most orders in the last 30 days.

For this task, we'll assume there are orders and users tables.

Sample Input:

Table:

Table:

Answer:

Here's a SQL query in PostgreSQL which solves the problem:

This query first joins the and the table on user_id. Then it filters for the orders which are placed in the last 30 days. It groups by username and counts the number of orders per user, ordering it in descending order to get the users with the most number of orders. The restricts the output to the top 10 users.

To practice a similar customer analytics SQL question where you can solve it right in the browser and have your SQL code instantly executed, try this Walmart Labs SQL Interview Question:

SQL Question 2: Calculate the Month Wise Sales and Their Change

Assuming that you are working as a Data Analyst at IndiaMART and your manager has asked you to analyze the current year's monthly sales for each product and calculate the percentage change in sales compared to the previous month. This will help the company to find out which products are doing well and which are not performing as per the expectation.

Example Input:

Answer:

You can use SQL window function to solve this.

Here is a SQL query which can provide the required output:

This query is producing the month wise total sales for each product and the % change in sales compared to the previous month by using PostgreSQL window functions. The inner-most subquery aggregates sales at a monthly level for each product and the following subquery uses the function to get the sales of the previous month. The main query calculates the percentage change.

To solve a similar window function question on DataLemur's free interactive coding environment, solve this Google SQL Interview Question:

SQL Question 3: Can you explain the distinction between a unique and a non-unique index?

While both types of indexes improve the performance of SQL queries by providing a faster way to lookup rows of data, a unique index enforces the uniqueness of the indexed columns while a non-unique index allows duplicate values in the indexed columns.

Suppose you had a table of IndiaMART employees. Here's an example of a unique index on the column:

This index would ensure that no two IndiaMART employees have the same , which could be used as a unique identifier for each employee.

Here's a non-unique index example example on the column:

This index would not enforce uniqueness, but it could be used to improve the performance of queries that filter or sort the data based on the column. For example, if you want to quicklly retreive all Data Scientists, the database can use the index to efficiently locate and retrieve the desired records without having to do a full table scan on all IndiaMART employees.

SQL Question 4: Sales Analysis for Products on IndiaMART

Given the sales data for products on IndiaMART, design tables for Products, Orders, and Order Details, and find out the best-selling product(s) in the last month.

Assumptions:

1. table will hold details about the products.
2. table will hold the details about the Orders.
3. table holds details about which product was ordered in which order.

Example Input:

Example Input:

Example Input:

Answer:

In PostgreSQL, the SQL code would look like this:

This query will output the best-selling product in the last month by joining the necessary tables and counting the total quantity ordered for each product. It then orders the products in descending order by the total quantity ordered and retrieves the top product.

SQL Question 5: Can you explain the difference between a foreign and primary key in a database?

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 IndiaMART customers table might have a primary key column called , while the IndiaMART 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 IndiaMART customer.

SQL Question 6: Calculate Average Purchase Price for Vendors from Different Cities

Suppose you are analyzing vendors from different cities selling on IndiaMART, with data such as vendor_id, city, category, and average_price. Write a SQL query to calculate the average purchase price per product category for vendors in Mumbai or Delhi. Assume the relevant table is called .

Example Input:

Example Output:

Answer:

This query filters records to only include vendors from Mumbai and Delhi using the clause. It then groups the data by city and category using the clause. Finally, it calculates the average price per category in each city using the aggregate function.

SQL Question 7: What's the purpose of a foreign key?

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.

To demonstrate this concept, let's analyze IndiaMART's marketing analytics database which stores data from Google Ads campaigns:

:

+------------+------------+------------+------------+ | ad_id | campaign_id| keyword | click_count| +------------+------------+------------+------------+ | 1 | 100 | IndiaMART pricing | 10 | | 2 | 100 | IndiaMART reviews | 15 | | 3 | 101 | IndiaMART alternatives | 7 | | 4 | 101 | buy IndiaMART | 12 | +------------+------------+------------+------------+

is a foreign key. It references the of the Google Ads campaign that each ad belongs to, establishing a relationship between the ads and their campaigns. This foreign key allows you to easily query the table to find out which ads belong to a specific campaign, or to find out which campaigns a specific ad belongs to.

It is also possible for a table to have multiple foreign keys that reference different primary keys in other tables. For example, the table could have additional foreign keys for the of the ad group that each ad belongs to, and the of the Google Ads account that the campaigns belong to.

SQL Question 8: Find the Average Quantity of Products per Order in IndiaMART

Given the "orders" and "order_details" tables on IndiaMART, write a SQL query to find the average quantity of products per order.

Note:

1. Each row in the "orders" table represents an order with a unique order_id.
2. Each row in the "order_details" table represents a product in an order, with quantity showing how many of that product are in the order.

Example Input:

Example Input:

Answer:

We can solve this by joining the "orders" and "order_details" tables on the "order_id" column, grouping by "order_id", and calculating the average quantity of products.

This query first joins the "orders" and "order_details" tables using an INNER JOIN on the "order_id". It then groups the results by "order_id", calculating the average quantity of products in each "order_id". Finally, it orders the results in descending order by the average quantity.

To practice a very similar question try this interactive Alibaba Compressed Mean Question which is similar for calculating means related to orders or this Walmart Histogram of Users and Purchases Question which is similar for dealing with quantities of products per order.

SQL Question 9: Calculate Click-Through-Rates for IndiaMART's Digital Ads

IndiaMART has launched several digital ads that lead to various product pages on their website. Whenever a customer clicks on an ad, a log is inserted in the table. If the customer adds the product to their cart after viewing the product page, a log is added to the table.

You are required to calculate the click-through rate from viewing a product to adding it to the cart for each product. The click-through rate is calculated as .

Write an SQL query that determines the click-through rate for each product.

Example Input:

Example Input:

Answer:

In this query, we first count the and for each in two subqueries. We then join these two subqueries on and calculate the by dividing by and multiplying by 100. The cast is necessary to perform division with fractions. If has no , it indicates that the product has been clicked but not added to the cart, leading to a of 0.

To solve a similar problem about calculating rates, try this TikTok SQL question within DataLemur's online SQL code editor:

SQL Question 10: 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 IndiaMART 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 11: Calculate Average Rating by Products, Rounded to Two Decimal Places

You are assisting IndiaMART with analyzing their product reviews data. They recently launched some new products and are mainly focused on understanding customer feedback for these items. Out of this, the company is particularly interested in the average rating for each product, with the results rounded to the nearest two decimal places.

They also want to calculate the absolute difference (using ABS() function) between the highest and lowest ratings of each product and the square root (using SQRT() function) of the total rating count of each product.

This data exercise is to help you use math functions and perform arithmetic operations in SQL.

Here is a sample data set consisting of 'reviews' table:

'reviews' Example Input:

Expected Output:

For this exercise, calculate the average rating and round it to two decimal places, the absolute difference between the highest and lowest ratings, and the square root (rounded to two decimal places) of the total rating count for each product.

Answer:

This SQL block calculates the average rating for each product and rounds the result to two decimal places. It also computes the absolute difference between the highest and lowest ratings for each product. Finally, it calculates the square root of the total count of ratings provided for each product, rounding that result to two decimal places as well.

To practice a very similar question try this interactive Amazon Average Review Ratings Question which is similar for calculating average ratings or this Snapchat Sending vs. Opening Snaps Question which is similar for rounding to decimal places.

Preparing For The IndiaMART SQL Interview

Assuming that you've already got basic SQL skills, the next best tip we have to prepare for the IndiaMART SQL interview is to solve as many practice SQL interview questions as you can! In addition to solving the above IndiaMART SQL interview questions, you should also solve the 200+ SQL questions on DataLemur which come from companies like Microsoft, Google, and Facebook.

Each exercise has multiple hints, detailed solutions and most importantly, there's an online SQL coding environment so you can right online code up your SQL query answer and have it graded.

To prep for the IndiaMART SQL interview you can also be wise to solve interview questions from other tech companies like:

• Alibaba SQL questions

• Global Sources SQL questions

• Made-in-China.com SQL questions

• eBay SQL questions

However, if your SQL coding skills are weak, don't worry about jumping right into solving questions – refresh your SQL knowledge with this free SQL tutorial.

This tutorial covers topics including aggreage functions like MIN()/MAX() and filtering data with WHERE – both of which show up routinely during IndiaMART SQL assessments.

IndiaMART Data Science Interview Tips

What Do IndiaMART Data Science Interviews Cover?

In addition to SQL interview questions, the other types of questions to prepare for the IndiaMART Data Science Interview are:

• Statistics and AB Testing Questions
• Python or R Programming Questions
• Product-Sense and Business Case Questions
• Machine Learning Questions
• Behavioral Based Interview Questions

How To Prepare for IndiaMART Data Science Interviews?

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

• 201 interview questions sourced from companies like Google, Tesla, & Goldman Sachs
• a crash course on Product Analytics, SQL & ML
• over 900+ 5-star reviews on Amazon