clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 648391 fiddles created (15115 in the last week).

CREATE TABLE sourcetable (id INT, price INT, volume INT)
 hidden batch(es)


INSERT INTO sourcetable WITH RECURSIVE cte AS ( SELECT 1 price, 1 volume UNION ALL SELECT price+1, price*price FROM cte WHERE price < 10 ) SELECT price, price, volume FROM cte
 hidden batch(es)


SELECT * FROM sourcetable
id price volume
1 1 1
2 2 1
3 3 4
4 4 9
5 5 16
6 6 25
7 7 36
8 8 49
9 9 64
10 10 81
 hidden batch(es)


WITH cte AS ( SELECT price, SUM(volume) OVER (ORDER BY price ASC) summ FROM sourcetable ) SELECT * FROM cte ORDER BY price -- total = 286 -- records below 10% = 28.6 are 1..4 -- their average price = 2.5
price summ
1 1
2 2
3 6
4 15
5 31
6 56
7 92
8 141
9 205
10 286
 hidden batch(es)


WITH cte AS ( SELECT price, SUM(volume) OVER (ORDER BY price ASC) summ FROM sourcetable ) SELECT AVG(price) FROM cte WHERE summ <= 0.1 * ( SELECT MAX(summ) FROM cte )
AVG(price)
2.5000
 hidden batch(es)


WITH cte AS ( SELECT price, SUM(volume) OVER (ORDER BY price ASC) running, SUM(volume) OVER (ORDER BY price ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) total FROM sourcetable ) SELECT AVG(price) FROM cte WHERE running <= 0.1 * total
AVG(price)
2.5000
 hidden batch(es)