--SELECT SUM(open)/COUNT(open) AS avgerage_open
 --FROM tutorial.aapl_historical_stock_price
 --SELECT MIN(low) AS lowest_stock_price
 -- FROM tutorial.aapl_historical_stock_price
 --SELECT MAX(close - open) AS highest_increase
 --FROM tutorial.aapl_historical_stock_price
 --SELECT AVG(volume)
 --FROM tutorial.aapl_historical_stock_price
 /*SELECT year,
       month,
       COUNT(volume) AS shares
  FROM tutorial.aapl_historical_stock_price
 GROUP BY year, month
 ORDER BY year, month*/ /*SELECT year, AVG(close-open) AS average_daily_change
 FROM tutorial.aapl_historical_stock_price
 GROUP BY Year
 ORDER BY Year*/ /*SELECT year, month, MIN(low), MAX(high)
 FROM tutorial.aapl_historical_stock_price
 GROUP BY Year, Month
 ORDER BY Year, Month*/ /*SELECT year,
       month,
       MAX(high) AS month_high
  FROM tutorial.aapl_historical_stock_price
 GROUP BY year, month
HAVING MAX(high) > 400
 ORDER BY year, month*/ /*SELECT DISTINCT year
  FROM tutorial.aapl_historical_stock_price
  ORDER BY year*/ /*SELECT year,
       COUNT(DISTINCT month) AS months_in_year
  FROM tutorial.aapl_historical_stock_price
 GROUP BY year
 ORDER BY year*/ /*SELECT COUNT(DISTINCT year),
       COUNT(DISTINCT month)
  FROM tutorial.aapl_historical_stock_price*/ /*SELECT player_name, state,
       CASE WHEN state = 'CA' THEN 'yes'
            ELSE 'no' END AS is_from_california
  FROM benn.college_football_players
ORDER BY is_from_california DESC*/ /*SELECT player_name,
       height,
       CASE WHEN height > 75 THEN 'over 75'
            WHEN height > 70 AND height <= 75 THEN '71-75'
            WHEN height > 64 AND height <= 70 THEN '65-70'
            ELSE '64 or under' END AS height_group
  FROM benn.college_football_players*/ /*SELECT *,
       CASE WHEN year = 'SR' OR year = 'JR' THEN player_name
            ELSE NULL END AS player_name_if_junior_or_senior
  FROM benn.college_football_players*/ --Write a query that counts the number of 300lb+ players for each of the following regions: West Coast (CA, OR, WA), Texas, and Other (Everywhere else).
/*SELECT COUNT(player_name) AS players_over_300lb , CASE WHEN state = 'CA' OR state = 'OR' OR state = 'WA' THEN 'West Coast'
  WHEN state = 'TX' THEN 'Texas'
  ELSE 'Other' END AS region
  FROM benn.college_football_players
  WHERE weight >= 300
  GROUP BY region*/ --Write a query that calculates the combined weight of all underclass players (FR/SO) in California as well as the combined weight of all upperclass players (JR/SR) in California.
/*SELECT SUM(weight) AS players_combined_weight ,
  CASE WHEN year = 'FR' OR year = 'SO' THEN 'underclass players'
  WHEN year = 'JR' OR year = 'SR' THEN 'upperclass players'
  ELSE NULL END AS player_years
  FROM benn.college_football_players
  WHERE state = 'CA'
  GROUP BY player_years*/ --Write a query that displays the number of players in each state, with FR, SO, JR, and SR players in separate columns and another column for the total
---number of players. Order results such that states with the most players come first.
/*  SELECT state,
       COUNT(CASE WHEN year = 'FR' THEN 1 ELSE NULL END) AS fr_count,
       COUNT(CASE WHEN year = 'SO' THEN 1 ELSE NULL END) AS so_count,
       COUNT(CASE WHEN year = 'JR' THEN 1 ELSE NULL END) AS jr_count,
       COUNT(CASE WHEN year = 'SR' THEN 1 ELSE NULL END) AS sr_count,
       COUNT(state) AS total_players
  FROM benn.college_football_players
 GROUP BY state
 ORDER BY total_players DESC*/ --Write a query that shows the number of players at schools with names that start with A through M, and the number at schools with names starting with N - Z.
/*SELECT CASE WHEN school_name < 'n' THEN 'A-M'
            WHEN school_name >= 'n' THEN 'N-Z'
            ELSE NULL END AS school_name_catog,
       COUNT(player_name) AS players
  FROM benn.college_football_players
 GROUP BY school_name_catog*/ --Write a query that selects the school name, player name, position, and weight for every player in Georgia, ordered by weight (heaviest to lightest). Be sure to make an alias for the table, and to reference all column names in relation to the alias.
