trade (buy_c, limit, sell_c)

buy_c	limit	sell_c
------	------	------
1	30$	2
3	20$	5
7	10$	11

buy_c	limit	sell_c
------	------	------
1	30	18
4	20	16
11	10	11

SELECT 
	sum(b.buy_c), 
	t.limit, 
	sum(s.sell_c)
FROM trade t
		LEFT JOIN trade b ON (b.limit >= t.limit) /* щедные покупатели */
		LEFT JOIN trade s ON (s.limit <= t.limit) /* щедрые продавцы */
GROUP BY t.limit
ORDER BY t.limit DESC

SELECT 
	sum(CASE WHEN b.limit >= t.limit THEN b.buy_c ELSE 0 END), /* щедные покупатели */
	t.limit, 
	sum(CASE WHEN b.limit <= t.limit THEN b.sell_c ELSE 0 END) /* щедрые продавцы */
FROM trade t, trade b
GROUP BY t.limit
ORDER BY t.limit DESC

SELECT
	b.buy_c,
	COALESCE(b.limit, s.limit) AS limit, /* DB2: если первое значение NULL, будет подставлено второе */
	s.sell_c
FROM 
	(SELECT t.limit, sum(b.buy_c) AS buy_c
	FROM trade t LEFT JOIN trade b ON b.limit >= t.limit
	GROUP BY t.limit) AS b /* щедные покупатели */

		FULL JOIN
	  
		(SELECT t.limit, sum(s.sell_c) AS sell_c
		FROM trade t LEFT JOIN trade s ON s.limit <= t.limit 
		GROUP BY t.limit) AS s /* щедрые продавцы */
	   
			ON (b.limit = s.limit)
ORDER BY limit DESC

CREATE VIEW order_v (stock, call_volume, call_backlog, limit, put_backlog, put_volume) AS
	/* 1st: calculate volumes for each limit, but without backlogs */
	WITH no_backlogs (stock, call_volume, limit, put_volume) AS
		(SELECT DISTINCT
			o_stock AS stock,
			SUM(CASE
					WHEN o_type = 'CALL' THEN 
						o_amount
					ELSE
						0
				END) OVER(PARTITION BY o_stock ORDER BY o_limit DESC) AS call_volume, /* including call orders with higher prices */
			o_limit AS limit,
			SUM(CASE
					WHEN o_type = 'PUT' THEN 
						o_amount
					ELSE
						0
				END) OVER(PARTITION BY o_stock ORDER BY o_limit ASC) AS put_volume /* including put orders with lower prices */
		FROM orders) 

	/* 2nd: calculate backlogs */
	SELECT
		stock,
		call_volume,
		CASE 
			WHEN call_volume > put_volume
			THEN call_volume - put_volume
			ELSE 0
		END			AS call_backlog,
		limit,
		CASE 
			WHEN put_volume > call_volume
			THEN put_volume - call_volume
			ELSE 0
		END			AS put_backlog,
		put_volume
	FROM no_backlogs