SQL Market Analytics

WITH returns AS (
  SELECT
    date,
    ticker,
    close / LAG(close, 63) OVER (
      PARTITION BY ticker ORDER BY date
    ) - 1 AS ret_63d
  FROM prices
)
SELECT
  r.ticker,
  s.sector,
  ROUND(r.ret_63d * 100, 2)                 AS return_pct,
  RANK() OVER (ORDER BY r.ret_63d DESC)     AS rank
FROM returns r
JOIN securities s USING (ticker)
WHERE r.date = (SELECT MAX(date) FROM prices)
  AND r.ret_63d IS NOT NULL
  AND s.sector != 'Benchmark'
ORDER BY rank;

WITH month_end AS (
  SELECT date, ticker, close,
         ROW_NUMBER() OVER (
           PARTITION BY ticker, strftime('%Y-%m', date)
           ORDER BY date DESC
         ) AS rn
  FROM prices
),
monthly_ret AS (
  SELECT
    strftime('%Y-%m', date) AS month,
    ticker,
    close / LAG(close) OVER (
      PARTITION BY ticker ORDER BY date
    ) - 1 AS ret
  FROM month_end
  WHERE rn = 1
)
SELECT
  m.month,
  s.sector,
  ROUND(AVG(m.ret) * 100, 2) AS avg_return_pct
FROM monthly_ret m
JOIN securities s USING (ticker)
WHERE m.ret IS NOT NULL
  AND s.sector != 'Benchmark'
  AND m.month >= strftime(
        '%Y-%m',
        date((SELECT MAX(date) FROM prices), '-24 months')
      )
GROUP BY m.month, s.sector
ORDER BY m.month, s.sector;

WITH daily AS (
  SELECT date,
         close / LAG(close) OVER (ORDER BY date) - 1 AS ret
  FROM prices
  WHERE ticker = 'SPY'
),
stats AS (
  SELECT date, ret,
         AVG(ret) OVER w              AS mean_21,
         AVG(ret * ret) OVER w        AS meansq_21,
         COUNT(ret) OVER w            AS n
  FROM daily
  WINDOW w AS (ORDER BY date ROWS BETWEEN 20 PRECEDING AND CURRENT ROW)
)
SELECT
  date,
  ROUND(SQRT((meansq_21 - mean_21 * mean_21) * 252.0) * 100, 2)
    AS annualized_vol_pct
FROM stats
WHERE n = 21
ORDER BY date;

WITH with_peak AS (
  SELECT
    ticker,
    date,
    close,
    MAX(close) OVER (
      PARTITION BY ticker
      ORDER BY date
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_peak
  FROM prices
)
SELECT
  w.ticker,
  s.sector,
  ROUND(MIN(w.close / w.running_peak - 1) * 100, 2) AS max_drawdown_pct
FROM with_peak w
JOIN securities s USING (ticker)
WHERE s.sector != 'Benchmark'
GROUP BY w.ticker, s.sector
ORDER BY max_drawdown_pct;

WITH ma AS (
  SELECT date, ticker, close,
         AVG(close) OVER (PARTITION BY ticker ORDER BY date
           ROWS BETWEEN 49 PRECEDING AND CURRENT ROW)  AS ma50,
         AVG(close) OVER (PARTITION BY ticker ORDER BY date
           ROWS BETWEEN 199 PRECEDING AND CURRENT ROW) AS ma200,
         COUNT(*) OVER (PARTITION BY ticker ORDER BY date
           ROWS BETWEEN 199 PRECEDING AND CURRENT ROW) AS n
  FROM prices
),
crosses AS (
  SELECT date, ticker,
         CASE WHEN ma50 > ma200 THEN 'golden' ELSE 'death' END AS state,
         LAG(CASE WHEN ma50 > ma200 THEN 'golden' ELSE 'death' END)
           OVER (PARTITION BY ticker ORDER BY date) AS prev_state
  FROM ma
  WHERE n = 200
)
SELECT
  ticker,
  MAX(date) AS last_cross_date,
  state     AS signal
FROM crosses
WHERE state != prev_state
  AND ticker != 'SPY'
GROUP BY ticker
ORDER BY last_cross_date DESC;