SQL_101.sql

gistfile1.txt

CREATE TABLE Accounts (
    Id INT,
    Name varchar(50),
    Account_type varchar(50),
    Created_at timestamp
);

INSERT INTO Accounts
    (Id, Name, Account_type, Created_at)
VALUES
    ('0', 'Retail', 'org', '2015-01-01 00:00:00'),
    ('1', 'The Terminators', 'W2', '2016-01-01 00:00:00'),
    ('2', 'Plumb Plumbing', 'W2', '2017-01-01 00:00:00'),
    ('3', 'Second American', 'Contractor', '2017-01-01 00:00:00'),
    ('4', 'Coolest Cooling', 'Contractor', '2017-01-01 00:00:00')
;


CREATE TABLE Organizations (
    Id INT,
    Name varchar(50),
    Created_at timestamptz
);

INSERT INTO Organizations
    (Id, Name, Created_at)
VALUES
    ('1', 'TMX 01', '2016-01-02'),
    ('2', 'TMX 02', '2016-02-01'),
    ('3', '2 Guys', '2017-02-02'),
    ('4', 'A+ Plumbing',  '2017-07-07'),
    ('5', 'HVAC and more', '2017-02-02'),
    ('6', 'HVAC for less',  '2017-03-07'),
    ('7', 'A1 Plumbing',  '2018-03-07')
;

CREATE TABLE Jobs (
    Id INT,
    Account_id INT,
    Organization_id INT,
    Created_at timestamptz,
    Status varchar(50),
  	State_abb varchar(10),
    Costs Numeric
);

INSERT INTO Jobs
    (Id, Account_id, Organization_id, created_at, Status, State_abb,Costs)
