Katkat
Back

Write queries for follows and bookings

MetaData Engineer · Technical Screen
MediumData EngineerTechnical ScreenCoding & Algorithms

Last updated

This problem tests your ability to manage relational data, handle temporal overlaps for booking availability, and derive insights from social graph-like connections using SQL or data manipulation scripts.

Scenario

You are managing a platform that supports two primary features: user 'follows' and property 'bookings'. You are provided with two datasets:

  1. follows table: follower_id, followee_id, created_at
  2. bookings table: user_id, property_id, start_date, end_date

Task 1: Mutual Follows (SQL)

Write a query to identify all pairs of users who follow each other (mutual follows). The output should be a single list of unique pairs represented as (user_a, user_b) where user_a < user_b to avoid duplicates.

Task 2: Booking Availability (Python/Algorithmic)

You are given a list of existing bookings for a specific property. Write a function is_available(existing_bookings, new_start, new_end) that returns True if a requested date range does not overlap with any existing bookings, and False otherwise.

Constraints

  • existing_bookings is a list of tuples: [(start1, end1), (start2, end2), ...]
  • Dates are represented as integers (days since epoch).
  • Assume the range is inclusive of the start date and exclusive of the end date [start, end).

Examples

# Example 1
# existing_bookings = [(10, 20), (30, 40)]
# new_booking = (20, 25)
# Result: True (20 is the start of the new booking, matching the end of the first)

# Example 2
# existing_bookings = [(10, 20), (30, 40)]
# new_booking = (15, 25)
# Result: False (Overlaps with 10-20)

Hints

Hint 1

For the SQL task, perform a self-join on the follows table where `t1.follower_id = t2.followee_id` and `t1.followee_id = t2.follower_id`.

Hint 2

For the booking task, a new interval `[s, e)` overlaps with `[start, end)` if `max(s, start) < min(e, end)`.

Hint 3

Alternatively, check if the new booking starts after an existing one ends, or ends before an existing one starts.

Follow-up questions

  • How would you optimize the booking lookup if you had millions of bookings for a single property?
  • If the 'follows' table is extremely large, how would you partition the data to make the mutual follow query faster?
  • How would you handle a scenario where 'follows' can be deleted, and you need to keep track of the most recent 'active' relationship?