Interview Q&A

Short answer: Database design patterns are general solutions to recurring problems that arise in database schema design. Some common patterns include:

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: The CAP theorem (Consistency, Availability, Partition Tolerance) states that in a distributed database system, it is impossible to simultaneously guarantee all three of the following:

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Eventual consistency is a consistency model used in distributed systems where updates to data will propagate and eventually become consistent across all nodes, but not necessarily immediately.

Explain a bit more

How it works: In an eventually consistent system, updates are made to a node and eventually, that update will be propagated to all other nodes. It allows for temporary inconsistencies, but ensures that the system will converge to a consistent state over time. Use cases: Suitable for systems that can tolerate a delay in consistency, like NoSQL databases (Cassandra, DynamoDB) and systems dealing with high availability and massive scale.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Treat HR as a partner who must balance budget, internal parity, and candidate closure timelines. When you understand these constraints, your ask becomes easier to approve. Lead with business outcomes and role fit, then discuss compensation structure logically.

Step-by-step approach

1. Ask HR how compensation is split across fixed, variable, bonus, and long-term benefits.
2. Position your request around role scope, risk ownership, and measurable delivery record.
3. Use a calm, data-backed narrative instead of competitive pressure language.
4. Negotiate sequence: fixed pay first, then one-time bonuses, then review timeline.
5. If numbers are frozen, request an early performance review clause in writing.
6. Confirm final details by email to avoid misalignment before offer acceptance.

Real-world example

Neha was interviewing for a platform lead role at Razorpay while employed at Flipkart. Instead of saying "another company is paying more," she explained that she would own migration risk and 24x7 availability for critical services. Arjun from Zoho helped her rewrite her talking points around business continuity and release stability. HR could not change total CTC much, but increased fixed pay and added a 6-month review commitment, which Neha accepted.

Mistakes to avoid

• Speaking aggressively and turning negotiation into confrontation.
• Skipping structure details and focusing only on total CTC headline.
• Assuming HR can always revise numbers without policy limits.
• Accepting verbal promises without written confirmation.

Follow-up questions you may get

• If HR says the budget is frozen, ask: "Can we agree on a written review checkpoint after 6 months based on defined KPIs?"

Negotiate in layers: fixed, bonus, then review cycle.

Short answer: A convincing justification links your compensation ask to measurable business value and future scope. Replace statements like "I worked hard" with clear evidence such as uptime, cost savings, delivery speed, or customer impact. Decision-makers approve hikes faster when your story is quantifiable and role-aligned.

Step-by-step approach

1. Build a value dossier with metrics from at least 3 projects you led or stabilized.
2. Connect each metric to one business outcome, such as revenue protection or support cost reduction.
3. Show benchmark parity by comparing your role-level compensation with market data.
4. Present your ask as a fair correction, not an emotional demand.
5. Suggest a timeline for decision and offer to share additional details if needed.
6. If immediate raise is not possible, negotiate milestone-linked revision checkpoints.

Real-world example

Arjun at Zoho wanted a correction after taking on architecture ownership that was previously handled by two senior engineers. He documented migration completion, outage reduction, and faster release cycles over six months. Priya from TCS reviewed his note and advised him to align metrics with team-level business outcomes. HR approved a staged hike with a confirmed review in the next appraisal cycle.

Mistakes to avoid

• Making comparisons with colleagues instead of market and role expectations.
• Using generic adjectives like "excellent" without hard evidence.
• Submitting a hike request without showing expanded responsibility.
• Escalating emotionally when manager asks for data.

Your best argument is a measurable before-versus-after story.

Short answer: You can share current salary selectively, but do not let it become the only anchor. Redirect the conversation toward market value and role scope so your future compensation reflects the new responsibility. If disclosure is mandatory by policy, share accurate numbers with full breakup context.

Step-by-step approach

1. Ask politely whether current salary disclosure is mandatory for this hiring process.
2. If asked, share complete structure: fixed, variable, bonus, and one-time payouts.
3. Immediately re-anchor discussion on expected range tied to new role scope.
4. Explain differences between current role complexity and upcoming role expectations.
5. Avoid inflating numbers; any mismatch can fail background checks later.
6. Document disclosure and expected range clearly over email.

Real-world example

Rahul was interviewing at Swiggy while working at TCS and was asked to share current compensation early. He provided the exact breakup and clarified that a large part was one-time retention payout, not recurring income. Karthik from Zoho advised him to pivot the discussion to the new platform ownership scope. The recruiter accepted his reasoning and evaluated him against role band, not his old fixed salary.

Mistakes to avoid

• Sharing only total CTC and hiding non-recurring components.
• Refusing abruptly without understanding recruiter policy constraints.
• Inflating numbers and risking verification failure.
• Letting old salary become permanent anchor for new role.

Disclose honestly, then re-anchor to market and scope.

Short answer: Talk about DMVs: sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_os_waiting_tasks, and reading plans. For query writing interviews, also discuss indexes, sargability, and avoiding SELECT *.

Sample solution

SELECT r.session_id, r.status, r.command, r.wait_type, r.blocking_session_id,
       t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id <> @@SPID;

