SQL

The following script contains the SQL statements for this query. Note that there are separate scripts optimized for RAPCache and VLDBServer.

VLDBServer

-- Determine the top 10 most active stocks for a specified date
-- sorted by cumulative trade volume by considering all trades.


-- This query is optimized to run on IQ.

commit
;

SELECT TOP 10 TRADING_SYMBOL, sum(TRADE_SIZE) as TRADESIZE, DENSE_RANK ()
OVER (ORDER BY sum(TRADE_SIZE) DESC) as RANKING
	FROM STOCK_TRADE
WHERE  	TRADE_DATE = '2005-11-14' 
	GROUP BY TRADING_SYMBOL
	order by sum(TRADE_SIZE) DESC
;

RAPCache

-- Determine the top 10 most active stocks for a specified date
-- sorted by cumulative trade volume by considering all trades.

-- This query will run on either the ASE or IQ platform.

set  rowcount 10
go

SELECT TRADING_SYMBOL, sum(TRADE_SIZE) as TRADESIZE
	FROM STOCK_TRADE
WHERE
TRADE_TIME between '2005-11-14 00:00:00'  and '2005-11-14 23:59:59'
GROUP BY TRADING_SYMBOL
order by sum(TRADE_SIZE) DESC
go
set rowcount 0
go