PostgreSQL Analysis of the World’s Oldest Businesses
INTRODUCTION
In the dynamic landscape of global commerce, the longevity of a business stands as a testament to its resilience, adaptability, and strategic foresight. Some enterprises have not only weathered the storms of changing market conditions but have thrived for centuries. BusinessFinancing.co.uk, in a meticulous endeavor, has delved into the archives of economic history, researching and identifying the oldest companies still operational in almost every country. The findings of this comprehensive investigation have been distilled into a dataset that now invites exploration.
The tool used for this analysis was PostgreSQL.
Business Questions We’ll Unravel
- What are the unique business categories in the dataset?
- How many businesses are there in each category?
- Which category has the highest number of businesses?
- How many unique countries are covered in the dataset?
- Which continent has the highest number of businesses?
- What is the distribution of businesses across continents?
- What is the range of founding years for businesses in the dataset?
- How many businesses were founded in each decade?
- What is the average founding year of businesses?
- What are the 10 oldest businesses in the dataset, and what are their categories and countries?
- Which categories have the highest percentage of businesses that have survived for over 200 years?
- How has the average age of businesses in each category changed over the past 100 years?
- What are the most common business categories within each country?
- Which countries have the most diverse representation of business categories?
- Identify any countries where a single category dominates the oldest businesses.
- Have any business categories consistently produced long-lasting businesses across multiple countries?
- Which categories have the most significant variation in business longevity across different countries?
- Which countries have the most businesses represented in the dataset?
- What are the top 5 countries with the most diverse range of business categories?
- Identify any categories that have seen a significant increase or decrease in representation over time.
- Analyze the distribution of businesses within each category by continent.
- Explore the relationship between business age and category size.
- Identify any trends in the founding dates of businesses within each country.
- Investigate the evolution of dominant business categories across different country groupings.
- Use window functions to calculate the “rolling age” of each business category within each country.
Problem Statement and Objectives
Our primary aim is to dissect the patterns and characteristics that have enabled certain businesses to endure for centuries. The specific objectives of this analysis include:
- Investigating the commonalities among the world’s oldest businesses.
- Analyzing the founding years and distribution of these businesses across categories and countries.
- Uncovering any discernible trends or anomalies that may provide insights into the factors contributing to long-term success.
Data Structure and SQL Environment
The business dataset, meticulously compiled for exploration, has been seamlessly integrated into a PostgreSQL database. The database structure is designed with clarity, featuring three key tables:
- Categories:
- category_code: Code for the category of the business.
- category: Description of the business category.
2. Countries:
- country_code: ISO 3166–1 3-letter country code.
- country: Name of the country.
- continent: Name of the continent that the country exists in.
3. Businesses:
- business: Name of the business.
- year_founded: Year the business was founded.
- category_code: Code for the category of the business.
- country_code: ISO 3166–1 3-letter country code.
This dataset for the study was sourced from Kaggle and is available for download here
Data Cleaning
The Oldest Businesses dataset has been thoroughly examined, comprising 3 tables and I’m pleased to report that the data is clean and well-suited for analysis. During our investigation, we found no significant issues such as duplicates, missing values, or incorrect data types.
In terms of consistency and accuracy, the tables show no outliers or anomalies that could impact our study. The data appears well organized, and there are clear relationships between the tables, contributing to its reliability.
Fortunately, the data didn’t require any cleaning or transformation efforts, as all columns contained the correct data types and values. We made a few minor adjustments to enhance the structure and organization of the tables for improved readability.
Overall, I have high confidence in the accuracy of the information, and I believe it will be a valuable asset to our study.
Questions and Answers
What are the unique business categories in the dataset?
We use DISTINCT to find the unique business categories in the dataset.
How many businesses are there in each category?
We use COUNT and GROUP BY to see how many businesses are in each category.
Which category has the highest number of businesses?
We use COUNT, GROUP BY, and ORDER BY to find the category with the highest number of businesses.
How many unique countries are covered in the dataset?
We use DISTINCT to find the unique countries covered in the dataset.
Which continent has the highest number of businesses?
We use COUNT, GROUP BY, and ORDER BY to find the continent with the highest number of businesses.
What is the distribution of businesses across continents?
We use COUNT, GROUP BY, and ORDER BY to see the distribution of businesses across continents.
What is the range of founding years for businesses in the dataset?
We use MIN and MAX to find the earliest and latest founding years, defining the range.
How many businesses were founded in each decade?
We use COUNT, EXTRACT, and GROUP BY to find how many businesses were founded in each decade.
What is the average founding year of businesses?
We use AVG and GROUP BY to find the average founding year of businesses.
What are the 10 oldest businesses in the dataset, and what are their categories and countries?
We use ORDER BY, LIMIT, and JOIN to find the 10 oldest businesses and their respective categories and countries.
Which categories have the highest percentage of businesses that have survived for over 200 years?
We use COUNT, FILTER, and Percentages to determine which categories have the highest percentage of businesses that have survived for over 200 years.
How has the average age of businesses in each category changed over the past 100 years?
We use AVG, EXTRACT, GROUP BY, and WHERE to analyze how the average age of businesses in each category has changed over the past 100 years.
What are the most common business categories within each country?
We use COUNT, GROUP BY, ORDER BY, and INNER JOIN to find the most common business categories within each country.
Which countries have the most diverse representation of business categories?
We use COUNT, GROUP BY, ORDER BY, and INNER JOINS to find countries with diverse representation across various business categories.
Identify any countries where a single category dominates the oldest businesses.
We use COUNT, GROUP BY, ORDER BY, and INNER JOIN to identify countries where a single category dominates the oldest businesses.
Have any business categories consistently produced long-lasting businesses across multiple countries?
We use COUNT, GROUP BY, JOIN, and WHERE filtering conditions to determine if certain business categories consistently produce long-lasting businesses across multiple countries.
Which categories have the most significant variation in business longevity across different countries?
We use STDDEV, GROUP BY to identify categories with the most significant variation in business longevity across different countries.
Which countries have the most businesses represented in the dataset?
We use COUNT, GROUP BY, ORDER BY to find countries with the most businesses.
What are the top 5 countries with the most diverse range of business categories?
We use COUNT, GROUP BY, ORDER BY to find the top 5 countries with a diverse range of business categories.
Identify any categories that have seen a significant increase or decrease in representation over time.
We use COUNT, GROUP BY to identify categories that have seen a significant increase or decrease in representation over time.
Analyze the distribution of businesses within each category by continent
We use COUNT, GROUP BY, JOIN to analyze how businesses are distributed within each category across different continents.
Explore the relationship between business age and category size.
We use AVG, COUNT, GROUP BY to explore the relationship between the age of businesses and the number of businesses in each category.
Identify any trends in the founding dates of businesses within each country.
We use COUNT, GROUP BY to identify patterns and trends in the founding dates of businesses within each country.
Investigate the evolution of dominant business categories across different country groupings
We use COUNT, GROUP BY, JOIN to investigate how dominant business categories have evolved across different country groupings (e.g., continents, economic regions).
Use window functions to calculate the “rolling age” of each business category within each country.
We use window functions, AVG to calculate the “rolling age” of each business category within each country, identifying periods of accelerated or decelerated growth in average age
Thank you for coming to my Analysis of the World’s Oldest Businesses.