Even app developers score points naming DMVs and “parameter sniffing” briefly.

Short answer: Work & Office decisions become easier when you prepare evidence, propose options, and communicate clearly. A structured approach reduces uncertainty and leads to better outcomes. Keep your plan practical and well documented from start to finish.

Step-by-step approach

1. Define your target outcome and constraints before taking action.
2. Collect relevant data points from policy, market, and stakeholder inputs.
3. Build a practical execution plan with milestones and fallback options.
4. Communicate clearly and confirm all decisions in writing.
5. Review results and refine your approach for the next cycle.

Real-world example

Neha was working at Flipkart and needed to handle this situation: how to deal with a toxic manager. She prepared a clear plan with timelines, ownership, and expected outcomes before speaking to HR and her manager. Arjun, who had recently moved to Zoho, reviewed her approach and helped her tighten the messaging with measurable results. Within a few weeks, Neha achieved a better career outcome while preserving strong professional relationships.

Mistakes to avoid

• Acting without understanding policy, market context, or role expectations.
• Using generic claims instead of measurable evidence and concrete examples.
• Delaying communication and creating last-minute pressure for stakeholders.
• Skipping rehearsal, which causes weak delivery during interviews or negotiations.

Capture major decisions in writing to avoid confusion and future disputes.

Short answer: Work & Office decisions become easier when you prepare evidence, propose options, and communicate clearly. A structured approach reduces uncertainty and leads to better outcomes. Keep your plan practical and well documented from start to finish.

Step-by-step approach

1. Define your target outcome and constraints before taking action.
2. Collect relevant data points from policy, market, and stakeholder inputs.
3. Build a practical execution plan with milestones and fallback options.
4. Communicate clearly and confirm all decisions in writing.
5. Review results and refine your approach for the next cycle.

Real-world example

Priya was working at Zoho and needed to handle this situation: how to manage work-life balance. She prepared a clear plan with timelines, ownership, and expected outcomes before speaking to HR and her manager. Rahul, who had recently moved to TCS, reviewed her approach and helped her tighten the messaging with measurable results. Within a few weeks, Priya achieved a better career outcome while preserving strong professional relationships.

Mistakes to avoid

• Acting without understanding policy, market context, or role expectations.
• Using generic claims instead of measurable evidence and concrete examples.
• Delaying communication and creating last-minute pressure for stakeholders.
• Skipping rehearsal, which causes weak delivery during interviews or negotiations.

Capture major decisions in writing to avoid confusion and future disputes.

Short answer: Work & Office decisions become easier when you prepare evidence, propose options, and communicate clearly. A structured approach reduces uncertainty and leads to better outcomes. Keep your plan practical and well documented from start to finish.

Step-by-step approach

1. Define your target outcome and constraints before taking action.
2. Collect relevant data points from policy, market, and stakeholder inputs.
3. Build a practical execution plan with milestones and fallback options.
4. Communicate clearly and confirm all decisions in writing.
5. Review results and refine your approach for the next cycle.

Real-world example

Karan was working at TCS and needed to handle this situation: how to handle office conflicts. She prepared a clear plan with timelines, ownership, and expected outcomes before speaking to HR and her manager. Isha, who had recently moved to Razorpay, reviewed her approach and helped her tighten the messaging with measurable results. Within a few weeks, Karan achieved a better career outcome while preserving strong professional relationships.

Mistakes to avoid

• Acting without understanding policy, market context, or role expectations.
• Using generic claims instead of measurable evidence and concrete examples.
• Delaying communication and creating last-minute pressure for stakeholders.
• Skipping rehearsal, which causes weak delivery during interviews or negotiations.

Capture major decisions in writing to avoid confusion and future disputes.

Short answer: ROLLUP creates subtotals and a grand total. GROUPING SETS lists exact aggregation levels. GROUPING(col) detects total rows (NULLs that mean “all”).

Sample solution

SELECT DepartmentId, JobTitle, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY ROLLUP (DepartmentId, JobTitle);

Use GROUPING() to distinguish real NULL dimension values from rollup NULLs.

Short answer: A covering index includes all columns a query needs (key + INCLUDE), enabling an Index Seek/Scan without Key Lookup. In plans, look for absence of Key Lookup and lower estimated cost.

Offer: CREATE INDEX ... INCLUDE (ColA, ColB) as the practical answer.

Short answer: Use AVG(Amount) OVER (ORDER BY OrderDate ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).

Sample solution

SELECT OrderDate, Amount,
       AVG(Amount * 1.0) OVER (
           ORDER BY OrderDate
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS MovingAvg7
FROM DailySales;

ROWS vs RANGE matters when OrderDate has duplicates.

Short answer: Two ranges [a,b] and [c,d] overlap when a <= d AND c <= b (for closed intervals). Self-join bookings/events with that predicate and exclude the same Id.

Sample solution

SELECT a.Id AS Id1, b.Id AS Id2
FROM Bookings a
INNER JOIN Bookings b ON a.Id < b.Id
    AND a.StartDate <= b.EndDate
    AND b.StartDate <= a.EndDate;

Clarify inclusive/exclusive end times — hotel checkout logic often uses half-open intervals.