Write SQL: sum values ≤ each row’s value

Q: Write SQL: sum values ≤ each row’s value

This is a Data Manipulation (SQL/Python) interview question from Coinbase for Data Scientist roles. View the full question and solution on PracHub.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) 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).

Write SQL: sum values ≤ each row’s value

Comments (0)