Compute delivery metrics and top-K queries

Quick Overview

Compute delivery metrics and top-K queries evaluates SQL or pandas logic, joins, grouping, window functions, null handling, edge cases, and validation in a realistic interview setting. A strong answer states assumptions, handles edge cases, explains trade-offs, and shows how to validate the result clearly.

Compute delivery metrics and top-K queries

Company: Coinbase

Role: Software Engineer

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Onsite

You have restaurant menus and orders for a food delivery platform. Part 1: Given a user location and a set of restaurants (each with its menu items and prices), return the restaurant offering the lowest total price for a specified basket and the nearest such restaurant if there are ties; define distance computation assumptions. Part 2: Given a stream of orders with timestamps and item-level prices, compute over a time window the total revenue, order count, and average order value; support multiple overlapping windows efficiently. Part 3: Over a time window, return the Top-K orders by total price and the Top-K items by units sold; design data structures/algorithms to handle updates in real time (e.g., heaps, hash maps) and discuss complexity and tie-breaking. Implement clean function signatures and minimal tests.

Quick Answer: Compute delivery metrics and top-K queries evaluates SQL or pandas logic, joins, grouping, window functions, null handling, edge cases, and validation in a realistic interview setting. A strong answer states assumptions, handles edge cases, explains trade-offs, and shows how to validate the result clearly.

Solution

# Solution Alignment The prompt asks for an implementation-level answer. The safest way to present it is to define the state, maintain clear invariants, then walk through complexity and tests. ## Problem Restatement You have restaurant menus and orders for a food delivery platform. Part 1: Given a user location and a set of restaurants (each with its menu items and prices), return the restaurant offering the lowest total price for a specified basket and the nearest such restaurant if there are ties; define distance computation assumptions. Part 2: Given a stream of orders with timestamps and item-level prices, compute over a time window the total revenue, order count, and average order value; support multiple overlapping windows efficiently. Part 3: Over a time window, return the Top-K orders by total price and the Top-K items by units sold; design data structures/algorithms to handle updates in real ti... ## Recommended Approach For one-time top-K, use a size-K min-heap or quickselect plus sorting the selected K. For streaming windows, maintain counts in a hash map plus a heap with lazy deletion or bucketed frequency structures when updates must be near O(1). Define deterministic tie-breaking. ## Correctness The implementation should maintain an invariant after each loop or operation that directly matches the problem statement. At termination, that invariant implies the returned value has considered every valid candidate exactly once, or has preserved the required data-structure state after every API call. ## Complexity One-time heap: O(n log k) time and O(k) space. Quickselect: expected O(n) plus O(k log k) to order output. Streaming complexity depends on window eviction and tie-breaking. ## Edge Cases and Tests k = 0, k > n, duplicate values, ties, negative values, stale heap entries, and deterministic output ordering.
|Home/Data Manipulation (SQL/Python)/Coinbase
Coinbase logo
Coinbase
Jul 31, 2025, 12:00 AM
mediumSoftware EngineerOnsiteData Manipulation (SQL/Python)
10
0

Compute delivery metrics and top-K queries

You have restaurant menus and orders for a food delivery platform. Part 1: Given a user location and a set of restaurants (each with its menu items and prices), return the restaurant offering the lowest total price for a specified basket and the nearest such restaurant if there are ties; define distance computation assumptions. Part 2: Given a stream of orders with timestamps and item-level prices, compute over a time window the total revenue, order count, and average order value; support multiple overlapping windows efficiently. Part 3: Over a time window, return the Top-K orders by total price and the Top-K items by units sold; design data structures/algorithms to handle updates in real time (e.g., heaps, hash maps) and discuss complexity and tie-breaking. Implement clean function signatures and minimal tests.

Clarifying Questions to Ask Guidance

  • Clarify SQL dialect or Python library versions, date/time semantics, duplicate handling, and null handling.
  • Define the grain of each intermediate result before aggregating.
  • State expected output columns and ordering explicitly.

What a Strong Answer Covers Guidance

  • A query or pandas plan that matches the requested output grain.
  • Correct joins, filters, grouping, window functions, and treatment of NULLs or duplicates.
  • A brief explanation of why the result is correct and how it handles edge cases.
  • Performance notes, indexes/partitioning, and validation queries when relevant.

Follow-up Questions Guidance

  • How would you test the query on a tiny hand-built dataset?
  • What changes if duplicate events or late-arriving data are present?
  • Which indexes, clustering, or partitions would help at production scale?
Loading comments...