Skip to content
Uncovering the World's Oldest Businesses
Staffelter Hof Winery is Germany's oldest business, established in 862 under the Carolingian dynasty. It has continued to serve customers through dramatic changes in Europe, such as the Holy Roman Empire, the Ottoman Empire, and both world wars. What characteristics enable a business to stand the test of time?
To help answer this question, BusinessFinancing.co.uk researched the oldest company still in business in almost every country and compiled the results into several CSV files.
The Data
businesses and new_businesses
| Column | Description |
|---|---|
business | Name of the business (varchar) |
year_founded | Year the business was founded (int) |
category_code | Code for the business category (varchar) |
country_code | ISO 3166-1 three-letter country code (char) |
countries
| Column | Description |
|---|---|
country_code | ISO 3166-1 three-letter country code (varchar) |
country | Name of the country (varchar) |
continent | Name of the continent the country exists in (varchar) |
categories
| Column | Description |
|---|---|
category_code | Code for the business category (varchar) |
category | Description of the business category (varchar) |
DataFrameas
oldest_business_continent
variable
-- What is the oldest business on each continent?
WITH oldest_ranked AS (
SELECT
continent,
country,
business,
year_founded,
ROW_NUMBER() OVER(PARTITION BY continent ORDER BY year_founded) as rank_n
FROM countries AS co
INNER JOIN businesses AS b
ON b.country_code = co.country_code
GROUP BY continent, country, business, year_founded
)
SELECT
continent,
country,
business,
year_founded
FROM oldest_ranked
WHERE rank_n = 1;DataFrameas
count_missing
variable
-- How many countries per continent lack data on the oldest businesses?
-- Does including the `new_businesses` data change this?
WITH missing_business AS (
SELECT
country,
SUM(CASE
WHEN b.business IS NULL AND nb.business IS NULL THEN 1 ELSE 0 END) AS countries_without_businesses
FROM countries AS c
FULL OUTER JOIN businesses AS b
ON b.country_code = c.country_code
FULL OUTER JOIN new_businesses AS nb
ON nb.country_code = c.country_code
GROUP BY country
)
SELECT
continent,
SUM(countries_without_businesses) AS countries_without_businesses
FROM missing_business
INNER JOIN countries AS c
ON c.country = missing_business.country
GROUP BY continent;DataFrameas
oldest_by_continent_category
variable
-- Which business categories are best suited to last over the course of centuries?
SELECT
continent,
category,
MIN(year_founded) AS year_founded
FROM countries AS co
INNER JOIN businesses AS b
ON b.country_code = co.country_code
INNER JOIN categories AS ca
ON ca.category_code = b.category_code
GROUP BY continent, category;