Quick Overview

This question evaluates proficiency in data validation, numeric robustness, error handling, aggregation, per-item cost computation and deterministic sorting within Python-based data manipulation tasks, and it belongs to the Data Manipulation (SQL/Python) domain.

Compute costs with validation and sorting in Python

Company: Stripe

Role: Software Engineer

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

Implement a three-part Python task to compute costs for purchase line items. Part 1: Write compute_cost(line_items, price_db) where line_items is a list of dicts like {"product_id": str, "qty": int} and price_db is a dict mapping product_id -> unit_price (float). Return (total_cost, breakdown) where breakdown lists per-item cost. Clarify and implement behavior when a product_id is missing from price_db (e.g., raise, skip with warning, or default). Part 2: Add robust validation: quantities/prices must be numeric; qty must be nonnegative; reject NaN/inf; detect and report invalid rows with clear errors. Include tests for empty input, large values, and duplicate product_ids (define whether to sum or treat as separate lines). Part 3: If sort=True, return breakdown sorted by per-item cost descending using a lambda key; otherwise preserve input order. Ensure outputs match expected results and document rounding rules.

Overview: This question evaluates proficiency in data validation, numeric robustness, error handling, aggregation, per-item cost computation and deterministic sorting within Python-based data manipulation tasks, and it belongs to the Data Manipulation (SQL/Python) domain.

Read the full Stripe Software Engineer interview experience this question came from

Compute line-item and total purchase cost

You are given two tables: products with unit prices, and line_items representing a shopping cart. Write a query to compute, for each line item that has a matching product, its unit price, its line cost (qty * unit_price), and the overall total cost across all such line items. Requirements: - Treat the products table as the price database. - Only include line_items whose product_id exists in products (i.e., skip missing product_ids by using an inner join). - Do not perform any additional validation (negative quantities and invalid prices are allowed in this part). - Return one row per joined line item, with a window column total_cost that repeats the overall sum on every row. - Order the result by line_id ascending.

Tables

products(product_id VARCHAR(10), unit_price DECIMAL(10,2))

line_items(line_id INT, product_id VARCHAR(10), qty INT)

Hints

  1. Start by joining line_items to products on product_id to get the unit_price for each line.
  2. Use a window function SUM(...) OVER() to compute the overall total_cost and repeat it on every row.

Validate line items and report invalid rows

Using the same products and line_items tables, write a query that performs robust validation of each line item and reports invalid rows with clear error messages. Validation rules: - A line is INVALID if its product_id does not exist in products. - A line is INVALID if qty is NULL or negative. - A line is INVALID if unit_price is NULL or negative. - Otherwise the line is VALID. For every line_items row, return: - line_id, product_id, qty, unit_price - validity_status: 'VALID' or 'INVALID' - error_reason: a short text such as 'Unknown product_id', 'Missing qty', 'Negative qty', 'Missing unit_price', or 'Negative unit_price' (pick the first applicable reason in that priority order) - line_cost: qty * unit_price for VALID rows, and NULL for INVALID rows. Treat duplicate product_ids across different line_ids as separate lines; you do not need to aggregate them here. Order the result by line_id ascending.

Tables

products(product_id VARCHAR(10), unit_price DECIMAL(10,2))

line_items(line_id INT, product_id VARCHAR(10), qty INT)

Hints

  1. Use a LEFT JOIN from line_items to products so that you can detect unknown product_ids as NULLs on the products side.
  2. Implement the validation logic with CASE expressions to derive both validity_status and a human-readable error_reason.

Sort validated line items by cost with a flag

Using the same products and line_items tables plus a query_params table, return a cost breakdown of only VALID line items (as defined in Question 2) and optionally sort the breakdown by per-item cost. Definitions for this query: - A line is VALID if: - product_id exists in products (inner join succeeds), and - qty is non-negative (qty >= 0), and - unit_price is not NULL and non-negative (unit_price >= 0). Requirements: - Return line_id, product_id, qty, unit_price, and line_cost (qty * unit_price) for only VALID lines. - The query_params table contains a single row with a column sort_flag (0 or 1). - If sort_flag = 1, sort the breakdown by line_cost in descending order. - If sort_flag = 0, preserve the original input order, which is represented by line_id ascending. - Implement the conditional ordering logic in a single query using ORDER BY; do not write two separate queries. - Round line_cost to 2 decimal places (use DECIMAL(10,2) arithmetic). Assume the provided sample_data has sort_flag = 1, so the sample output should be sorted by line_cost descending.

Tables

products(product_id VARCHAR(10), unit_price DECIMAL(10,2))

line_items(line_id INT, product_id VARCHAR(10), qty INT)

query_params(sort_flag INT)

Hints

  1. Filter to VALID rows using a WHERE clause that encodes the same rules from the validation step (inner join plus non-negative qty and unit_price).
  2. To switch between sorted and original order in a single query, join to query_params and use CASE expressions inside ORDER BY based on sort_flag.

Loading coding console...