Write SQL: sum values ≤ each row’s value
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.
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).