Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- 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;
Add Comment
Please, Sign In to add comment