Skip to content

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

ColumnDescription
businessName of the business (varchar)
year_foundedYear the business was founded (int)
category_codeCode for the business category (varchar)
country_codeISO 3166-1 three-letter country code (char)

countries

ColumnDescription
country_codeISO 3166-1 three-letter country code (varchar)
countryName of the country (varchar)
continentName of the continent the country exists in (varchar)

categories

ColumnDescription
category_codeCode for the business category (varchar)
categoryDescription of the business category (varchar)
Spinner
DataFrameas
oldest_business_continent
variable
SELECT continent, country, business,  year_founded
FROM public.businesses AS nb
LEFT JOIN public.countries AS c
ON nb.country_code = c.country_code
WHERE year_founded IN ( 
	SELECT MIN(year_founded)
	FROM public.businesses AS nb
	LEFT JOIN public.countries AS c
	ON nb.country_code = c.country_code
	GROUP BY continent)

from the above query result make an interpreatation from the query table result

The SQL query retrieves information about the oldest businesses on each continent. Here's a breakdown of the query and its results:

  1. Query Explanation:

    • The query selects the continent, country, business, and year_founded from the businesses table, which is joined with the countries table to get the continent information.
    • It uses a subquery to find the minimum year_founded for businesses on each continent, effectively identifying the oldest business on each continent.
    • The main query then retrieves the details of these oldest businesses.

  2. Result Interpretation:

    • The result is a table with columns: continent, country, business, and year_founded.
    • Each row in the result represents the oldest business on a specific continent, providing insights into the historical business landscape across different regions.
    • This information can be useful for understanding the geographical distribution of long-standing businesses and their historical significance in their respective regions.
Spinner
DataFrameas
count_missing
variable
-- How many countries per continent lack data on the oldest businesses
SELECT continent, COUNT(country) AS countries_without_businesses
FROM public.businesses AS b
FULL JOIN public.countries AS c
ON b.country_code = c.country_code
WHERE business IS NULL
GROUP BY continent
ORDER BY continent;

-- Does including the `new_businesses` data change this?
SELECT continent, COUNT(country) AS countries_without_businesses
FROM (SELECT *
FROM public.businesses AS b
UNION ALL
SELECT *
FROM new_businesses as nb) AS un

FULL JOIN public.countries AS c
ON un.country_code = c.country_code
WHERE un.business IS NULL
GROUP BY continent
ORDER BY continent;
Spinner
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 public.businesses AS b
INNER JOIN public.countries AS c
ON b.country_code = c.country_code
INNER JOIN public.categories
ON b.category_code = categories.category_code
GROUP BY continent, category
ORDER BY continent