Uncovering the world's oldest business using advanced PostgreSQL techniques.
Introduction
Staffelter Hof Winery, established in 862 under the Carolingian dynasty, represents a rare and enduring business that has withstood dramatic historical changes over the centuries. With the goal of understanding what enables a business to stand the test of time, this project aimed to explore data related to the oldest companies still in business around the world. By analyzing datasets provided by BusinessFinancing.co.uk, the project sought to uncover patterns and trends related to the longevity of businesses.
The focus of the analysis was to leverage PostgreSQL to join multiple data sources and perform aggregations to answer the following key questions:
What is the oldest business on each continent?
How many countries per continent lack data on the oldest businesses?
Which business categories are best suited to last over the course of centuries?
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 continent, country, business, year_founded
FROM (SELECT b.business,
b.year_founded,
c.country,
c.continent,
ROW_NUMBER() OVER(PARTITION BY c.continent ORDER BY b.year_founded ASC) AS rank
FROM businesses AS b
INNER JOIN countries AS c
ON b.country_code = c.country_code) AS business_rank
WHERE rank = 1
-- How many countries per continent lack data on the oldest businesses?
SELECT c.continent,
COUNT(c.country) AS countries_without_businesses
FROM countries AS c
LEFT JOIN businesses AS b
ON c.country_code = b.country_code
WHERE business IS NULL
GROUP BY c.continent
-- Which business categories are best suited to last over the course of centuries?
SELECT continent,category,year_founded
FROM (SELECT c.continent,
cat.category,
b.year_founded,
ROW_NUMBER() OVER(PARTITION BY c.continent, cat.category ORDER BY b.year_founded ASC) AS rank
FROM businesses AS b
INNER JOIN countries AS c
ON b.country_code = c.country_code
INNER JOIN categories AS cat
ON b.category_code = cat.category_code) AS business_groups
WHERE rank = 1