Curated interview practice questions with answers. These are practice scenarios, not a verified list of past interview questions.
About PhonePe hiring for SQL roles
PhonePe is an Indian digital payments and financial services company, majority-owned by Walmart, known for UPI payments, insurance and investment distribution, and the Pincode commerce app. Its engineering teams build high-scale payments systems. It hires for software engineers, SDETs, data analysts, data engineers and data scientists.
For SQL roles, this PhonePe practice set focuses most on Window Functions (5), Indexes and Performance (5), Transactions and Advanced SQL (4), Joins (4). It has 2 easy, 21 medium, 7 hard questions, so you can start easy and work up to the harder ones.
Company type
Fintech
Headquarters
Bengaluru, India
Main India offices
Bengaluru, Pune
PhonePe SQL interview process
Online coding assessment
Machine coding round
DSA or problem solving round
Design or hiring manager round
HR round
Typical pattern based on public candidate experiences. Rounds vary by role, team and year.
Solved for PhonePeStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
✓ 3 questions free • Unlock the rest in seconds
01
What is a deadlock and how do databases handle it?
A deadlock occurs when two transactions each hold a lock the other needs, so both wait forever. For example, T1 locks account A and wants B, while T2 locks B and wants A. The database detects the cycle and kills one transaction as the victim, rolling it back with an error. Applications should retry the victim and access resources in a consistent order.
02
Match each employee's salary to a grade using a range table.
SELECT e.name, e.salary, g.grade
FROM employees e
JOIN salary_grades g
ON e.salary BETWEEN g.min_salary AND g.max_salary;
This is a non-equi join. Ensure grade ranges do not overlap, otherwise an employee matches more than one grade and appears twice.
03
Your running total jumps by large amounts on some dates instead of row by row. Why?
With ORDER BY and no explicit frame, the default frame is RANGE UNBOUNDED PRECEDING, which treats all rows with the same order value as peers and includes them together. When several rows share the same date, the running total adds them all at once. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, or add a unique tiebreaker to ORDER BY.
04
You added an index but the query still does a full table scan. Why might that be?
Possible reasons: the predicate is not SARGable because of a function or type conversion, the query returns a large share of rows so a scan is cheaper, the composite index column order does not match the filter, statistics are stale, or OR conditions prevent index use. Check the plan, rewrite the predicate, update statistics, or adjust the index to match the query.
05
What is the difference between OLTP and OLAP databases?
OLTP systems handle many short transactions such as orders and payments, with normalised schemas, row-based storage and focus on fast inserts and updates. OLAP systems support analysis over large history, using denormalised star or snowflake schemas, columnar storage and heavy aggregations. A bank's core system is OLTP, while its data warehouse used for monthly reports is OLAP.
06
What is the difference between FULL OUTER JOIN and UNION of LEFT and RIGHT joins?
A FULL OUTER JOIN returns matched rows plus unmatched rows from both sides in one operation. MySQL does not support it, so it is emulated with a LEFT JOIN UNION a RIGHT JOIN, or LEFT JOIN UNION ALL a RIGHT JOIN filtered to unmatched rows. Using plain UNION removes duplicates but can also remove genuinely repeated rows, so the UNION ALL pattern is safer.
07
A query uses SELECT * on a table with 80 columns. Why is that a problem?
SELECT * fetches every column, increasing disk reads, memory, network transfer and application processing. It prevents covering indexes from being used and can break applications when columns are added or reordered. Select only required columns, which lets the optimiser use narrower indexes and makes the intent clear. SELECT * is acceptable only for quick exploration, not production code.
08
Tell me about a time you automated a manual data task with SQL.
Situation: the operations team spent four hours every Monday merging Excel files for a weekly report. Task: reduce the effort. Action: I loaded the files into staging tables, wrote SQL with joins and aggregations to produce the report, and scheduled it as a job. Result: the report was ready automatically at 8 AM, saving about 16 hours a month and removing copy-paste errors.
09
An interviewer asks for the Nth highest salary in a reusable way. How do you answer?
Use DENSE_RANK so ties share a rank: SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employees) t WHERE r = :n. Alternatively use ORDER BY salary DESC with OFFSET n-1 ROWS FETCH NEXT 1 ROW ONLY on distinct salaries. Mention how ties are handled and that per-department versions add PARTITION BY department_id.
10
An e-commerce app needs products with very different attributes, such as phones and shirts. How do you model them?
Options are a common products table plus category-specific tables (table per type), an entity-attribute-value table, or a JSON or JSONB column for flexible attributes. A practical design keeps shared fields like name, price and category as columns, stores variable attributes in JSONB with indexes on frequently filtered keys, and validates required attributes in the application or with check constraints.
11
Show all dates in a month with sales, including days with zero sales.
WITH days AS (
SELECT generate_series(DATE '2025-01-01',
DATE '2025-01-31', INTERVAL '1 day')::date AS d
)
SELECT d.d, COALESCE(SUM(s.amount), 0) AS sales
FROM days d
LEFT JOIN sales s ON s.sale_date = d.d
GROUP BY d.d
ORDER BY d.d;
This PostgreSQL example uses a date series; other databases use a calendar table.
WITH spend AS (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
)
SELECT customer_id, total,
NTILE(4) OVER (ORDER BY total DESC) AS quartile
FROM spend;
Quartile 1 contains the highest spenders. Use this for targeted offers or loyalty tiers.
13
How would you design a database for an online food delivery app at a high level?
Core tables are users, addresses, restaurants, menu_items, orders, order_items, payments, delivery_partners and deliveries. Orders reference users, restaurants and addresses, order_items reference orders and menu_items with price at order time, and payments and deliveries reference orders. Add status history tables for tracking and indexes on user_id, restaurant_id and created_at for common queries.
14
Find users who logged in on 3 or more consecutive days.
WITH d AS (
SELECT DISTINCT user_id, login_date FROM logins
), g AS (
SELECT user_id, login_date,
login_date - CAST(ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY login_date) AS INT) AS grp
FROM d
)
SELECT user_id, MIN(login_date) AS start_day, COUNT(*) AS days
FROM g GROUP BY user_id, grp
HAVING COUNT(*) >= 3;
Consecutive dates minus row number give the same group value (PostgreSQL date arithmetic).
15
How do you read an EXPLAIN ANALYZE output in PostgreSQL?
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 101;
Read the tree from the innermost node outward. Check node types such as Seq Scan, Index Scan or Hash Join, compare estimated rows with actual rows, and look at actual time per node. Large estimate errors suggest stale statistics, and Seq Scan on a selective filter suggests a missing index.
16
Write a transaction to transfer money between two accounts.
BEGIN;
UPDATE accounts
SET balance = balance - 5000
WHERE account_id = 1 AND balance >= 5000;
UPDATE accounts
SET balance = balance + 5000
WHERE account_id = 2;
COMMIT;
If the first update affects zero rows or any error occurs, the application issues ROLLBACK so neither change is saved.
Isolation levels balance consistency and concurrency. Read Uncommitted allows dirty reads. Read Committed, the default in PostgreSQL and SQL Server, prevents dirty reads but allows non-repeatable reads. Repeatable Read, the default in MySQL InnoDB, prevents non-repeatable reads, and InnoDB also prevents most phantoms with gap locks. Serializable prevents all anomalies but reduces concurrency. Choose based on business correctness needs.
18
An interviewer asks how you would find why a query is slow. What steps do you describe?
First reproduce the issue and measure time. Then check the execution plan for scans, expensive sorts, row estimate errors and join types. Look at indexes on filter and join columns, whether predicates are SARGable, and statistics freshness. Check for blocking or locks from other sessions. Change one thing at a time, measure again, and document the improvement.
19
The dashboard queries hit the production database and slow down the app at month end. What do you propose?
Separate workloads: route reporting to a read replica or data warehouse, pre-aggregate month-end metrics into summary tables refreshed after close, and cache results for dashboards. Tune the heaviest queries with indexes and better predicates. Schedule batch reports outside business hours and set resource limits or query timeouts for reporting users so customer transactions are protected.
20
Find departments where every employee earns above 40000.
SELECT d.department_id, d.name
FROM departments d
WHERE NOT EXISTS (
SELECT 1 FROM employees e
WHERE e.department_id = d.department_id
AND e.salary <= 40000
)
AND EXISTS (
SELECT 1 FROM employees e WHERE e.department_id = d.department_id
);
Double negation expresses for all; the EXISTS excludes empty departments.
21
How would you design data archiving for a table growing by 10 million rows a month?
Partition the table by month so old partitions can be detached or moved to an archive table or cheaper storage without heavy deletes. Define a retention policy with business and compliance teams, for example 24 months online. Keep archived data queryable through a separate schema or warehouse, update indexes and statistics, and automate the process with monitoring and alerts.
22
A LEFT JOIN is returning fewer rows than the left table. Why could that happen?
A LEFT JOIN never removes left rows by itself, so a WHERE condition on the right table is likely filtering out rows where it is NULL. For example WHERE o.status = 'Paid' drops customers without orders. Move the condition into the ON clause or add OR o.status IS NULL. Another cause is a later INNER JOIN in the chain that removes rows.
23
What are slowly changing dimensions in data warehousing?
Slowly changing dimensions track changes in dimension attributes over time. Type 1 overwrites old values with no history. Type 2 adds a new row with effective dates and a current flag, preserving full history, such as a customer moving city. Type 3 keeps a previous value column for limited history. Type 2 is most common when reports must reflect attributes as they were at transaction time.
LIKE matches string patterns. The percent sign matches any sequence of characters and the underscore matches exactly one character. name LIKE 'Ra%' finds names starting with Ra, and code LIKE 'A_1' matches A01 or AB1. Leading wildcards such as LIKE '%kumar' prevent index use and are slow on large tables. Case sensitivity depends on the database collation.
25
What is the difference between SUM with CASE and filtering with WHERE?
WHERE removes rows from the entire query, so all aggregates see only filtered data. SUM(CASE WHEN condition THEN amount END) applies the condition to one aggregate only, letting you calculate several conditional totals in one pass, such as paid and refunded amounts per customer. Some databases support the cleaner FILTER clause: SUM(amount) FILTER (WHERE status = 'Paid').
26
Your GROUP BY query returns an error that a column is not in the group by clause. How do you fix it?
Each selected column must be grouped or aggregated. Decide what you need: add the column to GROUP BY if it defines the group, aggregate it with MIN, MAX or STRING_AGG if any or all values are acceptable, or remove it. If you need details of the top row in each group, use a window function such as ROW_NUMBER instead of GROUP BY.
27
Produce region subtotals and a grand total in one query.
SELECT COALESCE(region, 'All Regions') AS region,
COALESCE(city, 'Subtotal') AS city,
SUM(sales) AS sales
FROM store_sales
GROUP BY ROLLUP(region, city)
ORDER BY region, city;
In MySQL use GROUP BY region, city WITH ROLLUP. Use GROUPING() to label totals when real NULLs exist.
WITH m AS (
SELECT DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS revenue
FROM orders GROUP BY 1
)
SELECT month, revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) AS mom_pct
FROM m ORDER BY month;
NULLIF avoids division by zero.
29
A long report query has eight nested subqueries and nobody can maintain it. How would you refactor it?
Break it into CTEs with meaningful names, each producing one business step such as active customers, order totals and returns. Remove repeated logic, standardise filters, and add comments on business rules. Test each step's row count against the original output. If parts are reused across reports, move them into views. Finally compare results and execution plans to ensure nothing changed.
PARTITION BY divides rows into groups and the window function restarts for each group, like per department. ORDER BY defines the order of rows within each partition, needed for ranking, running totals and LAG or LEAD. Without PARTITION BY the whole result is one partition. Without ORDER BY, aggregate windows compute over the entire partition.
Tips to crack the PhonePe SQL interview
Practise machine coding problems and focus on clean, extensible object-oriented design within the time limit.
Understand how UPI transactions work at scale, including idempotency, retries and reconciliation.
For analyst roles, prepare cases on transaction success rates, merchant adoption and fraud detection metrics.
Keep a one-minute story ready for every project on your CV: problem, your part, tools, result.
Practise the hard questions on this page aloud; PhonePe interviewers usually ask follow-ups on your first answer.
Frequently asked questions
How many rounds are there in the PhonePe SQL interview?
Candidates usually go through 5 stages: Online coding assessment; Machine coding round; DSA or problem solving round; Design or hiring manager round; HR round. The exact rounds change with the role, team, location and your experience, so confirm the process with the recruiter.
Which topics should I prepare for the PhonePe SQL interview?
This practice set leans on Window Functions (5), Indexes and Performance (5), Transactions and Advanced SQL (4), Joins (4). Revise those first, then practise explaining one or two of your own projects end to end, because most PhonePe interviewers ask you to walk through real work.
Are these the exact questions asked at PhonePe?
No. These are curated practice questions that match the skills and difficulty usually tested in fintech interviews like PhonePe's. They are not a leaked or verified list of past questions.
Where does PhonePe hire SQL professionals in India?
PhonePe has large teams in Bengaluru, Pune. Openings and locations change often, so check the PhonePe careers page and filter by city and experience.