← Lead Bank Interview Insights

Lead Bank·Software Engineer·Onsite - System Design / Architecture·Senior

SeniorPrefer not to say
Jun 2026

Summary

System design round at Lead Bank for a software engineer role, focused entirely on a hotel booking system. Two specific deep-dives came up that I wasn't fully prepared for, and one of them genuinely stumped me for a moment.

Questions Asked (2)

Q1

In a hotel booking system, how would you query and return only room types that are available across every single day in a requested date range?

System DesignData ModelingAlgorithms & Data Structures
Author's notes

This one tripped me up more than I expected.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Clarify the data model and availability semantics first, then propose a query that counts distinct available days per room type and compares it to the total days in the requested range. Discuss indexing and performance considerations for large-scale booking systems.

Pro tip: Mention that availability is often derived from booking and inventory tables, and that using a calendar table or generating a date series can simplify the query and improve performance. Also, consider time zones and check-in/check-out boundaries to avoid off-by-one errors.

1. Clarify Requirements and Data Model

Ask about the schema: tables for rooms, room types, bookings, and availability. Confirm whether availability is stored explicitly or derived from bookings and inventory.

2. Define Availability Logic

Determine what 'available' means: no overlapping bookings, sufficient inventory, or a combination. Consider check-in/check-out dates and time zones.

3. Design the Query Strategy

Use a date range generator (e.g., recursive CTE or calendar table) to list all dates. For each room type, count distinct dates where it has availability, and filter those with count equal to the total days.

4. Write the SQL Query

Construct a query with LEFT JOINs or NOT EXISTS to find available room types per date, then aggregate and filter. Use GROUP BY and HAVING COUNT(DISTINCT date) = total_days.

5. Optimize and Discuss Scalability

Suggest indexes on date columns and room_type_id. Mention partitioning, caching, or pre-aggregated availability tables for high-traffic systems.

Key Points to Mention

  • Use of a calendar table or recursive CTE to generate all dates in the range.
  • Counting distinct available days per room type and comparing to total days.
  • Handling check-in/check-out boundaries and time zones correctly.
  • Indexing strategies on date and room_type_id for performance.
  • Alternative approaches like pre-aggregated availability or materialized views.
  • Consideration of inventory levels and overbooking policies.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Q2

At checkout in a hotel booking system, how do you differentiate between a user who genuinely didn't pay versus a user who paid but whose third-party payment confirmation arrived after your timeout threshold?

System DesignTechnical Trade-offsAPI & Integrations
Author's notes

This was the harder one.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by acknowledging the core challenge: network timeouts create uncertainty, so you cannot rely solely on synchronous responses. Propose an idempotent, event-driven reconciliation system that treats payment status as eventually consistent, using webhooks, polling, and a state machine to resolve ambiguous cases.

Pro tip: Emphasize that you never mark a payment as failed until you've exhausted reconciliation attempts; instead, use a 'pending' state and communicate clearly to the user. This shows you prioritize correctness and user trust over premature conclusions.

1. Identify the ambiguity

Explain that a timeout does not mean failure; it means the outcome is unknown. The system must handle three states: paid, unpaid, and unknown.

2. Design for idempotency and reconciliation

Use idempotent operations and a reconciliation process that queries the payment provider (via webhooks or polling) to determine the true status, even after timeout.

3. Implement a state machine

Model the payment lifecycle with states like PENDING, PAID, FAILED, and REFUNDED. Transitions occur only upon confirmed events, not timeouts.

4. Handle user experience

For pending payments, show a 'processing' message and avoid double-charging. Use idempotency keys to prevent duplicate charges if the user retries.

5. Monitor and alert

Track reconciliation metrics and set up alerts for discrepancies to quickly address systemic issues.

Key Points to Mention

  • Idempotency keys to prevent duplicate charges on retries
  • Webhooks and polling for asynchronous payment confirmation
  • Eventual consistency and reconciliation jobs
  • State machine for payment lifecycle (e.g., PENDING, PAID, FAILED)
  • User communication: pending state and clear messaging
  • Timeout handling: never assume failure; always verify

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.