Quick Overview

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.

Write PostgreSQL updates with date filters

Company: Circle

Role: Software Engineer

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Online Assessment

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.

Overview: 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.

Update a flight price with correct updated_at timestamp

You work with a PostgreSQL database for a flight booking system. Using the tables below, write a PostgreSQL statement that updates the price of the flight with flight_no = 'PG124' to 375.00 and sets its updated_at to the fixed UTC timestamp '2025-06-01 09:00:00+00'. After performing the update, return the updated row for that flight. Format timestamptz values in the returned row as YYYY-MM-DD HH24:MI:SS+00.

Tables

flights(id INT, flight_no VARCHAR(10), origin VARCHAR(3), destination VARCHAR(3), departure_time TIMESTAMPTZ, price NUMERIC(10,2), updated_at TIMESTAMPTZ)

bookings(id BIGINT, flight_id INT, user_email VARCHAR(255), status VARCHAR(20), booking_created_at TIMESTAMPTZ, updated_at TIMESTAMPTZ)

Hints

  1. Use an UPDATE statement with a WHERE clause targeting flight_no = 'PG124'.
  2. After the UPDATE, run a SELECT to return the modified row.

Upsert a booking with conflict handling on flight and user

Using the same flight booking schema, assume there is a UNIQUE constraint or index on (flight_id, user_email) in the bookings table. Write a PostgreSQL statement that attempts to insert a booking for flight_id = 2 and user_email = 'bob@example.com' with status = 'confirmed', booking_created_at = '2025-05-02 10:00:00+00', and updated_at = '2025-06-01 09:00:00+00'. If a booking for the same (flight_id, user_email) already exists, update its status to 'confirmed' and set updated_at to '2025-06-01 09:00:00+00', but do not change booking_created_at. After performing the upsert, return the resulting row for that user and flight. For this validation schema, implement the upsert with update-then-insert CTEs so the result is executable even without a physical UNIQUE index.

Tables

flights(id INT, flight_no VARCHAR(10), origin VARCHAR(3), destination VARCHAR(3), departure_time TIMESTAMPTZ, price NUMERIC(10,2), updated_at TIMESTAMPTZ)

bookings(id BIGINT, flight_id INT, user_email VARCHAR(255), status VARCHAR(20), booking_created_at TIMESTAMPTZ, updated_at TIMESTAMPTZ)

Hints

  1. Use INSERT ... ON CONFLICT to implement the upsert behavior in PostgreSQL.
  2. In the DO UPDATE clause, reference EXCLUDED.status to take the incoming status while preserving the original booking_created_at.

Select bookings by UTC date range using timestamptz without off-by-one errors

Write a PostgreSQL query that returns all bookings whose booking_created_at falls within the UTC date range from 2025-05-01 inclusive through 2025-05-31 inclusive. Use a half-open timestamptz interval: booking_created_at >= '2025-05-01 00:00:00+00' and booking_created_at < '2025-06-01 00:00:00+00'. Return flight_no, user_email, and booking_created_at formatted in UTC as 'YYYY-MM-DD HH24:MI:SS', ordered by booking_created_at ascending and then booking id.

Tables

flights(id INT, flight_no VARCHAR(10), origin VARCHAR(3), destination VARCHAR(3), departure_time TIMESTAMPTZ, price NUMERIC(10,2), updated_at TIMESTAMPTZ)

bookings(id BIGINT, flight_id INT, user_email VARCHAR(255), status VARCHAR(20), booking_created_at TIMESTAMPTZ, updated_at TIMESTAMPTZ)

Hints

  1. Avoid casting timestamptz to date in the WHERE clause; compare against explicit timestamptz bounds instead.
  2. Use a half-open interval [start, end) with booking_created_at >= start AND booking_created_at < end to avoid missing late-night rows on the last day.

Indexing and EXPLAIN ANALYZE for date-filtered and email-based booking queries

For the same schema, add appropriate indexes to support (a) filtering bookings by booking_created_at in a UTC range like in Question 3, and (b) case-insensitive lookups by user_email. Then, run EXPLAIN ANALYZE on the date-range query from Question 3 to demonstrate index usage. Assume you may create: (1) a B-tree index on bookings(booking_created_at), and (2) a B-tree index on LOWER(user_email) for case-insensitive searches. Write SQL that creates these indexes and then runs EXPLAIN ANALYZE on the date-range query from 2025-05-01 00:00:00+00 (inclusive) to 2025-06-01 00:00:00+00 (exclusive). In an interview, you should also be ready to discuss common PostgreSQL pitfalls such as casting timestamptz to date in WHERE clauses, mixing time zones, using TEXT vs CITEXT for case-insensitive comparisons, and using non-immutable functions in index expressions. For validation, return the simplified QUERY PLAN sketch rows shown in the expected output; include the actual CREATE INDEX and EXPLAIN approach in the explanation.

Tables

flights(id INT, flight_no VARCHAR(10), origin VARCHAR(3), destination VARCHAR(3), departure_time TIMESTAMPTZ, price NUMERIC(10,2), updated_at TIMESTAMPTZ)

bookings(id BIGINT, flight_id INT, user_email VARCHAR(255), status VARCHAR(20), booking_created_at TIMESTAMPTZ, updated_at TIMESTAMPTZ)

Hints

  1. Create simple B-tree indexes on the raw booking_created_at column and on LOWER(user_email) so the planner can use them without wrapping the indexed column in a function in the WHERE clause.
  2. When defining functional indexes, ensure the functions used (like lower(text)) are immutable or stable; avoid non-immutable functions such as now() in index expressions, and avoid casting timestamptz to date in the WHERE clause if you want the booking_created_at index to be used efficiently.

Loading coding console...