SQL_answers.sql

gistfile1.txt

SELECT *
FROM jobs
LIMIT 10;

SELECT *
FROM jobs
WHERE costs > 50;

SELECT 
	state_abb
    ,organization_id
FROM jobs;

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

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

SELECT AVG(costs)
FROM jobs;

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

SELECT 
	account_id
    ,accounts.name
	,AVG(costs)
FROM jobs
LEFT JOIN accounts ON jobs.account_id = accounts.id
GROUP BY 1,2;



SELECT MAX(jobs.created_at)
FROM jobs;



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;

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

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

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

SELECT *
FROM jobs
WHERE state_abb is NULL;