VALUES
('1'	,	 '1'	,	 '1' 	,	 '2016-05-02T10:01:00.000Z'	,	 'complete'	,	'NY' 	,	'10')	,
('2'	,	 '1'	,	 '2' 	,	 '2016-05-05T10:01:00.000Z'	,	 'complete'	,	'NJ' 	,	 '15')	,
('3'	,	 '2'	,	 '4' 	,	 '2016-07-05T10:01:00.000Z'	,	 'canceled'	,	'TX' 	,	 '11')	,
('4'	,	 '2'	,	 '4' 	,	 '2016-08-05T10:01:00.000Z'	,	 'complete'	,	'TX' 	,	 '25')	,
('5'	,	 '3'	,	 '3' 	,	 '2016-11-05T10:01:00.000Z'	,	 'complete'	,	'OH' 	,	 '100')	,
('6'	,	 '0'	,	 '3' 	,	 '2017-05-05T10:01:00.000Z'	,	 'canceled'	,	'OH' 	,	 '100')	,
('7'	,	 '4'	,	 '5' 	,	 '2017-05-06T10:01:00.000Z'	,	 'complete'	,	'GA' 	,	 '10')	,
('8'	,	 '4'	,	 '6' 	,	 '2017-05-07T10:01:00.000Z'	,	 'complete'	,	'GA' 	,	 '15')	,
('9'	,	 '1'	,	 '1' 	,	 '2017-05-08T10:01:00.000Z'	,	 'canceled'	,	'NY' 	,	 '11')	,
('10'	,	 '1'	,	 '2' 	,	 '2017-05-09T10:01:00.000Z'	,	 'complete'	,	'NY' 	,	 '25')	,
('11'	,	 '2'	,	 '4' 	,	 '2017-06-05T10:01:00.000Z'	,	 'complete'	,	'TX' 	,	 '100')	,
('12'	,	 '2'	,	 '4' 	,	 '2017-06-05T10:01:00.000Z'	,	 'complete'	,	'TX' 	,	 '100')	,
('13'	,	 '3'	,	 '3' 	,	 '2017-07-05T10:01:00.000Z'	,	 'complete'	,	'OH' 	,	 '10')	,
('14'	,	 '3'	,	 '3' 	,	 '2017-08-05T10:01:00.000Z'	,	 'complete'	,	'OH' 	,	 '15')	,
('15'	,	 '4'	,	 '5' 	,	 '2017-09-05T10:01:00.000Z'	,	 'canceled'	,	'AL' 	,	 '9999')	,
('16'	,	 '4'	,	 '6' 	,	 '2017-10-05T10:01:00.000Z'	,	 'complete'	,	'GA' 	,	 '25')	,
('17'	,	 '1'	,	 '1' 	,	 '2017-10-05T10:01:00.000Z'	,	 'complete'	,	'NJ' 	,	 '100')	,
('18'	,	 '1'	,	 '2' 	,	 '2017-10-05T10:01:00.000Z'	,	 'paused'	,	'NY' 	,	 '100')	,
('19'	,	 '2'	,	 '4' 	,	 '2017-10-05T10:01:00.000Z'	,	 'complete'	,	'TX' 	,	 '10')	,
('20'	,	 '2'	,	 '4' 	,	 '2017-10-05T10:01:00.000Z'	,	 'complete'	,	'' 	,	 '15')	,
('21'	,	 '3'	,	 '3' 	,	 '2018-01-05T10:01:00.000Z'	,	 'canceled'	,	'OH' 	,	 '11')	,
('22'	,	 '3'	,	 '3' 	,	 '2018-01-06T10:01:00.000Z'	,	 'complete'	,	'OH' 	,	 '25')	,
('23'	,	 '4'	,	 '5' 	,	 '2018-01-07T10:01:00.000Z'	,	 'complete'	,	'GA' 	,	 '100')	,
('24'	,	 '4'	,	 '6' 	,	 '2018-11-30T11:05:00.000Z'	,	 'complete'	,	'AL' 	,	 '100')	,
('25'	,	 '1'	,	 '1' 	,	 '2018-11-30T11:05:00.000Z'	,	 'complete'	,	'NJ' 	,	'10')	,
('26'	,	 '0'	,	 '2' 	,	 '2018-12-01T11:05:00.000Z'	,	 'complete'	,	'NY' 	,	 '15')	,
('27'	,	 '2'	,	 '4' 	,	 '2018-12-01T11:05:00.000Z'	,	 'canceled'	,	'TX' 	,	 '11')	,
('28'	,	 '2'	,	 '4' 	,	 '2018-12-02T11:05:00.000Z'	,	 'complete'	,	'TX' 	,	 '25')	,
('29'	,	 '3'	,	 '3' 	,	 '2018-12-03T11:05:00.000Z'	,	 'complete'	,	'OH' 	,	 '100')	,
('30'	,	 '3'	,	 '3' 	,	 '2018-12-04T11:05:00.000Z'	,	 'complete'	,	'OH' 	,	 '100')	,
('31'	,	 '4'	,	 '5' 	,	 '2018-12-05T11:05:00.000Z'	,	 'inprogress'	,	'GA' 	,	 '10')	,
('32'	,	 '4'	,	 '6' 	,	 '2018-12-06T11:05:00.000Z'	,	 'inprogress'	,	'GA' 	,	 '15') ,
('33'	,	 '1'	,	 '1' 	,	 '2018-12-30T11:05:00.000Z'	,	 'complete'	,	'NJ' 	,	'10')	,
('34'	,	 '0'	,	 '2' 	,	 '2019-12-01T11:05:00.000Z'	,	 'complete'	,	'NY' 	,	 '15')	,
('35'	,	 '2'	,	 '4' 	,	 '2019-01-01T11:05:00.000Z'	,	 'canceled'	,	'TX' 	,	 '11')	,
('36'	,	 '2'	,	 '4' 	,	 '2019-01-02T11:05:00.000Z'	,	 'complete'	,	'TX' 	,	 '25')	,
('37'	,	 '3'	,	 '3' 	,	 '2019-01-03T11:05:00.000Z'	,	 'complete'	,	'OH' 	,	 '100')	,
('38'	,	 '3'	,	 '3' 	,	 '2019-01-04T11:05:00.000Z'	,	 'complete'	,	'OH' 	,	 '100')	,
('39'	,	 '4'	,	 '5' 	,	 '2019-02-05T11:05:00.000Z'	,	 'inprogress'	,	'GA' 	,	 '10')	,
('40'	,	 '4'	,	 '6' 	,	 '2019-02-06T11:05:00.000Z'	,	 'inprogress'	,	'GA' 	,	 '15')
;