/*SELECT players.school_name,
       players.player_name,
       players.position,
       players.weight
  FROM benn.college_football_players players
 WHERE players.state = 'GA'
 ORDER BY players.weight DESC*/ /*SELECT teams.conference, AVG(players.weight) AS average_weight
  FROM benn.college_football_players players
  JOIN benn.college_football_teams teams
    ON teams.school_name = players.school_name
    GROUP BY teams.conference
    ORDER BY average_weight*/


--Write a query that displays player names, school names and conferences for schools in the "FBS (Division I-A Teams)" division.
/*SELECT players.player_name AS players_player_name,
       players.school_name AS players_school_name,
       teams.conference AS teams_conference
FROM benn.college_football_players players
JOIN benn.college_football_teams teams
  ON teams.school_name = players.school_name
  WHERE teams.division = 'FBS (Division I-A Teams)'*/

-- SELECT companies.permalink AS companies_permalink,
--       companies.name AS companies_name,
--       acquisitions.company_permalink AS acquisitions_permalink,
--       acquisitions.acquired_at AS acquired_date
--   FROM tutorial.crunchbase_companies companies
--   LEFT JOIN tutorial.crunchbase_acquisitions acquisitions
--     ON companies.permalink = acquisitions.company_permalink



--Write a query that performs an inner join between the tutorial.crunchbase_acquisitions table and the tutorial.crunchbase_companies table, but instead of listing individual rows, count the number of non-null rows in each table.
-- SELECT COUNT(companies.permalink) AS companies_rowcount,
--       COUNT(acquisitions.company_permalink) AS acquisitions_rowcount
--   FROM tutorial.crunchbase_companies companies
--   JOIN tutorial.crunchbase_acquisitions acquisitions
--     ON companies.permalink = acquisitions.company_permalink

--Modify the query above to be a LEFT JOIN. Note the difference in results.
-- SELECT COUNT(companies.permalink) AS companies_rowcount,
--       COUNT(acquisitions.company_permalink) AS acquisitions_rowcount
--   FROM tutorial.crunchbase_companies companies
--   LEFT JOIN tutorial.crunchbase_acquisitions acquisitions
--     ON companies.permalink = acquisitions.company_permalink


--Modify the query above to be a RIGHT JOIN. Note the difference in results.
-- SELECT COUNT(companies.permalink) AS companies_rowcount,
--       COUNT(acquisitions.company_permalink) AS acquisitions_rowcount
--   FROM tutorial.crunchbase_companies companies
--   RIGHT JOIN tutorial.crunchbase_acquisitions acquisitions
--     ON companies.permalink = acquisitions.company_permalink


