sql script

Fundamental Analysis

1. Profit Margin > 0.05

SELECT TICKER, QUARTER_END, PROFIT_MARGIN
FROM FINAL.FUND_VIEW_1
WHERE PROFIT_MARGIN > 0.05
AND CUM_QUARTER = 1

2. Current Ratio > 1

SELECT TICKER, QUARTER_END, CURRENT_RATIO
FROM FINAL.FUND_VIEW_1
WHERE CURRENT_RATIO > 1
AND CUM_QUARTER = 1

3. Debt to Equity Ratio < 2

SELECT TICKER, QUARTER_END, DE_RATIO
FROM FINAL.FUND_VIEW_1
WHERE DE_RATIO < 2
AND CUM_QUARTER = 1

4. P/E ratio < 10

SELECT TICKER, QUARTER_END, PE_RATIO
FROM FINAL.FUND_VIEW_1
WHERE PE_RATIO < 10
AND CUM_QUARTER = 1

5. Quarter and Annual Growth

SELECT TICKER, QUARTER_END, REVENUE, EARNING, EPS, PE_RATIO, PE_RATIO - "PE_RATIO_PREQUARTER" AS "QoQ", PE_RATIO - "PE_RATIO_PREYEAR" AS "YOY"
FROM (
SELECT TICKER, QUARTER_END, REVENUE, EARNING, EPS, PE_RATIO,
LAG(PE_RATIO, 1) OVER(PARTITION BY TICKER ORDER BY QUARTER_END) AS "PE_RATIO_PREQUARTER",
LAG(PE_RATIO, 4) OVER(PARTITION BY TICKER ORDER BY QUARTER_END) AS "PE_RATIO_PREYEAR"
FROM FINAL.FUND_VIEW_1
ORDER BY TICKER, QUARTER_END DESC
)

6. Two Quarter Continuouse Growth for PE All Positive

SELECT A.TICKER, A.Q1_GROWTH, B.Q2_GROWTH FROM
(SELECT TICKER, SUM(PE_RATIO) AS Q1_GROWTH
FROM FINAL.FUND_VIEW_1
WHERE CUM_QUARTER = 1 OR CUM_QUARTER = 2
GROUP BY FINAL.FUND_VIEW_1.TICKER
HAVING SUM(PE_RATIO) > 0
) A
INNER JOIN
(
SELECT TICKER, SUM(PE_RATIO) AS Q2_GROWTH
FROM FINAL.FUND_VIEW_1
WHERE CUM_QUARTER = 2 OR CUM_QUARTER = 3
GROUP BY FINAL.FUND_VIEW_1.TICKER
HAVING SUM(PE_RATIO) > 0
) B
ON A.TICKER = B.TICKER
ORDER BY A.TICKER

Stock Analysis

1. Sum Volume

-- Kylin
SELECT TICKER, SUM(VOLUME) AS SUM_VOLUME
FROM STOCK_VIEW_1
GROUP BY TICKER
ORDER BY TICKER

-- MySQL
SELECT TICKER, SUM(VOLUME) AS SUM_VOLUME
FROM STOCK
GROUP BY TICKER
ORDER BY TICKER

2. Average Volume

-- Kylin
SELECT TICKER, AVG(VOLUME) AS AVG_VOLUME
FROM STOCK_VIEW_1
GROUP BY TICKER
ORDER BY TICKER

-- MySQL
SELECT TICKER, AVG(VOLUME) AS AVG_VOLUME
FROM STOCK
GROUP BY TICKER
ORDER BY TICKER

3. By Stock Year

-- Kylin
SELECT TICKER, STOCK_YEAR, AVG(VOLUME) AS SUM_VOLUME
FROM STOCK_VIEW_1
GROUP BY TICKER, STOCK_YEAR
ORDER BY TICKER, STOCK_YEAR

-- MySQL
SELECT `ticker`, year(`date`), SUM(`volume`)
FROM `stock`
GROUP BY `ticker`, year(`date`)
ORDER BY `ticker`, year(`date`)