These are SQL questions of the kind Flipkart actually asks — the patterns reported from Flipkart's SQL screens and data/SDE interview rounds. My advice: treat this page as a mock test. Attempt every question on paper first, then reveal. If any answer surprises you, the lesson it comes from is linked right below — go read it, then come back.
Flipkart SQL query questions
A developer wrote the following query to compute each customer's total amount spent: SELECT c.customer_name, SUM(o.total_amount) AS total_spent FROM customers c JOIN orders o ON c.customer_id = o.customer_id JOIN order_items oi ON o.order_id = oi.order_id GROUP BY c.customer_name; For customer Aditi Verma, whose two orders (101 totaling 1200.00, 102 totaling 800.00) should sum to 2000.00, this query instead returns 4000.00. Using the e-commerce schema, explain why, and write a corrected query that computes the right total_spent per customer without this inflation.
Asked in
customers4 rows
| customer_id | customer_name | city |
|---|---|---|
| 1 | Aditi Verma | Mumbai |
| 2 | Rohan Gupta | Delhi |
| 3 | Sneha Iyer | Bangalore |
| 4 | Karan Mehta | Mumbai |
orders4 rows
| order_id | customer_id | order_date | total_amount |
|---|---|---|---|
| 101 | 1 | 2023-01-05 | 1200.00 |
| 102 | 1 | 2023-02-10 | 800.00 |
| 103 | 2 | 2023-01-20 | 500.00 |
| 104 | 3 | 2023-03-01 | 1200.00 |
order_items6 rows
| item_id | order_id | product_id | quantity | unit_price |
|---|---|---|---|---|
| 1 | 101 | 501 | 2 | 300.00 |
| 2 | 101 | 502 | 1 | 600.00 |
| 3 | 102 | 501 | 1 | 300.00 |
| 4 | 102 | 503 | 1 | 500.00 |
| 5 | 103 | 503 | 1 | 500.00 |
| 6 | 104 | 502 | 2 | 600.00 |
products4 rows
| product_id | product_name | category | price |
|---|---|---|---|
| 501 | Wireless Mouse | Electronics | 300.00 |
| 502 | Mechanical Keyboard | Electronics | 600.00 |
| 503 | Desk Lamp | Home | 500.00 |
| 504 | Notebook Set | Stationery | 150.00 |
Using the same employees table, write a query to find the total salary and average salary (rounded to 2 decimal places) for each department, ordered by total salary descending.
Asked in
employees10 rows
| emp_id | name | department | city | salary | hire_date |
|---|---|---|---|---|---|
| 1 | Alice Sharma | Engineering | Mumbai | 95000 | 2019-03-12 |
| 2 | Bob Iyer | Engineering | Mumbai | 72000 | 2020-07-01 |
| 3 | Chitra Rao | Sales | Delhi | 65000 | 2018-11-20 |
| 4 | David Paul | Sales | Delhi | 58000 | 2021-01-15 |
| 5 | Esha Nair | Engineering | Bangalore | 88000 | 2019-09-05 |
| 6 | Farhan Khan | Marketing | Mumbai | 60000 | 2020-02-28 |
| 7 | Gita Menon | Marketing | Mumbai | 60000 | 2022-04-10 |
| 8 | Harish Verma | Sales | Bangalore | 72000 | 2022-06-18 |
| 9 | Amit Verma | Sales | Delhi | 52000 | 2021-08-01 |
| 10 | Isha Kapoor | Engineering | Mumbai | 95000 | 2023-01-10 |
The query SELECT * FROM customers WHERE email LIKE %@gmail.com OR phone LIKE '%1234'; is slow on a large customers table. Both patterns start with a wildcard, so neither can use a standard B-tree index efficiently. Assuming full-text indexing isn't available, rewrite the schema and query so that at least the email-domain condition becomes sargable (index-usable), and briefly explain why the phone condition still can't be made sargable with a plain B-tree index.
Asked in
customers4 rows
| customer_id | name | phone | |
|---|---|---|---|
| 1 | Asha | asha@gmail.com | 9876541234 |
| 2 | Ravi | ravi@yahoo.com | 9988771234 |
| 3 | Meera | meera@gmail.com | 9123456789 |
| 4 | Kiran | kiran@outlook.com | 9000001234 |
How to use this page: Flipkart rarely asks a question you've never seen — they ask standard patterns with a twist. Master the pattern in the SQL course lessons, and the twist stops mattering.
Keep practising: SQL Basics, Joins, GROUP BY & HAVING and Subqueries cover what most Flipkart SQL rounds test.