8 MapmyIndia SQL Interview Questions (Updated 2024)
Data Science, Data Engineering and Data Analytics employees at MapmyIndia code up SQL queries daily as part of their job. They use SQL for analyzing and interpreting complex geographical data sets, and managing the extensive geospatial databases efficiently. Because of this, MapmyIndia asks interviewees SQL interview problems.
To help you prep for the MapmyIndia SQL interview, we've collected 8 MapmyIndia SQL interview questions – scroll down to start solving them!
8 MapmyIndia SQL Interview Questions
Sure, here is a sample interview question related to MapmyIndia:
SQL Question 1: Calculating Ratings Over Time
MapmyIndia collects GPS data from users as they travel around cities and rates the routes taken. They collect the date, user_id, route_id, and a user-provided rating from 1 (lowest) to 5 (highest) for each trip.
Your task is analyze the average rating of each route per month. Write an SQL query to find this information.
Example Input:
| trip_id | user_id | date | route_id | rating |
|---|---|---|---|---|
| 1 | 1000 | 2022-01-14 | 500 | 3 |
| 2 | 1001 | 2022-01-31 | 500 | 4 |
| 3 | 1002 | 2022-02-01 | 500 | 2 |
| 4 | 1003 | 2022-02-15 | 500 | 5 |
| 5 | 1004 | 2022-03-03 | 600 | 4 |
| 6 | 1005 | 2022-03-18 | 600 | 3 |
| 7 | 1006 | 2022-03-29 | 500 | 4 |
Answer:
For PostgreSQL, we can use the function to extract the month and function to calculate the average rating.
The SQL query returns the average rating for each route for each month. The output would look like this:
Example Output:
| month | route_id | avg_rating |
|---|---|---|
| 1 | 500 | 3.5 |
| 2 | 500 | 3.5 |
| 3 | 500 | 4.0 |
| 3 | 600 | 3.5 |
So, for the above example, in January and February, route 500 has an average rating of 3.5, and in March, it has a rating of 4. For route 600, in March, it has an average rating of 3.5.
To practice another window function question on DataLemur's free interactive coding environment, solve this Google SQL Interview Question:
SQL Question 2: 2nd Largest Salary
Imagine you had a table of MapmyIndia employee salary data. Write a SQL query to find the 2nd highest salary at the company.
MapmyIndia Example Input:
| employee_id | salary |
|---|---|
| 1 | 2500 |
| 2 | 800 |
| 3 | 1000 |
| 4 | 1200 |
Example Output:
| second_highest_salary |
|---|
| 1200 |
Try this interview question and run your code right in the browser:
Answer:
You can find a detailed solution with hints here: 2nd Highest Salary.
SQL Question 3: When considering database normalization, how do 1NF, 2NF, and 3NF differ from one another?
Normalization is the process of organizing fields and tables of a database to minimize redundancy and dependency. While there are technically 5 levels (normal forms), the 3 most important normal forms you need to know about for SQL interviews at MapmyIndia are:
- First Normal Form (1NF): This should fix remove a table's duplicate columns. Also, each column should contain only a single value (no lists or containers of data), and finally each row of table should have a unique identifier as well.
- Second Normal Form (2NF): A table is in its second normal form if it meets all requirements of the first normal form and places the subsets of columns in separate tables. The relationships between tables are created using primary/foreign keys.
- Third Normal Form (3NF): The table should be in the second normal form. There should be no dependency on another non-key attribute (meaning a primary key should be the only thing required to identify the row).
SQL Question 4: User tracking for map usage in MapmyIndia
MapmyIndia is a company that builds mapping solutions and services. One of their features allows users to save popular locations and tracks (like daily jog routes). They would like to analyze the usage pattern of their users. They have three tables - with columns (int) and (varchar), with columns (int), (varchar), (float) and (float), and with columns (int, FK), (int, FK) and (date).
Design a query to fetch the names of all users who have saved a particular location named "Delhi" more than once.
Example Input:
| user_id | name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
| 4 | David |
Example Input:
| location_id | name | longitude | latitude |
|---|---|---|---|
| 1 | Delhi | 28.7041 | 77.1025 |
| 2 | Mumbai | 19.0760 | 72.8777 |
| 3 | Chennai | 13.0827 | 80.2707 |
| 4 | Kolkata | 22.5726 | 88.3639 |
Example Input:
| user_id | location_id | save_date |
|---|---|---|
| 1 | 1 | 06/08/2022 |
| 2 | 2 | 06/10/2022 |
| 1 | 1 | 06/18/2022 |
| 3 | 1 | 07/26/2022 |
| 2 | 1 | 07/05/2022 |
| 1 | 1 | 07/23/2022 |
| 2 | 1 | 07/25/2022 |
Answer:
This query first joins and to find all records where the location name is "Delhi", then groups by to count the frequency of saving. Conditions in the clause filter out users who have saved the location more than once. The results are then joined with the table to get users' names.
SQL Question 5: What's the difference between an inner and a full outer join?
An inner join only includes rows from both tables that satisfy the join condition, whereas a full outer join includes all rows from both tables, regardless of whether they match the join condition or not.
For example, suppose you had a table of MapmyIndia orders and MapmyIndia customers.
INNER JOIN: When there is a match in the shared key or keys, rows from both tables are retrieved. In this example, an between the Orders and Customers tables would retrieve rows where the in the Orders table matches the in the Customers table.
FULL OUTER JOIN: A retrieves all rows from both tables, regardless of whether there is a match in the shared key or keys. If there is no match, NULL values will be returned for the columns of the non-matching table.
SQL Question 6: Average Travel Duration
MapmyIndia provides advanced mapping and location-based services. One of their products is a navigation app for consumers. As an SQL developer at MapmyIndia, can you write a query to find the average travel duration of each destination that users traveled to during the last month?
Example Input:
| log_id | user_id | destination | travel_date | travel_duration(mins) |
|---|---|---|---|---|
| 5001 | 101 | Delhi | 09/01/2022 00:00:00 | 60 |
| 5002 | 102 | Mumbai | 09/15/2022 00:00:00 | 200 |
| 5003 | 103 | Delhi | 09/20/2022 00:00:00 | 75 |
| 5004 | 104 | Mumbai | 09/25/2022 00:00:00 | 210 |
| 5005 | 105 | Delhi | 09/29/2022 00:00:00 | 65 |
Example Output:
| Destination | Average_Duration(mins) |
|---|---|
| Delhi | 66.67 |
| Mumbai | 205.00 |
Answer:
The PostgreSQL code calculates the average travel duration for each destination from the travel_logs table where the travel_date is between the first and the last day of the previous month. To achieve this, it uses the AVG function for calculating the average, DATE_TRUNC function to get the first day of the current and previous month, and Current_Date to get today's date. The result is grouped by the destination. Notice that this takes into account only those logs which have a travel date in the last month.
To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for finding top items or this Facebook Active User Retention Question which is similar for data time range analysis.
SQL Question 7: What does adding 'DISTINCT' to a SQL query do?
The clause is used to remove all duplicate records from a query.
For example, if you had a table of open jobs MapmyIndia was hiring for, and wanted to see what are all the unique job titles that were currently available at the company, you could write the following query:
SQL Question 8: Compute Distance and Total Travel Time
MapmyIndia provides APIs for calculating the distance and time between different points on a map. Suppose you are working with a table called 'trips' that tracks users' trips with starting and ending coordinates, time, and additional information.
Your task is to write a SQL query that calculates the total distance covered (in km) and total travel time (in minutes) for each trip and rounds them to 2 decimal places. You can assume that the distance function DIST() calculates the distance between two points in kilometers, and travel_time function gives you the time in minutes.
Example Input:
| trip_id | user_id | start_point | end_point | start_time | end_time |
|---|---|---|---|---|---|
| 125 | 765 | POINT(28.620756 77.283391) | POINT(28.459497 77.026634) | 06/08/2022 08:00:00 | 06/08/2022 10:20:00 |
| 898 | 432 | POINT(28.613939 77.209021) | POINT(28.704060 77.102493) | 06/10/2022 11:00:00 | 06/10/2022 11:40:00 |
| 367 | 543 | POINT(28.459497 77.026634) | POINT(28.613939 77.209021) | 06/18/2022 18:30:00 | 06/18/2022 19:20:00 |
| 921 | 102 | POINT(28.704060 77.102493) | POINT(28.620756 77.283391) | 07/26/2022 20:00:00 | 07/26/2022 20:30:00 |
Answer:
In the query, the total distance covered by each trip is calculated using the DIST() function which is assumed to calculate the distance between the start_point and end_point. The result is rounded to 2 decimal places.
The total time for each trip is calculated by subtracting the start_time from the end_time. As PostgreSQL stores time intervals in seconds, the difference needs to be converted from seconds to minutes by dividing by 60. The EXTRACT(EPOCH FROM interval) function gives the number of seconds in the interval. This result is also rounded to 2 decimal places.
To practice a very similar question try this interactive Snapchat Sending vs. Opening Snaps Question which is similar for calculating percentages and applying rounding or this Amazon Server Utilization Time Question which is similar for calculating total time and working with tracking data.
Preparing For The MapmyIndia SQL Interview
The key to acing a MapmyIndia SQL interview is to practice, practice, and then practice some more!
Besides solving the earlier MapmyIndia SQL interview questions, you should also solve the 200+ DataLemur SQL Interview Questions which come from companies like Netflix, Airbnb, and Amazon.
Each interview question has multiple hints, full answers and most importantly, there is an interactive SQL code editor so you can right online code up your SQL query and have it executed.
To prep for the MapmyIndia SQL interview it is also a great idea to practice interview questions from other tech companies like:
In case your SQL query skills are weak, forget about diving straight into solving questions – improve your SQL foundations with this DataLemur SQL tutorial.
This tutorial covers things like handling date/timestamp data and filtering data with WHERE – both of these come up often during MapmyIndia SQL assessments.
MapmyIndia Data Science Interview Tips
What Do MapmyIndia Data Science Interviews Cover?
Beyond writing SQL queries, the other question categories tested in the MapmyIndia Data Science Interview are:
- Experimental Design & AB Testing Questions
- Python or R Programming Questions
- Product Analytics Questions
- Machine Learning Questions
- Behavioral & Resume-Based Questions
How To Prepare for MapmyIndia Data Science Interviews?
I'm a tad biased, but I believe the optimal way to prepare for MapmyIndia Data Science interviews is to read the book I wrote: Ace the Data Science Interview.
The book solves 201 interview questions taken from Google, Microsoft & tech startups. It also has a crash course on Python, SQL & ML. And finally it's helped thousands of people land their dream job in data, which is how it's gotten to be an Amazon best-seller with over 1000+ 5-star reviews.