SQL_Answer_Key.sql

gistfile1.txt

-- Query all columns from the jobs table, and limit to 10 rows

SELECT *
FROM jobs
LIMIT 10;

-- Query all columns with costs above 50

SELECT *
FROM jobs
WHERE costs > 50;

-- Query the state and organization Columns from the jobs table

SELECT 
	state_abb
  ,organization_id
FROM jobs;

-- Query a count of jobs in NY

SELECT COUNT(*)
FROM jobs
WHERE state_abb = 'NY';

-- Query the count of jobs in NY for last 30 days

SELECT COUNT(*)
FROM jobs
WHERE state_abb = 'NY'
	AND jobs.created_at > CURRENT_DATE - 30;

-- Query the average cost of jobs

SELECT AVG(costs)
FROM jobs;

-- Query the average cost per account

SELECT 
	account_id
	,AVG(costs)
FROM jobs
GROUP BY 1;

-- Find the first and last jobs created

SELECT MIN(jobs.created_at), MAX(jobs.created_at)
FROM jobs;

-- Find the total cost per organization for jobs that were completed

SELECT 
	organizations.name as organization_name
    ,SUM(costs) as Total_cost
FROM Jobs
LEFT JOIN organizations ON jobs.organization_id = organizations.id
LEFT JOIN accounts ON jobs.account_id = accounts.id
WHERE status = 'complete'
GROUP BY 1;

-- What are the statuses

SELECT
	status
    ,COUNT(*)
FROM jobs
GROUP BY 1;

-- Which organization has the most jobs

SELECT
	organization_id
    ,COUNT(*)
FROM jobs
GROUP BY 1
ORDER BY 2 DESC;

-- Which month had the most activity

SELECT
	date_trunc('month', (created_at)::timestamp)::date
    ,COUNT(*)
FROM jobs
GROUP BY 1;