Project Description
Planning and ensuring that a company survives changing market conditions is as vital as ever. Some businesses do this well and last for hundreds of years.
BusinessFinancing.co.uk researched the oldest companies on record and compiled the results. We'll explore that data. Like many business problems, the data is contained in several different datasets. To understand the world's oldest businesses, we will first need to use joining techniques to merge our data. From there, we can use manipulation tools such as grouping and filtering to answer questions about these historic businesses.
The database contains three tables.
1. Old meets New
Let's start by finding the minimum and the maximum years in the dataset, corresponding to the oldest and newest businesses on record.
SQL:
SELECT
min(year_founded),
max(year_founded)
FROM
businesses;
Tableau:
2. How many businesses were founded prior to 1000?
There is variation between countries. In one country, the oldest business was only founded in 1999. By contrast, the oldest business in the world was in 578. Incredible to think that a business has survived for more than a millennium.
I wonder how many other businesses there are like that.
SQL:
-- Get the count of rows in businesses where the founding year was before 1000
SELECT
count(business)
FROM
businesses
WHERE
year_founded < 1000;
Tableau:
3. Which businesses were founded before 1000?
Which businesses have been around for more than a millennium?
SQL:
-- Select all columns from businesses where the founding year was before 1000
-- Arrange the results from oldest to newest
SELECT
*
FROM
businesses
WHERE
year_founded < 1000
ORDER BY year_founded ASC;
Tableau:
4. Exploring the categories
Now we know the oldest continuously operating company globally is called Kongō Gumi. But what does that company do? The category codes in the businesses table aren't helpful: the descriptions of the categories are stored in the categories table.
This is a common problem: for data storage, it's better to keep different data types in other tables, but for analysis, you want all the data in one place. To solve this, we'll join the two tables together.
SQL:
-- Select business name, founding year, and country-code from businesses; and category from categories where the founding year was before 1000, arranged from oldest to newest
SELECT
b.business,
b.year_founded,
b.country_code,
c.category
FROM
businesses AS b
INNER JOIN
categories AS c
ON b.category_code = c.category_code
WHERE
year_founded < 1000
ORDER BY year_founded;
Tableau:
5. Counting the categories
With that extra detail about the oldest businesses, we can see that Kongō Gumi is a construction company. The two companies recorded as "Manufacturing and Production" are both mints. That is, they produce currency. We also see a café, a winery, and a bar in the list of six businesses.
I'm curious what other industries constitute the oldest companies worldwide and which industries are most common.
SQL:
-- Select the category and count of the category (as "n"), arranged by descending count, limited to 10 most common categories
SELECT
category,
count(*) AS n
FROM
categories AS c
INNER JOIN
businesses AS b
ON c.category_code = b.category_code
GROUP BY c.category_code
ORDER BY n DESC
LIMIT 10;
Tableau:
6. Oldest business by continent
It looks like "Banking & Finance" is the most popular category. Maybe that's where we should aim if we want to start a thousand-year business.
One thing we haven't looked at yet is where in the world these old businesses are. We'll need to join the businesses table to the countries table to answer these questions. Let's start by asking how old the oldest business is on each continent.
SQL:
-- Select the oldest founding year (as "oldest") from businesses, and continent from countries for each continent, ordered from oldest to newest
SELECT
MIN(year_founded) AS oldest,
continent
FROM
businesses AS b
INNER JOIN
countries AS c
ON b.country_code = c.country_code
GROUP BY continent
ORDER BY oldest;
Tableau:
7. Join everything for further analysis
There's a jump in time from the older businesses in Asia and Europe to the 16th Century oldest businesses in North and South America, then to the 18th and 19th Century oldest businesses in Africa and Oceania.
When analysing data, it's often helpful to have all the tables you want access to joined together into a single set of results. Here, that means we need to join all three tables.
SQL:
-- Select the business, founding year, category, country, and continent
SELECT
business,
year_founded,
category,
country,
continent
FROM
businesses AS b
INNER JOIN
countries AS c
ON b.country_code = c.country_code
INNER JOIN
categories AS d
ON d.category_code = b.category_code;
Tableau:
8. Counting categories by continent
Having businesses joined to categories and countries together means we can ask questions about these things together. For example, which are the most common categories for the oldest businesses on each continent?
SQL:
- Count the number of businesses in each continent and category
SELECT
continent,
category,
count(business) AS n
FROM
businesses AS b
INNER JOIN
countries AS c
ON b.country_code = c.country_code
INNER JOIN
categories AS d
ON d.category_code = b.category_code
GROUP BY continent, category;
Tableau:
9. Filtering counts by continent and category
Combining continent and business categories led to a lot of results. It isn't easy to see what is essential. To trim this down to a manageable size, let's restrict the results to only continent/category pairs with a high count.
SQL:
-- Repeat that previous query, filtering for results having a count greater than 5
SELECT
continent,
category,
count(business) AS n
FROM
businesses AS b
INNER JOIN
countries AS c
ON b.country_code = c.country_code
INNER JOIN
categories AS d
ON d.category_code = b.category_code
GROUP BY continent, category
HAVING COUNT(*) > 5
ORDER BY n DESC;
Tableau:
In this notebook, we looked at the oldest businesses around the globe, with the longest surviving business over 1000 years old. We also found Banking and Finance to be the most reliable industry from which long-lasting businesses are sustained.