-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathFortune 500 Analysis
82 lines (75 loc) · 3.73 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
# 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:
# Average revenue per industry
SELECT industry, AVG(revenue) AS avg_revenue
FROM fortune_companies
GROUP BY industry;
# Companies with the most generous maternity leave:
SELECT company_name, maternity_leave_weeks
FROM fortune_companies
ORDER BY maternity_leave_weeks DESC
LIMIT 5; -- Display the top 5
# Healthcare benefits vs average employee tenure:
SELECT
CASE WHEN healthcare_benefits = 1 THEN 'Healthcare Provided'
ELSE 'No Healthcare'
END AS benefits_status,
AVG(avg_employee_tenure) AS avg_tenure
FROM fortune_companies
GROUP BY benefits_status;