Unlock your potential in data analytics. Discover the essential SQL Interview Questions asked by Swiggy and Zomato for 2+ years of experience in roles, with competitive 18-22 LPA packages. Get practical insights and actionable tips to confidently navigate your next interview. Read More

Q. Design a database schema to manage restaurants, customers, and orders, ensuring efficient data retrieval and integrity.

Schema Design

1. Restaurants Table

Store details of each restaurant:

CREATE TABLE Restaurants (
    restaurant_id   INT PRIMARY KEY AUTO_INCREMENT,
    name           VARCHAR(255) NOT NULL,
    address        TEXT NOT NULL,
    phone          VARCHAR(20),
    cuisine_type   VARCHAR(100),
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. Customers Table

Store customer details:

CREATE TABLE Customers (
    customer_id    INT PRIMARY KEY AUTO_INCREMENT,
    name          VARCHAR(255) NOT NULL,
    email         VARCHAR(255) UNIQUE NOT NULL,
    phone         VARCHAR(20) UNIQUE NOT NULL,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

3. Menu Table

Store restaurant menus with their items:

CREATE TABLE Menu (
    menu_id        INT PRIMARY KEY AUTO_INCREMENT,
    restaurant_id  INT NOT NULL,
    item_name      VARCHAR(255) NOT NULL,
    price          DECIMAL(10,2) NOT NULL,
    category       VARCHAR(100),
    is_available   BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (restaurant_id) REFERENCES Restaurants(restaurant_id) ON DELETE CASCADE
);

4. Orders Table

Store orders placed by customers:

CREATE TABLE Orders (
    order_id       INT PRIMARY KEY AUTO_INCREMENT,
    customer_id    INT NOT NULL,
    restaurant_id  INT NOT NULL,
    order_status   ENUM('Pending', 'Processing', 'Completed', 'Cancelled') DEFAULT 'Pending',
    order_time     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount   DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ON DELETE CASCADE,
    FOREIGN KEY (restaurant_id) REFERENCES Restaurants(restaurant_id) ON DELETE CASCADE
);

5. Order_Items Table

Store items within an order:

CREATE TABLE Order_Items (
    order_item_id  INT PRIMARY KEY AUTO_INCREMENT,
    order_id       INT NOT NULL,
    menu_id        INT NOT NULL,
    quantity       INT CHECK (quantity > 0),
    price          DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (menu_id) REFERENCES Menu(menu_id) ON DELETE CASCADE
);

6. Payments Table

Store payment details for orders:

CREATE TABLE Payments (
    payment_id     INT PRIMARY KEY AUTO_INCREMENT,
    order_id       INT NOT NULL UNIQUE,
    payment_method ENUM('Credit Card', 'Debit Card', 'Cash', 'UPI') NOT NULL,
    payment_status ENUM('Pending', 'Completed', 'Failed') DEFAULT 'Pending',
    transaction_id VARCHAR(50) UNIQUE,
    payment_time   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE CASCADE
);

7. Reviews Table

Store customer reviews and ratings for restaurants:

CREATE TABLE Reviews (
    review_id      INT PRIMARY KEY AUTO_INCREMENT,
    customer_id    INT NOT NULL,
    restaurant_id  INT NOT NULL,
    rating         INT CHECK (rating BETWEEN 1 AND 5),
    review_text    TEXT,
    review_time    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ON DELETE CASCADE,
    FOREIGN KEY (restaurant_id) REFERENCES Restaurants(restaurant_id) ON DELETE CASCADE
);

Key Features of This Schema:

✅ Normalization: Reduces redundancy (separate Orders, Order_Items, Menu).
✅ Referential Integrity: Enforced via foreign keys (CASCADE deletion ensures consistency).
✅ Scalability: Easy to extend with additional features (e.g., promotions, delivery tracking).
✅ Efficiency: Indexed primary keys ensure fast lookups; ENUM types for status fields optimize storage.

Q. Given tables for customer purchases and product lists, write An SQL query to calculate the year-over-year growth rate of the amount spent by each customer.

WITH salesum AS (
    SELECT 
        YEAR(purchase_date) AS year, 
        customer_id, 
        SUM(purchase_amount) AS total_purchase 
    FROM Purchases
    GROUP BY YEAR(purchase_date), customer_id
),
saleagg AS (
    SELECT  
        s1.customer_id,
        s1.year AS prev_year,
        s1.total_purchase AS prev_year_sale,
        s2.year AS curr_year,
        s2.total_purchase AS current_year_sale
    FROM salesum s1 
    INNER JOIN salesum s2 
    ON s1.customer_id = s2.customer_id 
    AND s1.year + 1 = s2.year
)
SELECT *,
    ROUND(((current_year_sale - prev_year_sale) / prev_year_sale) * 100, 2) AS Y_on_Y_growth
FROM saleagg;

https://www.db-fiddle.com/f/u4LKSXpHVTy6KSXgKHkZ6q/0

My Approach to Solving This Question

First, I focused on understanding the requirement: calculating the year-over-year (YoY) growth in customer spending on Zomato. To do this, I needed to analyze the available data and extract the necessary information. The key table for this calculation was the Purchases table since it contained all the relevant timestamps and spending details.

Step 1: Extracting Customer-Wise Yearly Spend

I created a Common Table Expression (CTE) where:

• I extracted the year from the purchase_date.
• Retrieved the customer_id.
• Calculated the total purchase amount for each customer per year.
• Grouped the data by year and customer_id to get customer-wise yearly spending.

Step 2: Aggregating Year-on-Year Sales

Next, I created another CTE that aggregated the previous and current year’s spending by:

• Joining the yearly spending CTE with itself based on customer_id, ensuring the second year was always the next year of the first.
• Extracting the previous year’s sales, current year’s sales, and their respective years.

Step 3: Calculating Year-on-Year Growth

In the final query:

• I selected everything from the sales aggregation CTE.
• Used the formula: Current Year Sale−Previous Year SalePrevious Year Sale×100\frac{\text{Current Year Sale} – \text{Previous Year Sale}}{\text{Previous Year Sale}} \times 100Previous Year SaleCurrent Year Sale−Previous Year Sale​×100 to calculate YoY growth, rounding it to two decimal places.

This gave me the year-over-year growth in customer spending in a structured and optimized way. 🚀

Q. Write a SQL query to find the average rating for each restaurant for each month, only including restaurants with at least two reviews in a given month.

SELECT 
    DATE_FORMAT(review_date, '%Y-%m') AS month,    
    restaurant_id,
    ROUND(AVG(rating),2) AS avg_rating,
    COUNT(rating) AS num_rating
FROM Reviews
GROUP BY restaurant_id, month
HAVING num_rating >= 2;

https://onecompiler.com/mysql/438j75sbb

My approach to solving this was to use the Reviews table because it contains all the necessary data: review date, restaurant ID, and rating—everything needed to compute the required result.

1. Extracting the Month & Year:
   • I extracted the year and month from the review_date using DATE_FORMAT(review_date, '%Y-%m').
   • This format ensures clarity, so anyone glancing at the output can immediately identify the specific year and month for each review.
2. Selecting Required Fields:
   • I took restaurant_id since we need to calculate ratings per restaurant.
   • I computed the average rating using AVG(rating), giving the monthly average rating per restaurant.
   • I also calculated COUNT(rating) to get the number of reviews per restaurant per month.
3. Filtering Restaurants with at Least 2 Reviews:
   • Since the question specifies that only restaurants with at least 2 reviews per month should be included, I applied a HAVING clause to filter out restaurants with less than 2 reviews.
4. Grouping the Data:
   • I grouped the result by restaurant ID and month to ensure we get the correct monthly aggregation.

By following this approach, I successfully retrieved the monthly average rating for each restaurant, ensuring that only restaurants with two or more reviews per month were considered.

Q. What is the purpose of normalization in databases, and why is it important?

Normalization organizes data to reduce redundancy and improve integrity by dividing tables into smaller, related ones. This minimizes anomalies in insertion, update, and deletion while improving data consistency.

Q. Explain the concept of a stored procedure in SQL and provide a scenario where it would be beneficial.

A stored procedure is a precompiled collection of SQL statements that can be executed as a single unit. It is stored in the database and can accept input parameters, process data, and return results.

Key Features of Stored Procedures:

✅ Modularity – Encapsulates SQL logic for reuse.
✅ Performance – Reduces parsing and compilation overhead.
✅ Security – Can restrict direct table access by granting permission only on procedures.
✅ Maintainability – Simplifies complex queries by breaking them into manageable blocks.

Q. How would you explain a complex technical concept to a non-technical team member or stakeholder?

When explaining a complex technical concept to a non-technical stakeholder, my approach is clarity, relevance, and simplicity. Here’s how I do it:

1️⃣ Understand Their Perspective – Focus on what matters to them and avoid jargon.
2️⃣ Use Analogies – Relate concepts to everyday examples (e.g., a database index is like a phone book).
3️⃣ Explain the ‘Why’ First – Show the impact before diving into details.
4️⃣ Keep It Visual & Concise – Use diagrams or simple explanations.
5️⃣ Encourage Questions – Verify understanding and adjust if needed.

Example: Instead of saying “We optimize queries with indexing,” I’d say “Indexes help find data faster, like using a phone book instead of searching every page.”

Clear, relatable explanations lead to better collaboration!

Additional Database & SQL Interview Questions

1. What are different types of SQL joins?

A. SQL joins are used to combine data from two or more tables based on a related column. The different types of joins are,

1. INNER JOIN – Returns only the matching rows from both tables.
2. LEFT JOIN (LEFT OUTER JOIN) – Returns all rows from left table with matching rows from right table, unmatched rows from the left table are included with NULLs.
3. RIGHT JOIN (RIGHT OUTER JOIN) – Returns all rows from right table with matching rows from left table, unmatched rows from the right table are included with NULLs.
4. FULL JOIN (FULL OUTER JOIN) – Returns all rows from both tables, filling in NULLs when there is no match.
5. CROSS JOIN – Produces a cartesian product of both tables (i. e. every row both first table is joined with every row from second table.)
6. SELF JOIN – A table joins with itself to compare or relate data within the same table.
7. NATURAL JOIN – Automatically join tables based on columns with the same name and data type, eliminating duplicate columns.

2. How do you find duplicate records in a table?

A. To find duplicate records in a table, we can used GROUP BY clause along with HAVING COUNT(*)>1 .

3. What is a window function in SQL? Give examples?

A. A window function in SQL performs calculations across a set of table rows related to the current row, without collapsing the result set like aggregate functions do, it “slides” over a window of rows and allows you to compute running totals, rankings and other calculations while retaining individual row details.

For example,

1. ROW_NUMBER() – Assigns a unique row number
2. RANK() – Assigns ranks with gaps for ties
3. DENSE_RANK() – Assigns ranks without gaps.

4. How do you optimize a slow SQL query?

A. There are many techniques to optimize a slow running query, some of which are, using indexing, selecting only necessary columns, filtering early, using appropriate joins, partitioning large table, and using EXPLAIN ANALYSE to identify bottlenecks in query execution.

5. Explain the difference between a CTE and a subquery.

A. A Common Table Expression (CTE) is a temporary result set defined using with and can be referenced multiple times in a query, making it more readable and efficient for complex operations. A subquery, on the other hand, is a nested query inside SELECT, FROM, or `WHERE, and executes once per use, which can sometimes impact performance.

6. How would you design a normalized database schema?

A. To design a normalized database schema, I follow normalization principles to minimize redundancy and improve data integrity. The key steps are:”

1. Identify Entities & Attributes – Determine the main entities (e.g., Customers, Orders, Products) and their attributes.
2. Apply Normalization Rules – Ensure the schema meets at least Third Normal Form (3NF):
   • 1NF (First Normal Form): Ensure atomicity (no repeating groups or arrays).
   • 2NF (Second Normal Form): Remove partial dependencies (every non-key column depends on the full primary key).
   • 3NF (Third Normal Form): Remove transitive dependencies (non-key attributes depend only on the primary key).
3. Define Relationships – Use primary keys and foreign keys to establish one-to-many or many-to-many relationships.
4. Optimize for Queries – Normalize to avoid redundancy but denormalize if performance requires it.
5. Ensure Referential Integrity – Enforce constraints like ON DELETE CASCADE or ON UPDATE RESTRICT where needed.

For example, in an e-commerce system, I would design tables like Customers (customer_id, name), Orders (order_id, customer_id, order_date), and Order_Items (order_item_id, order_id, product_id, quantity), ensuring all relationships are properly defined.

7. What is the use of indexing in SQL?

A. Indexing in SQL improves query performance by allowing the database engine to locate data faster instead of scanning entire tables. Indexes act like a roadmap, helping retrieve records efficiently.”

Key benefits:

• Speeds up SELECT queries by reducing the number of rows scanned.
• Optimizes WHERE, JOIN, ORDER BY, and GROUP BY operations.
• Improves performance in large datasets.
• Supports unique constraints (e.g., PRIMARY KEY, UNIQUE).

“However, indexes add overhead on INSERT, UPDATE, and DELETE operations, so they should be used wisely.

We hope this article has provided valuable information about SQL Interview Questions. We’re here to support you on your data analyst journey. Make sure you share this article with your friends or on social media.

Also Read:-

• Covering Indexes: Boosting Query Performance with Less Data Access
• Multi-Column Indexes: When You Need More Than One Column
• SQL Interview questions Asked By Swiggy and Zomato | Experience: 2+ years | CTC= 18 – 22 LPA
• 20 practice questions on the Python while loop
• Flipkart Business Analyst Interview Experience (1-3 years):CTC – 14 LPA