Nerd Level Tech logo
Back to Course|Backend Engineer Interviews: Databases, APIs & Distributed Systems Mastery
Lab

Database Design Challenge: Ride-Sharing Platform

25 min
Intermediate
3 Free Attempts

Instructions

Database Design Challenge: Ride-Sharing Platform

You are designing the database for a ride-sharing platform similar to Uber or Lyft. Your task covers four areas: schema design, SQL queries, index selection, and transaction safety.

Part 1: Schema Design (25 points)

Design a complete relational schema for a ride-sharing platform with the following entities:

  • Users — passengers and drivers share the same user table with a role field
  • Driver Profiles — license number, vehicle info, verification status, current location
  • Trips — pickup/dropoff locations, timestamps, fare, status (requested, accepted, in_progress, completed, cancelled)
  • Payments — amount, method (card, wallet), status (pending, completed, refunded), linked to trip
  • Ratings — rider rates driver and driver rates rider after each trip (1-5 stars, optional comment)

Requirements:

  • Use appropriate data types (BIGSERIAL for IDs, NUMERIC for money, TIMESTAMPTZ for times, POINT or DOUBLE PRECISION for coordinates)
  • Include all necessary constraints (PRIMARY KEY, FOREIGN KEY, NOT NULL, CHECK, UNIQUE, DEFAULT)
  • Add at least one denormalized column with justification

Part 2: SQL Queries (30 points)

Write the following queries:

  1. Top drivers by rating with pagination — Find the top 10 drivers ranked by average rating who have completed at least 50 trips. Use a window function (RANK or DENSE_RANK). Return driver name, average rating, total trips, and rank. Support pagination by accepting an offset.

  2. Trip analytics — Write a query that returns daily trip statistics for the last 30 days: date, total trips, total revenue, average fare, and the percentage change in revenue compared to the previous day (use LAG window function).

  3. Surge pricing candidates — Find geographic areas (rounded to 2 decimal places of latitude/longitude) where the ratio of trip requests to available drivers exceeds 3:1 in the current hour.

Part 3: Index Selection (20 points)

For your schema, propose at least 4 indexes. For each index:

  • Write the CREATE INDEX statement
  • Identify which query or access pattern it optimizes
  • Explain why you chose this index type (B-tree, Hash, GIN, composite, covering)
  • Explain the column ordering for composite indexes

Part 4: Transaction Safety (25 points)

Write a deadlock-free transaction for processing a trip payment that:

  1. Verifies the trip status is 'completed' and payment is still 'pending'
  2. Charges the rider's wallet (deduct balance)
  3. Credits the driver's wallet (add balance minus platform commission of 20%)
  4. Updates the payment status to 'completed'
  5. All of the above must be atomic — if any step fails, nothing changes

Requirements:

  • Use explicit locking (SELECT ... FOR UPDATE) with consistent ordering to prevent deadlocks
  • Include proper error handling (check sufficient balance)
  • Specify the isolation level and explain why you chose it

What to Submit

Your submission should contain 1 file section in the editor below: a complete SQL file with all 4 parts.

Grading Rubric

Schema Design: All 5 tables (users, driver_profiles, trips, payments, ratings) created with correct relationships, appropriate data types (BIGSERIAL, NUMERIC, TIMESTAMPTZ, DOUBLE PRECISION), complete constraints (PK, FK, NOT NULL, CHECK, UNIQUE), and at least one justified denormalized column25 points
SQL Queries: Query 1 uses RANK()/DENSE_RANK() with HAVING for 50+ trips and OFFSET pagination. Query 2 groups by date for 30 days with LAG() for revenue change percentage. Query 3 uses ROUND() for area grouping with division-by-zero protection and current hour filter.30 points
Index Selection: At least 4 indexes with valid CREATE INDEX syntax, each justified with a specific query/access pattern, correct index type selection (B-tree, composite, covering), and column ordering following leftmost prefix rule20 points
Transaction Safety: Correct BEGIN/COMMIT/ROLLBACK with isolation level, trip/payment status verification, consistent lock ordering (SELECT FOR UPDATE by ascending ID), correct 20% platform fee calculation, and proper error handling with balance checks25 points

Checklist

0/4

Your Solution

3 free attempts remaining