Write SQL: sum values ≤ each row’s value
Company: Coinbase
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Given table numbers(id INT PRIMARY KEY, val INT), return for every row the sum of all val across the entire table that are ≤ that row’s val (i.e., compare against values, not row order). Aim for an approach that avoids O(n²) correlated subqueries on large tables.
Sample data:
numbers
id | val
1 | 1
2 | 2
3 | 15
4 | 14
5 | 13
6 | 2
7 | 1
Requirements:
- Output columns: id, val, sum_leq.
- Ties count fully (e.g., for val=2, include all rows with val 1 or 2).
- Provide an ANSI-SQL solution that scales (hint: pre-aggregate by val, then use a cumulative sum over distinct val and join back).
Overview: This question evaluates proficiency in SQL aggregation, query optimization, and handling duplicate values when computing cumulative sums across rows. It is commonly asked to assess practical ability in the Data Manipulation (SQL/Python) domain to design scalable, efficient queries and demonstrates a focus on practical application of query-design and optimization rather than purely conceptual theory.
Read the full Coinbase Data Scientist interview experience this question came from
You are given a table numbers(id INT PRIMARY KEY, val INT). For every row, return the sum of all val across the entire table that are less than or equal to that row’s val (compare against values, not row order).
Requirements:
- Output columns: id, val, sum_leq.
- Ties count fully (e.g., for val = 2, include all rows with val 1 or 2).
- Write an ANSI-SQL solution that scales well on large tables (avoid O(n^2) correlated subqueries). Hint: pre-aggregate by val, compute a cumulative sum over distinct val, then join back to the base table.
Tables
numbers(id INT, val INT)
Hints
- Aggregate first by val to reduce the number of rows the window function processes.
- Compute a running (cumulative) sum ordered by val, then join the result back to the original table on val.