Write PostgreSQL updates with date filters

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Take-home Project rounds at Circle.

Q: What role is this question designed for?

This question is commonly asked for Software Engineer candidates at Circle during technical interviews.

Question

Write PostgreSQL statements to:
(
1) update a flight's price and set updated_at correctly;
(
2) upsert a booking record with conflict handling;
(
3) select bookings filtered by a UTC date range using timestamptz without off-by-one errors;
(
4) add appropriate indexes and demonstrate their use with EXPLAIN ANALYZE. Discuss common pitfalls when moving from generic SQL to PostgreSQL, including date/time casting, time zones, text vs citext, and use of immutable/stable functions in indexes.

PracHub · Accepted Answer

This question evaluates competency in PostgreSQL data manipulation, covering updates with correct timestamp handling, upserts with conflict resolution, date-range selection using timestamptz, and index design plus performance analysis with EXPLAIN ANALYZE.

Quick Overview

Write PostgreSQL updates with date filters