These independent assignments use synthetic data. Try them first, then check edge cases and open the hint. Time is an estimate; allow additional time for project tests and documentation.
Choose your practice · Interview mistakes
Scroll the table horizontally →
| Assignment | Stage | Estimate |
|---|---|---|
| Missing kitchen ticket numbers | SQL and PostgreSQL | 30 min |
| Menu price history | SQL and PostgreSQL | 45 min |
| Available seats by room | SQL and PostgreSQL | 35 min |
| Latest bookings with a stable cursor | SQL and PostgreSQL | 45 min |
Missing kitchen ticket numbers
The ticket_events(event_id, ticket_no) table contains ticket numbers 201, 202, 202, 204, 207. Find every missing number in the explicitly supplied inclusive range 201…207, ascending: 203, 205, 206. Repeated tickets must not create false gaps. Ignore values outside the range; for an empty table return the entire range. Write one PostgreSQL query.
Check your result
- Cover leading, trailing and consecutive gaps.
- For 201…201, only the presence of 201 matters.
- Explain why table MIN/MAX cannot replace the supplied boundaries.
Hint — after your attempt
Compare the expected number set with existing numbers, accounting for duplicates.
Extension: Return missing ranges and compare query cost for a million-number interval.
Menu price history
Design dishes and a per-branch price history. A dish price in integer minor units applies over [valid_from, valid_to); the end may be NULL. Prices are nonnegative. Preserve history when a dish is archived. Two prices for the same dish and branch must never overlap. Write DDL, an as-of price query and a price-change procedure that keeps the timeline continuous. Explain protection against concurrent changes.
Check your result
- The database rejects nonexistent dishes and negative prices.
- At a shared interval boundary, select exactly the new price.
- A two-transaction test cannot create overlapping active prices.
Hint — after your attempt
A UNIQUE constraint on interval starts alone does not prevent overlap.
Extension: Add currency and prevent mixed currencies within one timeline.
Available seats by room
Use rooms(id, capacity) and reservations(id, room_id, guests, status). Rooms: (1, 12), (2, 4), (3, 3), (4, 6). Reservations: room 1 has confirmed bookings for 4 and 3 guests; room 2 has a cancelled booking for 3; room 3 has a confirmed booking for 3. Return every room and available seats, counting confirmed bookings only. Sort by available seats descending, then id ascending. Expected: (4,6), (1,5), (2,4), (3,0). Keep negative availability visible for diagnosis.
Check your result
- A room without bookings stays in the result.
- Cancelled bookings neither occupy seats nor remove the room.
- Joining a guest table must not accidentally multiply booking totals.
Hint — after your attempt
With LEFT JOIN, status-filter placement affects whether empty rooms survive.
Extension: Compare an exact primary-database query with a cached report allowed to lag by 30 seconds.
Latest bookings with a stable cursor
The reservations(id, restaurant_id, created_at, status) table has 20 million rows. Return the latest 25 confirmed bookings for one restaurant, ordered by (created_at DESC, id DESC). Propose first-page and next-page queries, an index and a measurement plan. The cursor includes both values from the last row. Consider equal timestamps, a new booking inserted between requests and an older row changing status. One immutable snapshot across separate HTTP requests is not required.
Check your result
- Equal timestamps have a stable id tie-breaker.
- The next page does not repeat the previous page’s last row.
- Justify the index through filters and ordering; report actual rows, time and buffers.
Hint — after your attempt
A timestamp-only cursor can skip rows sharing that timestamp.
Extension: Define a different contract if the report requires one fixed snapshot across every page.