tick_qry5.sql

Finds the most active stocks in the COMPUTER industry using SIC codes.

Output

The first 25 rows returned by this query are:

TRADING_SYMBOL    TRADESIZE    RANKING
ASZ                  249000          1
APE                  198300          2
BGS                  195400          3
BHI                  194200          4
BDR                  188400          5
AJB                  185700          6
BGW                  185100          7
AAZ                  182700          8
AEF                  182100          9
BFE                  181000         10
ADC                  180900         11
BCY                  180200         12
BIG                  177000         13
BLS                  176200         14
BJU                  175700         15
ANW                  174200         16
ANS                  171600         17
AJL                  169600         18
AOD                  168300         19
ALD                  166700         20
AXS                  166400         21
BIJ                  166300         22
ABS                  165900         23
AAV                  165500         24
BFG                  163200         25
...

SQL Statements

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

RAPStore Database Script

-- Find the most active stocks in the "COMPUTER" industry
-- for the current day.

-- This query is optimized to run on IQ.

commit
;

SELECT st.TRADING_SYMBOL, SUM(TRADE_SIZE) TRADESIZE, 
DENSE_RANK() OVER (ORDER by SUM(TRADE_SIZE) DESC) as RANKING
FROM STOCK_TRADE st 
inner join INSTRUMENT ii
on ii.INSTRUMENT_ID = st.INSTRUMENT_ID
inner join SCND_IDST_CLS sc
on ii.SCND_IDST_CLS_ID = sc.SCND_IDST_CLS_ID
and sc.SIC_NAME = 'COMPUTERS'
WHERE  st.TRADE_DATE = '2005-11-14'
GROUP BY 
st.TRADING_SYMBOL
;

RAPCache Database Script

-- Find the most active stocks in the "COMPUTER" industry.


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

SELECT st.TRADING_SYMBOL,SUM(TRADE_SIZE) as TRADESIZE
FROM STOCK_TRADE st 
inner join INSTRUMENT ii
on ii.INSTRUMENT_ID = st.INSTRUMENT_ID
inner join SCND_IDST_CLS sc
on ii.SCND_IDST_CLS_ID = sc.SCND_IDST_CLS_ID
and sc.SIC_NAME = 'COMPUTERS'
WHERE 
TRADE_TIME between '2005-11-14 00:00:00' and '2005-11-14 23:59:59'
GROUP BY st.TRADING_SYMBOL
order by SUM(TRADE_SIZE) DESC

go