Coding questions · Coinbase
Write SQL: sum values ≤ each row’s value
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.
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).
Sum of values less than or equal to each row’s value (value-based cumulative sum)
Table numbers
| column | type | constraints |
|---|---|---|
| id | INT | PRIMARY KEY, NOT NULL |
| val | INT | NOT NULL |
Sample data:
| id | val |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 15 |
| 4 | 14 |
| 5 | 13 |
| 6 | 2 |
| 7 | 1 |
Expected output
One row per input row with the cumulative sum of all values in the table that are <= that row's val. Columns: id, val, sum_leq.
| id | sum_leq | val |
|---|---|---|
| 1 | 2 | 1 |
| 2 | 6 | 2 |
| 3 | 48 | 15 |
| 4 | 33 | 14 |
| 5 | 19 | 13 |
| 6 | 6 | 2 |
| 7 | 2 | 1 |
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.