CA_660_Assignment_2

Query_1

SELECT
  c.sp_1,
  f.fi_company_name,  -- <<< not strictly necessary, but good to have the name!
  c.first_6_avg AS FIRST,
  c.last_6_avg AS LAST,
  c.last_6_avg - c.first_6_avg AS diff,
  (c.last_6_avg::DECIMAL/c.first_6_avg::DECIMAL)::DECIMAL(6,2) * 100 AS percentage_rise -- <<-- the CRITICAL measurement
FROM comp_price c                     -- <<<--- this table I created by inserting the av. price over 6 months 5 years ago (first)
JOIN financial_info f                 --                            and inserting the av. price for the most recent 6 months (last).
  ON (c.sp_1 = f.fi_company_symbol)
ORDER BY percentage_rise DESC          --  <<<--- Sorting by this critical measurement
LIMIT 50 OFFSET 200;

Query_2


SELECT SUM(volatility_metric)::DECIMAL(7,2) AS "Total Volatility" <<== my metric
FROM
(
SELECT
  s.company_code,
  AVG(s.low_price)::DECIMAL(15, 2) AS low_average,
  AVG(s.high_price)::DECIMAL(15,2) AS high_average,
  ((AVG(s.high_price)::DECIMAL(15,2) + AVG(s.low_price)::DECIMAL(15,2))/2)::DECIMAL(10,2) AS average_mean,
  (SUM((((s.high_price::DECIMAL + s.low_price::DECIMAL)/2) - s.open_price)^2))::DECIMAL(15,2) AS stuff,  -- <<-- help for sigma!
  (SQRT(SUM((((s.high_price::DECIMAL + s.low_price::DECIMAL)/2) - s.open_price)^2)/COUNT(s.company_code)))::DECIMAL(15,2) AS std_deviation,
  (((AVG(s.high_price)::DECIMAL(15,2) + AVG(s.low_price)::DECIMAL(15,2))/2)::DECIMAL(10,2))/((SQRT(SUM((((s.high_price::DECIMAL + s.low_price::DECIMAL)/2) - s.open_price)^2)/COUNT(s.company_code)))::DECIMAL(15,2)) AS volatility_metric,

  COUNT(s.company_code)
FROM share s
WHERE s.company_code IN
(
  SELECT the_code FROM
  (
    -- Most of the fields here are superfluous - I just didn't want to delete them in case
    -- they came in useful later on - but, time waits for no man!
    SELECT
    c.sp_1 AS the_code,
    f.fi_company_name,
    c.first_6_avg AS FIRST,
    c.last_6_avg AS LAST,
    c.last_6_avg - c.first_6_avg AS diff,
    (c.last_6_avg::DECIMAL/c.first_6_avg::DECIMAL)::DECIMAL(6,2) * 100 AS percentage_rise
    FROM comp_price c
    JOIN financial_info f
      ON (c.sp_1 = f.fi_company_symbol)
    ORDER BY percentage_rise DESC
    LIMIT 50 OFFSET 398   -- <<<<   Vary these numbers to obtain the Volatility Coefficient for various cohorts!
  ) AS x
)
GROUP BY s.company_code
ORDER BY company_code
) AS y;