Write SQL: sum values ≤ each row’s value

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at Coinbase.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Coinbase during technical interviews.

Question

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).

PracHub · Accepted Answer

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.

Quick Overview

Write SQL: sum values ≤ each row’s value