8 Meituan SQL Interview Questions (Updated 2024)
At Meituan, MySQL & TiDB by PingCap is used day-to-day for analyzing user behavior patterns for tailored recommendations and managing large data sets to optimize delivery logistics. Unsurprisingly this is why Meituan often tests SQL problems during interviews for Data Science, Data Engineering and Data Analytics jobs.
So, if you're stressed about an upcoming SQL Assessment, we've curated 8 Meituan SQL interview questions – how many can you solve?
8 Meituan SQL Interview Questions
SQL Question 1: Power Users on Meituan
Question:
Assume that Meituan is a food delivery platform. It has a table that records each order placed by its users. Power users are defined as users who place orders four or more times a week. Write a SQL query to identify these power users for the current week.
Example Input:
| Order_id | User_id | Restaurant_id | Order_date | Order_amount |
|---|---|---|---|---|
| 101 | 22 | 307 | 07/18/2022 13:00:00 | 19.99 |
| 102 | 33 | 528 | 07/19/2022 07:00:00 | 10.09 |
| 103 | 22 | 779 | 07/20/2022 20:00:00 | 30.99 |
| 104 | 44 | 307 | 07/21/2022 17:30:00 | 45.00 |
| 105 | 22 | 528 | 07/22/2022 12:45:00 | 25.90 |
| 106 | 33 | 779 | 07/22/2022 18:15:00 | 33.79 |
| 107 | 22 | 307 | 07/23/2022 13:30:00 | 20.50 |
| 108 | 55 | 528 | 07/23/2022 07:00:00 | 12.89 |
Answer:
The following PostgreSQL query would solve this problem:
Explanation:
This query does the following:
- Filters the records from the current week: .
- Groups the records by : .
- Aggregates the data by the count of orders per user: .
- Only keeps the users who placed an order 4 or more times: .
The result is the list of user IDs corresponding to power users. Note that 'Order' in the clause is an example table name and should be replaced with the actual order data table name in Meituan's database.
To practice a related customer analytics question on DataLemur's free interactive SQL code editor, try this Microsoft SQL Interview problem:
SQL Question 2: Analyze Product's Monthly Average Rating
Meituan, just like any other product based company, relies on customer's reviews of their products. Analysing these reviews can provide important insights about how well the products are received. In this SQL question, you are asked to write a query that can calculate the monthly average rating (stars) for each product.
Let's consider as the dataset which includes following fields: , , , and . Here, is an unique identifier for each review, identifies unique customers who submitted the review, is the date when the review was submitted, represents unique products and indicates the rating given by the customer ranging from 1 to 5.
Example Input:
| review_id | user_id | submit_date | product_id | stars |
|---|---|---|---|---|
| 6171 | 123 | 2022-06-08 00:00:00 | 50001 | 4 |
| 7802 | 265 | 2022-06-10 00:00:00 | 69852 | 4 |
| 5293 | 362 | 2022-06-18 00:00:00 | 50001 | 3 |
| 6352 | 192 | 2022-07-26 00:00:00 | 69852 | 3 |
| 4517 | 981 | 2022-07-05 00:00:00 | 69852 | 2 |
We want to generate a result with the following structure where is the month of the year (e.g., 1 for January, 2 for February, etc.), represents unique products and is the average rating for that product in that particular month.
Example Output:
| mth | product | avg_stars |
|---|---|---|
| 6 | 50001 | 3.5 |
| 6 | 69852 | 4.0 |
| 7 | 69852 | 2.5 |
Answer:
A SQL solution using window functions and my be written in this way:
This query works by first extracting the month from the using the function. Then it averages the for each for each month using the function. The clause with is used to window the data by and month. Finally, the resulting table is sorted by month and product, to present the data in an organized manner.
To solve a related window function SQL problem on DataLemur's free online SQL coding environment, solve this Amazon SQL Interview Question:
SQL Question 3: What do stored procedures do, and when would you use one?
Stored procedures are a lot like functions in programming. They're used to encapsulate and organize business logic into one unit of code, and they can accept multiple input parameters and return multiple output values.
For example, if you were a Data Analyst at Meituan working on a HR analytics project, you might create a stored procedure to calculate the average salary for a given department:
To call this stored procedure and find the average salary for the Data Analytics department you'd write the following query:
SQL Question 4: Finding Customers Ordering Specific Meal Type
Meituan is a large delivery and on-demand food services business. Your task is to use the table to find out how many customers ordered 'Vegetarian' meal option in the year 2021.
Example Input:
| order_id | customer_id | order_date | meal_type | restaurant_id |
|---|---|---|---|---|
| 2551 | 087 | 2021-03-07 | Vegetarian | 707 |
| 6732 | 998 | 2021-12-04 | Meat Lover | 550 |
| 4782 | 657 | 2020-05-25 | Vegetarian | 707 |
| 5823 | 900 | 2021-10-18 | Fish Feast | 356 |
| 3192 | 320 | 2021-11-14 | Vegetarian | 707 |
Answer:
This query uses the SQL command to find the number of unique customers, the clause to filter the where is 'Vegetarian' and where the order was placed in the year 2021. A special date function is used to get the year part from the . undefined
SQL Question 5: Can you explain the purpose of the SQL constraint?
A UNIQUE constraint ensures that all values in a column are different. It is often used in conjunction with other constraints, such as NOT NULL, to ensure that the data meets certain conditions.
For example, if you had Meituan employee data stored in a database, here's some constraints you'd use:
In the Meituan employee example, the UNIQUE constraint is applied to the "email" field to ensure that each employee has a unique email address. This helps to ensure the integrity of the data in the database and prevents errors that could occur if two employees had the same email address.
SQL Question 6: Analysing transaction and customer data
In Meituan, we maintain two tables, and . The has the fields , , , and .
The includes , and .
Your task is to write a SQL query to join these two tables and find the total amount each customer has spent on a given restaurant. Assume the restaurant_id for the given restaurant is 'R101'.
Here's an example of how you should format sample tables:
Example Input:
| transaction_id | customer_id | order_amount | transaction_date | restaurant_id |
|---|---|---|---|---|
| 101 | C1 | 100 | 08/01/2022 | R101 |
| 102 | C2 | 150 | 08/01/2022 | R102 |
| 103 | C1 | 200 | 08/02/2022 | R101 |
| 104 | C3 | 50 | 08/02/2022 | R101 |
| 105 | C1 | 75 | 08/03/2022 | R103 |
Example Input:
| customer_id | customer_name | city |
|---|---|---|
| C1 | John Doe | Beijing |
| C2 | Jane Smith | Shanghai |
| C3 | Mary Johnson | Beijing |
Examples of Output:
Example Output:
| customer_name | total_amount_spent |
|---|---|
| John Doe | 300 |
| Mary Johnson | 50 |
Answer:
This query first joins the table and table on . It then filters for transactions where is 'R101'. The function is used to get the total per customer and is used to group the result by .
Since joins come up frequently during SQL interviews, try this interactive Snapchat Join SQL question:
SQL Question 7: Can you explain the distinction between an inner and a full outer join?
An inner join returns only the rows that match the join condition between the two tables, whereas a full outer join returns all rows from both tables, including any unmatched rows.
Example:
Example:
SQL Question 8: Average Daily Sales for a Specific Product Category
Meituan, as a multinational tech company specializing in local services, has extensive service categories. Let's focus on the "Food Delivery" category. Interviewees are provided the and tables. The table stores all sales and their timestamp. The table contains the details of all the products sold in the "Food Delivery" category.
The question is: Write a SQL query to calculate the average daily sales (in terms of the quantity sold) for the "Burger" product type in the "Food Delivery" category for the month of August 2022.
Example Input:
| order_id | product_id | quantity | order_timestamp |
|---|---|---|---|
| 1092 | 567 | 3 | 08/01/2022 00:30:22 |
| 2389 | 891 | 2 | 08/03/2022 10:15:50 |
| 3490 | 567 | 1 | 08/03/2022 09:25:35 |
| 4874 | 245 | 4 | 08/04/2022 14:15:13 |
| 5218 | 891 | 3 | 08/10/2022 18:39:56 |
Example Input:
| product_id | product_type | category |
|---|---|---|
| 567 | Burger | Food Delivery |
| 891 | Burger | Food Delivery |
| 245 | Pizza | Food Delivery |
| 786 | Noodles | Food Delivery |
Answer:
The above PostgreSQL query first joins the and tables on the column. It then filters out orders of "Burger" type in the "Food Delivery" category and in the month of August 2022. The postgres function truncates the timestamp to the day level and the month level, respectively. The result is grouped by the date, and the average sale quantity is calculated daily. The clause sorts the result by date. undefined
How To Prepare for the Meituan SQL Interview
The key to acing a Meituan SQL interview is to practice, practice, and then practice some more!
Besides solving the above Meituan SQL interview questions, you should also solve the 200+ SQL Interview Questions on DataLemur which come from companies like Google, Microsoft and Silicon Valley startups.
Each problem on DataLemur has hints to guide you, detailed solutions and crucially, there's an interactive SQL code editor so you can right in the browser run your query and have it graded.
To prep for the Meituan SQL interview it is also wise to solve SQL problems from other tech companies like:
In case your SQL foundations are weak, forget about diving straight into solving questions – refresh your SQL knowledge with this DataLemur SQL Tutorial.
This tutorial covers topics including UNION and Self-Joins – both of which come up often in Meituan SQL assessments.
Meituan Data Science Interview Tips
What Do Meituan Data Science Interviews Cover?
For the Meituan Data Science Interview, besides SQL questions, the other types of questions which are covered:
- Statistics and Probability Questions
- Python or R Programming Questions
- Data Case Study Questions
- Machine Learning and Predictive Modeling Questions
- Behavioral Based Interview Questions
How To Prepare for Meituan Data Science Interviews?
To prepare for Meituan Data Science interviews read the book Ace the Data Science Interview because it's got:
- 201 interview questions sourced from Facebook, Google & startups
- a crash course covering Stats, ML, & Data Case Studies
- over 900+ reviews on Amazon & 4.5-star rating