Database connection settings

Create a file at controllers/privateSettings.js with these contents

let dbConnection = {
  user: 'dbUserName',
  host: 'dbIPAddress',
  database: 'dbName',
  password: 'dbPassword',
  port: 5432
};

module.exports = dbConnection;

Stock data queries

Distinct Stocks

This query gives us a list of all the unique stocks that exist in the database.

  SELECT DISTINCT ticker
  FROM "stocks"."stockData"

Data per year

This query returns the number of data points per calendar year

  SELECT date_trunc('year', date) AS year,
         COUNT(*) 
  FROM "stocks"."rawData"
  GROUP BY year
  ORDER BY year;

Data per ticker

This query returns the number of data points per ticker, sorted in descending order

  SELECT ticker, COUNT(*) AS total
  FROM "stocks"."stockData"
  GROUP BY ticker
  ORDER BY total DESC;