--Count the number of unique companies (don't double-count companies) and unique acquired companies by state. Do not include results for which there is no state data, and order by the number of acquired companies from highest to lowest.
-- SELECT companies.state_code, COUNT(DISTINCT companies.permalink) AS company_num,
--   COUNT(DISTINCT acquisitions.company_permalink) as acquired_num
--   FROM tutorial.crunchbase_companies companies
--   LEFT JOIN tutorial.crunchbase_acquisitions acquisitions
--   ON companies.permalink = acquisitions.company_permalink
--   WHERE companies.state_code IS NOT NULL
--   GROUP BY companies.state_code
--   ORDER BY acquired_num DESC



-- Rewrite the previous practice query in which you counted total and acquired companies by state, but with a RIGHT JOIN instead of a LEFT JOIN. The goal is to produce the exact same results.
-- SELECT companies.state_code, COUNT(DISTINCT companies.permalink) AS company_num,
--   COUNT(DISTINCT acquisitions.company_permalink) as acquired_num
--   FROM tutorial.crunchbase_acquisitions acquisitions
--   RIGHT JOIN  tutorial.crunchbase_companies companies
--   ON acquisitions.company_permalink = companies.permalink
--   WHERE companies.state_code IS NOT NULL
--   GROUP BY companies.state_code
--   ORDER BY acquired_num DESC



--Write a query that shows a company's name, "status" (found in the Companies table), and the number of unique investors in that company. Order by the number of investors from most to fewest. Limit to only companies in the state of New York.
-- SELECT t1.company_name, t2.status,
-- COUNT(DISTINCT t1.investor_name) AS investors_num
-- FROM tutorial.crunchbase_investments t1
-- LEFT JOIN tutorial.crunchbase_companies t2
-- ON t1.company_permalink = t2.permalink
-- WHERE t1.company_state_code = 'NY'
-- GROUP BY t1.company_name, t2.status
-- ORDER BY  investors_num DESC



--Write a query that lists investors based on the number of companies in which they are invested. Include a row for companies with no investor, and order from most companies to least.
-- SELECT CASE WHEN t2.investor_name IS NULL THEN 'No Investors'
--             ELSE t2.investor_name END AS investor,
--       COUNT(DISTINCT t1.permalink) AS companies_invested_in
--   FROM tutorial.crunchbase_companies t1
--   LEFT JOIN tutorial.crunchbase_investments t2
--     ON t1.permalink = t2.company_permalink
-- GROUP BY investor
-- ORDER BY companies_invested_in DESC



-- SELECT COUNT(CASE WHEN companies.permalink IS NOT NULL AND acquisitions.company_permalink IS NULL
--                   THEN companies.permalink ELSE NULL END) AS companies_only,
--       COUNT(CASE WHEN companies.permalink IS NOT NULL AND acquisitions.company_permalink IS NOT NULL
--                   THEN companies.permalink ELSE NULL END) AS both_tables,
--       COUNT(CASE WHEN companies.permalink IS NULL AND acquisitions.company_permalink IS NOT NULL
--                   THEN acquisitions.company_permalink ELSE NULL END) AS acquisitions_only
--   FROM tutorial.crunchbase_companies companies
--   FULL JOIN tutorial.crunchbase_acquisitions acquisitions
--     ON companies.permalink = acquisitions.company_permalink






--Write a query that joins tutorial.crunchbase_companies and tutorial.crunchbase_investments_part1 using a FULL JOIN. Count up the number of rows that are matched/unmatched as in the example above.
-- SELECT COUNT(CASE WHEN companies.permalink IS NOT NULL AND invest_part_1.company_permalink IS NULL
--                   THEN companies.permalink ELSE NULL END) AS companies_only,
--       COUNT(CASE WHEN companies.permalink IS NOT NULL AND invest_part_1.company_permalink IS NOT NULL
--                   THEN companies.permalink ELSE NULL END) AS both_tables,
--       COUNT(CASE WHEN companies.permalink IS NULL AND invest_part_1.company_permalink IS NOT NULL
--                   THEN invest_part_1.company_permalink ELSE NULL END) AS invest_part_1_only
--   FROM tutorial.crunchbase_companies companies
--   FULL JOIN tutorial.crunchbase_investments_part1 invest_part_1
--     ON companies.permalink = invest_part_1.company_permalink


--UNION EXAMPLE
-- SELECT *
--   FROM tutorial.crunchbase_investments_part1
-- UNION
-- SELECT *
--   FROM tutorial.crunchbase_investments_part2



  -- Write a query that appends the two crunchbase_investments datasets above (including duplicate values). Filter the first dataset to only companies with names that start with the letter "T", and filter the second to companies with names starting with "M" (both not case-sensitive). Only include the company_permalink, company_name, and investor_name columns.
-- SELECT company_permalink, company_name, investor_name
--   FROM tutorial.crunchbase_investments_part1
--   WHERE company_name ILIKE 'T%'
-- UNION ALL
-- SELECT company_permalink, company_name, investor_name
--   FROM tutorial.crunchbase_investments_part2
--   WHERE company_name ILIKE 'M%'



-- Write a query that shows 3 columns. The first indicates which dataset (part 1 or 2) the data comes from, the second shows company status, and the third is a count of the number of investors.

-- Hint: you will have to use the tutorial.crunchbase_companies table as well as the investments tables. And you'll want to group by status and dataset.
-- SELECT 'investments_part1' AS dataset_part,
--       companies.status,
--       COUNT(DISTINCT investments.investor_permalink) AS investors
--   FROM tutorial.crunchbase_companies companies
--   LEFT JOIN tutorial.crunchbase_investments_part1 investments
--     ON companies.permalink = investments.company_permalink
-- GROUP BY dataset_part, status
-- UNION ALL
-- SELECT 'investments_part2' AS dataset_part,
--       companies.status,
--       COUNT(DISTINCT investments.investor_permalink) AS investors
--   FROM tutorial.crunchbase_companies companies
--   LEFT JOIN tutorial.crunchbase_investments_part2 investments
--     ON companies.permalink = investments.company_permalink
-- GROUP BY dataset_part, status