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. This dataset has been cleaned.
Having useful information in different files is a common problem. While it's better to keep different types of data separate for data storage, you'll want all the data in one place for analysis. You'll use joining and data manipulation to work with this data and better understand the world's oldest businesses.
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) |
-- What is the oldest business on each continent?
SELECT
ct.continent,
ct.country,
bs.business,
bs.year_founded
FROM businesses AS bs
INNER JOIN countries AS ct ON bs.country_code = ct.country_code
WHERE (bs.year_founded, ct.continent) IN (
SELECT MIN(bs2.year_founded), ct2.continent
FROM businesses AS bs2
INNER JOIN countries AS ct2 ON bs2.country_code = ct2.country_code
GROUP BY ct2.continent
)
ORDER BY bs.year_founded ASC;WITH combined_business AS (
SELECT country_code
FROM businesses
UNION ALL
SELECT country_code
FROM new_businesses
),
countries_without_businesses AS (
SELECT DISTINCT c.continent, c.country_code
FROM countries as c
LEFT JOIN combined_business AS cb ON c.country_code = cb.country_code
WHERE cb.country_code IS NULL
)
SELECT continent, COUNT(country_code) AS countries_without_businesses
FROM countries_without_businesses
GROUP BY continent;-- Which business categories are best suited to last over the course of centuries?
WITH continent_category_ages AS (
SELECT
ct.continent,
cat.category,
MIN(bs.year_founded) AS oldest_year
FROM businesses bs
INNER JOIN countries ct ON bs.country_code = ct.country_code
INNER JOIN categories cat ON bs.category_code = cat.category_code
GROUP BY ct.continent, cat.category
)
SELECT
continent,
category,
oldest_year AS year_founded
FROM continent_category_ages
ORDER BY continent, oldest_year ASC;