-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathFortune 500 Analysis
95 lines (86 loc) · 4.23 KB
/
Fortune 500 Analysis
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
# This analysis utilized the following Fortune 500 dataset:
CREATE TABLE fortune_companies (
company_id INTEGER PRIMARY KEY,
company_name TEXT,
industry TEXT,
revenue REAL,
employees INTEGER,
healthcare_benefits BIT,
paid_time_off_days INTEGER,
maternity_leave_weeks INTEGER,
avg_employee_tenure REAL
);
INSERT INTO fortune_companies (company_name, industry, revenue, employees, healthcare_benefits, paid_time_off_days, maternity_leave_weeks, avg_employee_tenure)
VALUES
('Apple Inc.', 'Technology', 365.7, 147000, 1, 20, 12, 4.5),
('Walmart Inc.', 'Retail', 523.96, 2200000, 1, 15, 8, 6.2),
('Exxon Mobil Corporation', 'Energy', 265.01, 72000, 0, 18, 6, 7.8),
('Amazon.com Inc.', 'Technology', 386.06, 1370000, 1, 22, 14, 5.1),
('JPMorgan Chase & Co.', 'Financials', 160.1, 255998, 1, 21, 12, 6.9),
('Verizon Communications Inc.', 'Telecommunications', 131.88, 132600, 0, 15, 6, 5.5),
('Company A', 'Retail', 235.4, 2000, 1, 18, 10, 5.8),
('Company B', 'Healthcare', 400.7, 2300, 1, 22, 13, 5.7),
('Company C', 'Manufacturing', 300.2, 2000, 1, 18, 10, 5.8),
('Company D', 'Healthcare', 150.5, 3500, 1, 20, 12, 6.5),
('Company E', 'Finance', 280.7, 1800, 0, 14, 8, 4.2),
('Company F', 'Technology', 420.1, 2500, 1, 22, 14, 7.1),
('Company G', 'Retail', 190.8, 1500, 1, 16, 9, 5.3),
('Company H', 'Energy', 280.5, 2200, 0, 15, 8, 6.8),
('Company I', 'Telecommunications', 110.3, 1800, 1, 19, 11, 4.9),
('Company J', 'Manufacturing', 390.6, 2700, 1, 21, 13, 6.2),
('Company K', 'Healthcare', 180.2, 3200, 1, 17, 9, 7.4),
('Company L', 'Finance', 230.4, 1900, 0, 13, 7, 5.6),
('Company M', 'Technology', 340.9, 2800, 1, 23, 15, 6.9),
('Company N', 'Retail', 200.6, 1600, 1, 15, 8, 4.7),
('Company O', 'Energy', 260.2, 2400, 0, 14, 7, 6.1),
('Company P', 'Telecommunications', 130.5, 2100, 1, 20, 12, 5.3),
('Company Q', 'Manufacturing', 360.0, 2900, 1, 22, 14, 7.8),
('Company R', 'Technology', 400.7, 2300, 1, 22, 13, 5.7),
('Company S', 'Retail', 210.8, 1600, 0, 16, 9, 4.9),
('Company T', 'Energy', 290.5, 2200, 1, 15, 8, 7.2),
('Company U', 'Telecommunications', 140.3, 1900, 1, 20, 12, 6.1),
('Company V', 'Manufacturing', 350.6, 2800, 1, 22, 14, 5.4),
('Company W', 'Healthcare', 160.2, 3300, 0, 18, 10, 4.8),
('Company X', 'Finance', 240.4, 2000, 1, 13, 7, 7.1),
('Company Y', 'Technology', 320.9, 2700, 1, 23, 15, 5.6),
('Company Z', 'Retail', 180.6, 1400, 0, 14, 8, 6.3),
('Company AA', 'Energy', 240.2, 2600, 1, 17, 9, 6.5),
('Company BB', 'Telecommunications', 120.5, 2100, 0, 19, 11, 4.5),
('Company CC', 'Manufacturing', 380.0, 3000, 1, 21, 13, 7.3),
('Company DD', 'Healthcare', 170.2, 3200, 1, 17, 9, 5.8),
('Company EE', 'Finance', 250.4, 1900, 0, 12, 6, 6.4),
('Company FF', 'Technology', 300.9, 2500, 1, 24, 16, 6.9),
('Company GG', 'Retail', 190.6, 1700, 0, 13, 7, 5.2),
('Company HH', 'Energy', 280.2, 2300, 1, 16, 9, 6.8),
('Company II', 'Telecommunications', 110.5, 2000, 1, 21, 12, 4.9),
('Company JJ', 'Manufacturing', 370.0, 3100, 1, 20, 12, 7.6),
('Company KK', 'Healthcare', 150.2, 3400, 0, 16, 8, 5.3);
# Analysis:
# Preview table
SELECT *
FROM fortune_companies;
# Which companies include benefits and at least 20 pto days?
SELECT company_name, paid_time_off_days
FROM fortune_companies
WHERE healthcare_benefits=1
AND paid_time_off_days>="20";
# What is the average employee tenure at a tech company?
SELECT industry, AVG(avg_employee_tenure)
FROM fortune_companies
WHERE industry='Technology';
# What is the total revenue for each industry?
SELECT industry, ROUND(SUM(revenue)) AS 'total_revenue_billions'
FROM fortune_companies
GROUP BY industry
HAVING SUM(revenue);
# Assign all companies with as "mom friendly workplace", "acceptable", "abysmal", based on the maternity leave offered.
SELECT company_name,
CASE WHEN maternity_leave_weeks>='10' THEN "mom friendly workplace"
WHEN maternity_leave_weeks>='8' THEN "acceptable"
ELSE "abysmal"
END AS "mom_friendliness"
FROM fortune_companies;
# Which company in the energy industry has the greatest revenue and how much is it?
SELECT company_name, max(revenue) AS "max_revenue_billions"
FROM fortune_companies
WHERE industry="Energy"