← Lead Bank Interview Insights

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

Senior
Apr 2026

Summary

System design round at Lead Bank for a software engineer role, focused entirely on a hotel booking system. Two specific scenarios came up that pushed beyond the usual high-level architecture stuff and got into some genuinely tricky edge cases.

Questions Asked (2)

Q1

In a hotel booking system, how would you design the query to 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 took me a second to think through properly.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the data model and constraints, then explain how to use a NOT EXISTS or LEFT JOIN with a count of available days to filter room types that have availability for every day in the range. Emphasize the importance of indexing and handling edge cases like overlapping bookings or partial availability.

Pro tip: Mention that you would consider using a calendar table or generate_series to expand the date range, which simplifies the query and improves performance for large date ranges. Also, discuss how to handle time zones and check-in/check-out times if relevant.

1. Clarify Requirements and Data Model

Ask about the schema: tables for rooms, room types, bookings, and availability. Confirm whether availability is derived from bookings or stored separately, and understand check-in/check-out semantics.

2. Choose an Approach

Decide between using NOT EXISTS with a subquery per day, or aggregating availability counts. Consider performance implications and data volume.

3. Write the Query

Construct the SQL using either a correlated subquery with NOT EXISTS for each date, or a LEFT JOIN with a calendar table and GROUP BY room type HAVING COUNT(available days) = total days.

4. Optimize and Index

Suggest indexes on booking dates and room type IDs. Discuss partitioning or materialized views for large datasets.

5. Test and Validate

Mention testing with edge cases: fully booked days, partial availability, and date ranges spanning months. Validate results against expected outcomes.

Key Points to Mention

  • Use of NOT EXISTS or LEFT JOIN with COUNT to ensure availability for every day.
  • Importance of a calendar table or generate_series to expand date ranges.
  • Indexing strategies on booking dates and room type IDs for performance.
  • Handling of check-in/check-out times and time zones.
  • Consideration of concurrency and real-time availability updates.
  • Trade-offs between query complexity and performance for large date ranges.

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

Q2

During checkout in a booking system, how do you handle the difference between a user who genuinely didn't pay versus a user who did pay but the third-party payment confirmation arrived after your timeout threshold?

System DesignTechnical Trade-offsAPI & Integrations
Author's notes

Probably the most interesting part of the whole interview.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by framing the core challenge as a distributed systems consistency problem: you cannot rely on synchronous confirmation alone. Propose an idempotent, event-driven reconciliation design where the payment gateway's webhook is the source of truth, and your system maintains a pending state with retries and a reconciliation job. Emphasize that the user experience must avoid double-charging or false failure, so you separate payment authorization from booking finalization.

Pro tip: Mention that you would never mark a payment as failed based solely on a timeout; instead, you would keep the booking in a 'pending payment' state and reconcile asynchronously, because in payments, a timeout is not a definitive failure—it's an unknown state.

1. Model the payment lifecycle with explicit states

Define states such as INITIATED, PENDING, AUTHORIZED, CAPTURED, FAILED, and EXPIRED. This prevents conflating a timeout with a definitive failure and allows the system to handle late confirmations.

2. Use webhooks as the source of truth and make them idempotent

Design the payment gateway integration to send asynchronous webhook events for payment success/failure. Ensure your webhook handler is idempotent using a unique payment ID or event ID to avoid duplicate processing.

3. Implement a reconciliation job for unknown states

For payments stuck in PENDING beyond a threshold, run a scheduled job that queries the payment gateway's API to fetch the final status. This handles cases where webhooks are lost or delayed.

4. Decouple booking finalization from payment confirmation

Do not block the user or expire the booking immediately on timeout. Instead, hold the booking in a pending state with a reasonable expiration (e.g., 15 minutes) and only finalize or release based on the reconciled payment status.

5. Handle edge cases and user communication

If the payment is confirmed late, ensure the booking is still honored if within the hold window; if not, initiate a refund. Communicate clearly to the user via email or UI that payment is being verified, avoiding false failure messages.

Key Points to Mention

  • Idempotency keys for payment operations to prevent double charges
  • Webhook retries and exponential backoff with dead-letter queues
  • Reconciliation jobs that poll the payment gateway for pending transactions
  • State machine for payment and booking statuses to avoid inconsistent states
  • Timeout is not failure: treat as unknown and reconcile asynchronously
  • User experience: avoid showing 'payment failed' until confirmed, and handle refunds automatically if booking cannot be honored

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