Famous Paintings SQL Analysis
A self-directed SQL practice project exploring a relational dataset of 14,000+ paintings, artists, and museums (Maven Analytics' "Famous Paintings" dataset). Raw CSVs are cleaned in pandas, then queried in SQLite across three phases of increasing complexity — from basic filtering to window functions and multi-CTE aggregation.
Entity Relationship Diagram:
import pandas as pd
from sqlalchemy import create_engine
# Create a SQLAlchemy engine directly
engine = create_engine('sqlite:///paintings.db')
df_subject = pd.read_csv('/content/subject.csv')
df_subject.head()
| work_id | subject | |
|---|---|---|
| 0 | 160228 | Still-Life |
| 1 | 160236 | Still-Life |
| 2 | 160244 | Still-Life |
| 3 | 160252 | Still-Life |
| 4 | 160260 | Still-Life |
# The dimensionality of the DataFrame
df_subject.shape
(6771, 2)
# Display information about the DataFrame
df_subject.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 6771 entries, 0 to 6770 Data columns (total 2 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 work_id 6771 non-null int64 1 subject 6771 non-null object dtypes: int64(1), object(1) memory usage: 105.9+ KB
# Find the total number of duplicate rows in the DataFrame
df_subject.duplicated().sum()
np.int64(59)
# Remove duplicates
df_subject = df_subject.drop_duplicates()
df_subject.shape
(6712, 2)
df_subject.isnull().sum()
| 0 | |
|---|---|
| work_id | 0 |
| subject | 0 |
df_subject['subject'].unique()
array(['Still-Life', 'Horses', 'Marine Art/Maritime', 'Portraits',
'Musics', 'Dancers', 'U.S. Presidents', 'Dogs', 'Rivers/Lakes',
'Flowers', 'Bridges', 'Spring', 'Landscape Art', 'Autumn/Fall',
'Deers', 'Winter', 'Jesus Christ', 'Christianity', 'Nude',
'Gardens', 'Seascapes', 'Churches/Temples/Mosques', 'Cafes/Bars',
'Summer', 'Architectures', 'Lovers', 'Abstract/Modern Art',
'Water Lilies', 'Tigers'], dtype=object)
df_subject['subject'] = df_subject['subject'].replace({
'Musics' : 'Music',
'Deers' : 'Deer',
'Architectures' : 'Architecture'
})
df_subject['subject'].unique()
array(['Still-Life', 'Horses', 'Marine Art/Maritime', 'Portraits',
'Music', 'Dancers', 'U.S. Presidents', 'Dogs', 'Rivers/Lakes',
'Flowers', 'Bridges', 'Spring', 'Landscape Art', 'Autumn/Fall',
'Deer', 'Winter', 'Jesus Christ', 'Christianity', 'Nude',
'Gardens', 'Seascapes', 'Churches/Temples/Mosques', 'Cafes/Bars',
'Summer', 'Architecture', 'Lovers', 'Abstract/Modern Art',
'Water Lilies', 'Tigers'], dtype=object)
df_museum_hrs = pd.read_csv('/content/museum_hours.csv')
df_museum_hrs.head()
| museum_id | day | open | close | |
|---|---|---|---|---|
| 0 | 30 | Sunday | 10:30:AM | 05:30:PM |
| 1 | 30 | Monday | 10:30:AM | 05:30:PM |
| 2 | 30 | Tuesday | 10:30:AM | 05:30:PM |
| 3 | 30 | Wednesday | 10:30:AM | 05:30:PM |
| 4 | 30 | Thusday | 10:30:AM | 05:30:PM |
# The dimensionality of the DataFrame
df_museum_hrs.shape
(351, 4)
df_museum_hrs.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 351 entries, 0 to 350 Data columns (total 4 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 museum_id 351 non-null int64 1 day 351 non-null object 2 open 351 non-null object 3 close 351 non-null object dtypes: int64(1), object(3) memory usage: 11.1+ KB
df_museum_hrs.duplicated().sum()
np.int64(1)
df_museum_hrs = df_museum_hrs.drop_duplicates()
df_museum_hrs.shape
(350, 4)
df_museum_hrs.isnull().sum()
| 0 | |
|---|---|
| museum_id | 0 |
| day | 0 |
| open | 0 |
| close | 0 |
df_museum_hrs['day'].unique()
array(['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thusday', 'Friday',
'Saturday', 'Thursday'], dtype=object)
df_museum_hrs['day'] = df_museum_hrs['day'].str.replace('Thusday','Thursday', regex=False)
df_museum_hrs['day'].unique()
array(['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday',
'Saturday'], dtype=object)
df_museum_hrs['open'].unique()
array(['10:30:AM', '11:00:AM', '10:00:AM', '09:30:AM', '09:00:AM',
'04:00:PM', '01:00:PM', '12:30:PM', '11:30:AM', '08:15:AM',
'12:00:PM'], dtype=object)
df_museum_hrs['open'] = (
df_museum_hrs['open']
.str.replace(' ','', regex=False)
.str.replace(':AM', ' AM', regex=False)
.str.replace(':PM', ' PM', regex=False)
)
df_museum_hrs['open'].unique()
array(['10:30 AM', '11:00 AM', '10:00 AM', '09:30 AM', '09:00 AM',
'04:00 PM', '01:00 PM', '12:30 PM', '11:30 AM', '08:15 AM',
'12:00 PM'], dtype=object)
df_museum_hrs['close'].unique()
array(['05:30:PM', '07:00:PM', '06:00:PM', '05:00:PM', '08:00:PM',
'09:00:PM', '06:30:PM', '09:45:PM', '02:00:PM', '04:00:PM',
'05:15:PM', '08:45:PM', '04:30:PM', '03:30:PM', '08 :00:PM'],
dtype=object)
df_museum_hrs['close'] = (
df_museum_hrs['close']
.str.replace(' ','', regex=False)
.str.replace(':AM', ' AM', regex=False)
.str.replace(':PM', ' PM', regex=False)
)
df_museum_hrs['close'].unique()
array(['05:30 PM', '07:00 PM', '06:00 PM', '05:00 PM', '08:00 PM',
'09:00 PM', '06:30 PM', '09:45 PM', '02:00 PM', '04:00 PM',
'05:15 PM', '08:45 PM', '04:30 PM', '03:30 PM'], dtype=object)
df_museum_hrs
| museum_id | day | open | close | |
|---|---|---|---|---|
| 0 | 30 | Sunday | 10:30 AM | 05:30 PM |
| 1 | 30 | Monday | 10:30 AM | 05:30 PM |
| 2 | 30 | Tuesday | 10:30 AM | 05:30 PM |
| 3 | 30 | Wednesday | 10:30 AM | 05:30 PM |
| 4 | 30 | Thursday | 10:30 AM | 05:30 PM |
| ... | ... | ... | ... | ... |
| 346 | 86 | Tuesday | 10:00 AM | 06:00 PM |
| 347 | 86 | Wednesday | 10:00 AM | 06:00 PM |
| 348 | 86 | Thursday | 10:00 AM | 06:00 PM |
| 349 | 86 | Friday | 10:00 AM | 06:00 PM |
| 350 | 86 | Saturday | 10:00 AM | 06:00 PM |
350 rows × 4 columns
df_museum_hrs.head()
| museum_id | day | open | close | |
|---|---|---|---|---|
| 0 | 30 | Sunday | 10:30 AM | 05:30 PM |
| 1 | 30 | Monday | 10:30 AM | 05:30 PM |
| 2 | 30 | Tuesday | 10:30 AM | 05:30 PM |
| 3 | 30 | Wednesday | 10:30 AM | 05:30 PM |
| 4 | 30 | Thursday | 10:30 AM | 05:30 PM |
# Create a column to represent the museum number of operating hours
df_museum_hrs['open_hrs'] = (pd.to_datetime(df_museum_hrs['close'], format='%I:%M %p') - pd.to_datetime(df_museum_hrs['open'], format='%I:%M %p')).dt.total_seconds() / 3600
df_museum_hrs.head()
| museum_id | day | open | close | open_hrs | |
|---|---|---|---|---|---|
| 0 | 30 | Sunday | 10:30 AM | 05:30 PM | 7.0 |
| 1 | 30 | Monday | 10:30 AM | 05:30 PM | 7.0 |
| 2 | 30 | Tuesday | 10:30 AM | 05:30 PM | 7.0 |
| 3 | 30 | Wednesday | 10:30 AM | 05:30 PM | 7.0 |
| 4 | 30 | Thursday | 10:30 AM | 05:30 PM | 7.0 |
df_artist = pd.read_csv('/content/artist.csv')
df_artist.head()
| artist_id | full_name | first_name | middle_names | last_name | nationality | style | birth | death | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 500 | Pierre-Auguste Renoir | Pierre | Auguste | Renoir | French | Impressionist | 1841 | 1919 |
| 1 | 501 | Alexandre Cabanel | Alexandre | NaN | Cabanel | French | Classicist | 1823 | 1889 |
| 2 | 502 | James Ensor | James | NaN | Ensor | Belgian | Expressionist | 1860 | 1949 |
| 3 | 503 | Maximilien Luce | Maximilien | NaN | Luce | French | Pointillist | 1858 | 1941 |
| 4 | 504 | August Macke | August | NaN | Macke | German | Expressionist | 1887 | 1914 |
df_artist.shape
(421, 9)
df_artist.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 421 entries, 0 to 420 Data columns (total 9 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 artist_id 421 non-null int64 1 full_name 421 non-null object 2 first_name 421 non-null object 3 middle_names 148 non-null object 4 last_name 421 non-null object 5 nationality 421 non-null object 6 style 421 non-null object 7 birth 421 non-null int64 8 death 421 non-null int64 dtypes: int64(3), object(6) memory usage: 29.7+ KB
df_artist.duplicated().sum()
np.int64(0)
df_artist.isnull().sum()
| 0 | |
|---|---|
| artist_id | 0 |
| full_name | 0 |
| first_name | 0 |
| middle_names | 273 |
| last_name | 0 |
| nationality | 0 |
| style | 0 |
| birth | 0 |
| death | 0 |
df_artist_missing_vals = df_artist.isnull().sum().reset_index()
df_artist_missing_vals.columns = ['columns', 'nr_of_missingValues']
df_artist_missing_vals['perc_of_missingValues'] = (df_artist_missing_vals['nr_of_missingValues']/df_artist.shape[0]* 100).round(2)
df_artist_missing_vals[df_artist_missing_vals['perc_of_missingValues'] != 0]
| columns | nr_of_missingValues | perc_of_missingValues | |
|---|---|---|---|
| 3 | middle_names | 273 | 64.85 |
# Remove 'middle_names' column
df_artist.drop('middle_names', axis=1 , inplace=True)
df_artist.columns
Index(['artist_id', 'full_name', 'first_name', 'last_name', 'nationality',
'style', 'birth', 'death'],
dtype='object')
# Distinct art styles
df_artist['style'].unique()
array(['Impressionist', 'Classicist', 'Expressionist', 'Pointillist',
'Rococo', 'Naturalist', 'American West', 'Landscape Art',
'Northern Renaissance', 'Modern Art', 'Portraitist',
'Hudson River School', 'Marine Art', 'Realist', 'Romantic',
'Colonial', 'Baroque', 'Avant-Garde', 'Post-Impressionist',
'Symbolist', 'Nabi', 'Mannerist', 'High Renaissance',
'Neoclassical', 'Futurist', 'Art Nouveau', 'Cubist', 'Fauvist',
'Renaissance', 'Ukiyo-e', 'Early Renaissance', 'Surrealist',
'America West', 'Orientalist', 'Folk'], dtype=object)
df_museum = pd.read_csv('/content/museum.csv')
df_museum.head()
| museum_id | name | address | city | state | postal | country | phone | url | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 30 | The Museum of Modern Art | 11 W 53rd St | New York | NY | 10019 | USA | +1 212 708-9400 | https://www.moma.org/ |
| 1 | 31 | Pushkin State Museum of Fine Arts | 12 Ulitsa Volkhonka | Moscow | NaN | 119019 | Russia | +7 495 697-95-78 | https://pushkinmuseum.art/ |
| 2 | 32 | National Gallery of Victoria | 180 St Kilda Rd | Melbourne | Victoria | 3004 | Australia | +61 (0)3 8620 2222 | https://www.ngv.vic.gov.au/ |
| 3 | 33 | São Paulo Museum of Art | Av. Paulista, 1578 - Bela Vista | São Paulo | NaN | 01310-200 | Brazil | +55 11 3149-5959 | https://masp.org.br/ |
| 4 | 34 | The State Hermitage Museum | Palace Square | 2 | Sankt-Peterburg | 190000 | Russia | 7 812 710-90-79 | https://www.hermitagemuseum.org/wps/portal/her... |
df_museum.shape
(57, 9)
df_museum.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 57 entries, 0 to 56 Data columns (total 9 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 museum_id 57 non-null int64 1 name 57 non-null object 2 address 57 non-null object 3 city 57 non-null object 4 state 38 non-null object 5 postal 50 non-null object 6 country 57 non-null object 7 phone 57 non-null object 8 url 57 non-null object dtypes: int64(1), object(8) memory usage: 4.1+ KB
df_museum.duplicated().sum()
np.int64(0)
df_museum.isnull().sum()
| 0 | |
|---|---|
| museum_id | 0 |
| name | 0 |
| address | 0 |
| city | 0 |
| state | 19 |
| postal | 7 |
| country | 0 |
| phone | 0 |
| url | 0 |
# Remove columns with missing values
df_museum.drop(['state', 'postal'], axis=1 , inplace=True)
df_museum.columns
Index(['museum_id', 'name', 'address', 'city', 'country', 'phone', 'url'], dtype='object')
df_museum['country'].unique()
array(['USA', 'Russia', 'Australia', 'Brazil', 'Germany', 'France',
'United Kingdom', 'Netherlands', 'Israel', 'Switzerland', 'UK',
'Spain', 'Hungary', 'Italy', 'Japan', 'Norway', 'Czechia'],
dtype=object)
df_museum['country'] = df_museum['country'].replace({
'United Kingdom' : 'UK' ,
'UK' : 'UK'
})
df_museum['country'].unique()
array(['USA', 'Russia', 'Australia', 'Brazil', 'Germany', 'France', 'UK',
'Netherlands', 'Israel', 'Switzerland', 'Spain', 'Hungary',
'Italy', 'Japan', 'Norway', 'Czechia'], dtype=object)
df_productSize = pd.read_csv('/content/product_size.csv')
df_productSize.head()
| work_id | size_id | sale_price | regular_price | |
|---|---|---|---|---|
| 0 | 160228 | 24 | 85 | 85 |
| 1 | 160228 | 30 | 95 | 95 |
| 2 | 160236 | 24 | 85 | 85 |
| 3 | 160236 | 30 | 95 | 95 |
| 4 | 160244 | 24 | 85 | 85 |
df_productSize.shape
(110347, 4)
df_productSize.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 110347 entries, 0 to 110346 Data columns (total 4 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 work_id 110347 non-null int64 1 size_id 110347 non-null object 2 sale_price 110347 non-null int64 3 regular_price 110347 non-null int64 dtypes: int64(3), object(1) memory usage: 3.4+ MB
size_id¶df_productSize['size_id'].unique()
array(['24', '30', '3024', '3226', '3629', '4030', '4836', '6048', '6854',
'3624', '4630', '5436', '6040', '7248', '20', '2430', '2632',
'2936', '3040', '3648', '4860', '5468', '36', '2436', '3046',
'3654', '4060', '4872', '1620', '2024', '1624', '2030', '40', '48',
'4020', '4824', '6030', '7236', '8040', '9648', '2424', '3030',
'3636', '4040', '4848', '6060', '2016', '2420', '2020', '2416',
'3020', '#VALUE!', '1632', '2040', '2448', '3060', '3672', '4080',
'16.223', '28.836', '39.532', '3216', '56', '3628.7', '32.226',
'3225.5', '4896', '3218', '32.426', '3239.8', '2936.5', '3225.6',
'3223.7', '36.329', '36.432', '36.532', '27.326', '31.944',
'28.525', '3232', '57.745', '4434', '3225.9', '2417.8', '7171',
'5136.5', '5439.4', '22.43', '26.232', '2429', '50.635', '39.632',
'3225.8', '3730', '3627', '35.652', '4235.5', '5062.6', '2823.2',
'2419.7', '25.632', '37.428', '2921.7', '2921.5', '2639.8',
'32.251', '31.525', '5936.2', '2923.6', '2923.7', '25.732',
'2632.1', '57.542', '3536.5', '38.531', '5691.2', '3225.7',
'32.326', '39.526', '23.529', '4023.7', '36.429', '36.326',
'36.829', '32.524', '36.529', '39.435', '79.163', '36.626',
'3239.4', '39.429', '5844.9', '3239.2', '57.545', '25.832',
'32.332', '28.824', '3628.6', '4635.4', '4635', '2632.3', '50.465',
'4835.8', '19.524', '28.936', '3246', '3628.5', '44.534', '3628.8',
'45.835', '28.537', '32.126', '28.724', '3729.3', '37.53',
'29.137', '3527.6', '2227.2', '4532', '3123.6', '3729.9', '26.332',
'2936.6', '35.628'], dtype=object)
# Convert the column to numeric, errors='coerce' will set non-numeric values to NaN
df_productSize['size_id'] = pd.to_numeric(df_productSize['size_id'], errors='coerce')
df_productSize['size_id'].unique()
array([ 24. , 30. , 3024. , 3226. , 3629. , 4030. ,
4836. , 6048. , 6854. , 3624. , 4630. , 5436. ,
6040. , 7248. , 20. , 2430. , 2632. , 2936. ,
3040. , 3648. , 4860. , 5468. , 36. , 2436. ,
3046. , 3654. , 4060. , 4872. , 1620. , 2024. ,
1624. , 2030. , 40. , 48. , 4020. , 4824. ,
6030. , 7236. , 8040. , 9648. , 2424. , 3030. ,
3636. , 4040. , 4848. , 6060. , 2016. , 2420. ,
2020. , 2416. , 3020. , nan, 1632. , 2040. ,
2448. , 3060. , 3672. , 4080. , 16.223, 28.836,
39.532, 3216. , 56. , 3628.7 , 32.226, 3225.5 ,
4896. , 3218. , 32.426, 3239.8 , 2936.5 , 3225.6 ,
3223.7 , 36.329, 36.432, 36.532, 27.326, 31.944,
28.525, 3232. , 57.745, 4434. , 3225.9 , 2417.8 ,
7171. , 5136.5 , 5439.4 , 22.43 , 26.232, 2429. ,
50.635, 39.632, 3225.8 , 3730. , 3627. , 35.652,
4235.5 , 5062.6 , 2823.2 , 2419.7 , 25.632, 37.428,
2921.7 , 2921.5 , 2639.8 , 32.251, 31.525, 5936.2 ,
2923.6 , 2923.7 , 25.732, 2632.1 , 57.542, 3536.5 ,
38.531, 5691.2 , 3225.7 , 32.326, 39.526, 23.529,
4023.7 , 36.429, 36.326, 36.829, 32.524, 36.529,
39.435, 79.163, 36.626, 3239.4 , 39.429, 5844.9 ,
3239.2 , 57.545, 25.832, 32.332, 28.824, 3628.6 ,
4635.4 , 4635. , 2632.3 , 50.465, 4835.8 , 19.524,
28.936, 3246. , 3628.5 , 44.534, 3628.8 , 45.835,
28.537, 32.126, 28.724, 3729.3 , 37.53 , 29.137,
3527.6 , 2227.2 , 4532. , 3123.6 , 3729.9 , 26.332,
2936.6 , 35.628])
# Remove all rows where 'size_id' has a NaN value
df_productSize = df_productSize.dropna(subset = ['size_id'])
# Filter out non-integer values
df_productSize= df_productSize[df_productSize['size_id'].apply(lambda x: x.is_integer() if pd.notnull(x) else False)]
# Convert column to an integer data type
df_productSize['size_id'] = df_productSize['size_id'].astype(int)
df_productSize.info()
<class 'pandas.core.frame.DataFrame'> Index: 110019 entries, 0 to 110346 Data columns (total 4 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 work_id 110019 non-null int64 1 size_id 110019 non-null int64 2 sale_price 110019 non-null int64 3 regular_price 110019 non-null int64 dtypes: int64(4) memory usage: 4.2 MB
# Display the unique size_ids
df_productSize['size_id'].unique()
array([ 24, 30, 3024, 3226, 3629, 4030, 4836, 6048, 6854, 3624, 4630,
5436, 6040, 7248, 20, 2430, 2632, 2936, 3040, 3648, 4860, 5468,
36, 2436, 3046, 3654, 4060, 4872, 1620, 2024, 1624, 2030, 40,
48, 4020, 4824, 6030, 7236, 8040, 9648, 2424, 3030, 3636, 4040,
4848, 6060, 2016, 2420, 2020, 2416, 3020, 1632, 2040, 2448, 3060,
3672, 4080, 3216, 56, 4896, 3218, 3232, 4434, 7171, 2429, 3730,
3627, 4635, 3246, 4532])
df_productSize.duplicated().sum()
np.int64(687)
df_productSize = df_productSize.drop_duplicates()
df_productSize.shape
(109332, 4)
df_productSize.isnull().sum()
| 0 | |
|---|---|
| work_id | 0 |
| size_id | 0 |
| sale_price | 0 |
| regular_price | 0 |
df_imageLink = pd.read_csv('/content/image_link.csv')
df_imageLink.head()
| work_id | url | thumbnail_small_url | thumbnail_large_url | |
|---|---|---|---|---|
| 0 | 181978 | https://v5.airtableusercontent.com/v1/15/15/16... | https://v5.airtableusercontent.com/v1/15/15/16... | https://v5.airtableusercontent.com/v1/15/15/16... |
| 1 | 173188 | https://v5.airtableusercontent.com/v1/15/15/16... | https://v5.airtableusercontent.com/v1/15/15/16... | https://v5.airtableusercontent.com/v1/15/15/16... |
| 2 | 194065 | https://v5.airtableusercontent.com/v1/15/15/16... | https://v5.airtableusercontent.com/v1/15/15/16... | https://v5.airtableusercontent.com/v1/15/15/16... |
| 3 | 129337 | https://v5.airtableusercontent.com/v1/15/15/16... | https://v5.airtableusercontent.com/v1/15/15/16... | https://v5.airtableusercontent.com/v1/15/15/16... |
| 4 | 141073 | https://v5.airtableusercontent.com/v1/15/15/16... | https://v5.airtableusercontent.com/v1/15/15/16... | https://v5.airtableusercontent.com/v1/15/15/16... |
df_imageLink.shape
(14775, 4)
df_imageLink.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 14775 entries, 0 to 14774 Data columns (total 4 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 work_id 14775 non-null int64 1 url 14775 non-null object 2 thumbnail_small_url 14773 non-null object 3 thumbnail_large_url 14773 non-null object dtypes: int64(1), object(3) memory usage: 461.8+ KB
df_imageLink.duplicated().sum()
np.int64(60)
df_imageLink = df_imageLink.drop_duplicates()
df_imageLink.shape
(14715, 4)
df_imageLink.isnull().sum()
| 0 | |
|---|---|
| work_id | 0 |
| url | 0 |
| thumbnail_small_url | 2 |
| thumbnail_large_url | 2 |
# Remove all rows in specified columns where atleast 1 value is missing
df_imageLink = df_imageLink.dropna(subset=['thumbnail_small_url', 'thumbnail_large_url'])
df_work = pd.read_csv('/content/work.csv')
df_work.head()
| work_id | name | artist_id | style | museum_id | |
|---|---|---|---|---|---|
| 0 | 160228 | Still Life with Flowers and a Watch | 615 | Baroque | 43.0 |
| 1 | 160236 | Still Life with Fruit and a Beaker on a Cock's... | 615 | Baroque | 43.0 |
| 2 | 160244 | Still Life with Fruit and a Goldfinch | 615 | Baroque | 43.0 |
| 3 | 160252 | Still Life with Fruit and Oysters | 615 | Baroque | 43.0 |
| 4 | 160260 | Still Life with Fruit, Oysters, and a Porcelai... | 615 | Baroque | 43.0 |
df_work.shape
(14776, 5)
df_work.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 14776 entries, 0 to 14775 Data columns (total 5 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 work_id 14776 non-null int64 1 name 14776 non-null object 2 artist_id 14776 non-null int64 3 style 13490 non-null object 4 museum_id 4553 non-null float64 dtypes: float64(1), int64(2), object(2) memory usage: 577.3+ KB
df_work.duplicated().sum()
np.int64(60)
df_work = df_work.drop_duplicates()
df_work.shape
(14716, 5)
df_work.isnull().sum()
| 0 | |
|---|---|
| work_id | 0 |
| name | 0 |
| artist_id | 0 |
| style | 1228 |
| museum_id | 10163 |
df_work_missing_vals = df_work.isnull().sum().reset_index()
df_work_missing_vals.columns = ['columns', 'nr_of_missingValues']
df_work_missing_vals['perc_of_missingValues'] = (df_work_missing_vals['nr_of_missingValues']/df_work.shape[0]* 100).round(2)
df_work_missing_vals[df_work_missing_vals['perc_of_missingValues'] != 0]
| columns | nr_of_missingValues | perc_of_missingValues | |
|---|---|---|---|
| 3 | style | 1228 | 8.34 |
| 4 | museum_id | 10163 | 69.06 |
# Fill missing values on style column with 'Other'
df_work['style'] = df_work['style'].fillna(value='Other')
df_work['style'].unique()
array(['Baroque', 'Other', 'Neo-Classicism', 'Renaissance',
'Expressionism', 'American Landscape', 'Post-Impressionism',
'Classicism', 'Avant-Garde', 'Impressionism', 'Cubism', 'Rococo',
'Realism', 'Fauvism', 'Nabi', 'Symbolism', 'Naturalism',
'American Art', 'Romanticism', 'Orientalism', 'Art Nouveau',
'Surrealism', 'Pointillism', 'Japanese Art'], dtype=object)
df_canvasSize = pd.read_csv('/content/canvas_size.csv')
df_canvasSize.head()
| size_id | width | height | label | |
|---|---|---|---|---|
| 0 | 20 | 20 | NaN | 20" Long Edge |
| 1 | 24 | 24 | NaN | 24" Long Edge |
| 2 | 30 | 30 | NaN | 30" Long Edge |
| 3 | 36 | 36 | NaN | 36" Long Edge |
| 4 | 40 | 40 | NaN | 40" Long Edge |
df_canvasSize.shape
(200, 4)
df_canvasSize.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 200 entries, 0 to 199 Data columns (total 4 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 size_id 200 non-null int64 1 width 200 non-null int64 2 height 193 non-null float64 3 label 200 non-null object dtypes: float64(1), int64(2), object(1) memory usage: 6.4+ KB
df_canvasSize.duplicated().sum()
np.int64(0)
df_canvasSize.isnull().sum()
| 0 | |
|---|---|
| size_id | 0 |
| width | 0 |
| height | 7 |
| label | 0 |
# Initialize a new engine that can interface with the SQLite database
engine = create_engine('sqlite:///paintings.db')
# Connect to the database
conn = engine.connect()
# Write DataFrames to SQL database
df_subject.to_sql('subject', engine, if_exists='replace', index=False)
df_museum_hrs.to_sql('museum_hrs', engine, if_exists='replace', index=False)
df_artist.to_sql('artist', engine, if_exists='replace', index=False)
df_museum.to_sql('museum', engine, if_exists='replace',index=False)
df_productSize.to_sql('product_size', engine, if_exists='replace', index=False)
df_imageLink.to_sql('image_link', engine, if_exists='replace', index=False)
df_work.to_sql('work', engine, if_exists='replace', index=False)
df_canvasSize.to_sql('canvas_size', engine, if_exists='replace', index=False)
200
List all tables in the database:
query = """
SELECT
name
FROM sqlite_master
WHERE type='table';
"""
pd.read_sql(query, engine)
| name | |
|---|---|
| 0 | subject |
| 1 | museum_hrs |
| 2 | artist |
| 3 | museum |
| 4 | product_size |
| 5 | image_link |
| 6 | work |
| 7 | canvas_size |
query = """
SELECT
COUNT (DISTINCT work_id)
FROM product_size
"""
pd.read_sql(query, engine)
| COUNT (DISTINCT work_id) | |
|---|---|
| 0 | 14630 |
query = """
SELECT
COUNT(work_id)
FROM work
WHERE museum_id IS NULL
"""
pd.read_sql(query,engine)
| COUNT(work_id) | |
|---|---|
| 0 | 10163 |
query = """
SELECT
-- Artists with nr of paintings not assigned to a museum
a.full_name as artist,
COUNT(*) as nr_of_paintings
FROM work AS w
LEFT JOIN artist AS a
ON w.artist_id = a.artist_id
WHERE w.museum_id IS NULL
GROUP BY 1
ORDER BY 2 DESC;
"""
pd.read_sql(query, engine)
| artist | nr_of_paintings | |
|---|---|---|
| 0 | Maurice Utrillo | 251 |
| 1 | Albert Marquet | 231 |
| 2 | Henri Lebasque | 200 |
| 3 | Pierre-Auguste Renoir | 197 |
| 4 | Claude Monet | 186 |
| ... | ... | ... |
| 357 | Charles Caleb Ward | 1 |
| 358 | Carel Fabritius | 1 |
| 359 | Benjamin West | 1 |
| 360 | Bartolomé Esteban Murillo | 1 |
| 361 | Andrea Mantegna | 1 |
362 rows × 2 columns
query = """
SELECT
subject,
COUNT(*) AS nr_of_paintings
FROM subject
GROUP BY 1
ORDER BY nr_of_paintings DESC
LIMIT 10;
"""
pd.read_sql(query, engine)
| subject | nr_of_paintings | |
|---|---|---|
| 0 | Portraits | 1070 |
| 1 | Nude | 525 |
| 2 | Landscape Art | 495 |
| 3 | Rivers/Lakes | 480 |
| 4 | Flowers | 457 |
| 5 | Abstract/Modern Art | 399 |
| 6 | Still-Life | 395 |
| 7 | Seascapes | 323 |
| 8 | Marine Art/Maritime | 268 |
| 9 | Horses | 265 |
query = """
SELECT
m.country,
COUNT(w.work_id) AS nr_of_paintings
FROM museum AS m
LEFT JOIN work AS w
ON m.museum_id = w.museum_id
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;
"""
pd.read_sql(query, engine)
| country | nr_of_paintings | |
|---|---|---|
| 0 | USA | 2672 |
query = """
SELECT
COUNT(work_id) AS nr_of_paintings
FROM image_link AS il
WHERE thumbnail_small_url IS NULL
AND thumbnail_large_url IS NULL
"""
pd.read_sql(query, engine)
| nr_of_paintings | |
|---|---|
| 0 | 0 |
query = """
SELECT
m.museum_id,
m.name
FROM museum AS m
INNER JOIN museum_hrs AS mh
ON m.museum_id = mh.museum_id
WHERE day in ('Sunday', 'Monday')
GROUP BY 1,2
HAVING COUNT(DISTINCT mh.day) = 2;
"""
pd.read_sql(query, engine)
| museum_id | name | |
|---|---|---|
| 0 | 30 | The Museum of Modern Art |
| 1 | 31 | Pushkin State Museum of Fine Arts |
| 2 | 32 | National Gallery of Victoria |
| 3 | 35 | The Metropolitan Museum of Art |
| 4 | 37 | Museum of Grenoble |
| 5 | 39 | Nelson-Atkins Museum of Art |
| 6 | 40 | Musée du Louvre |
| 7 | 41 | National Maritime Museum |
| 8 | 42 | Museum of Fine Arts Boston |
| 9 | 43 | Rijksmuseum |
| 10 | 44 | Israel Museum |
| 11 | 46 | National Gallery of Art |
| 12 | 47 | National Gallery |
| 13 | 48 | Mauritshuis Museum |
| 14 | 50 | The Prado Museum |
| 15 | 51 | The Barnes Foundation |
| 16 | 57 | Van Gogh Museum |
| 17 | 60 | Los Angeles County Museum of Art |
| 18 | 61 | Solomon R. Guggenheim Museum |
| 19 | 62 | The Tate Gallery |
| 20 | 64 | Museum of Fine Arts of Nancy |
| 21 | 66 | Smithsonian American Art Museum |
| 22 | 67 | Philadelphia Museum of Art |
| 23 | 68 | The Art Institute of Chicago |
| 24 | 82 | Army Museum |
| 25 | 84 | National Gallery Prague |
| 26 | 85 | Norton Simon Museum |
| 27 | 86 | Courtauld Gallery |
query = """
SELECT
m.name,
m.city,
m.country
FROM museum AS m
INNER JOIN museum_hrs AS mh
ON m.museum_id = mh.museum_id
GROUP BY 1,2, 3
HAVING COUNT(DISTINCT mh.day) = 7
ORDER BY country ASC;
"""
pd.read_sql(query, engine)
| name | city | country | |
|---|---|---|---|
| 0 | National Gallery of Victoria | Melbourne | Australia |
| 1 | National Gallery Prague | Nové Měst | Czechia |
| 2 | Army Museum | Paris | France |
| 3 | Israel Museum | Jerusalem | Israel |
| 4 | Mauritshuis Museum | Den Haag | Netherlands |
| 5 | Rijksmuseum | Amsterdam | Netherlands |
| 6 | Van Gogh Museum | Amsterdam | Netherlands |
| 7 | Pushkin State Museum of Fine Arts | Moscow | Russia |
| 8 | The Prado Museum | Madrid | Spain |
| 9 | Courtauld Gallery | Stran | UK |
| 10 | National Gallery | London | UK |
| 11 | National Maritime Museum | London | UK |
| 12 | The Tate Gallery | London | UK |
| 13 | Smithsonian American Art Museum | Washington | USA |
| 14 | Solomon R. Guggenheim Museum | New York | USA |
| 15 | The Metropolitan Museum of Art | New York | USA |
| 16 | The Museum of Modern Art | New York | USA |
query = """
WITH french_artists AS (
SELECT *
FROM artist
WHERE nationality = 'French'
)
SELECT
f.full_name AS artist,
w.name AS work,
m.name AS museum,
m.country,
m.city
FROM french_artists AS f
INNER JOIN work AS w
ON f.artist_id = w.artist_id
INNER JOIN museum AS m
ON m.museum_id = w.museum_id
WHERE m.country != 'France'
ORDER BY artist ASC
"""
pd.read_sql(query, engine)
| artist | work | museum | country | city | |
|---|---|---|---|---|---|
| 0 | Adèle Romany | Portrait de la Famille de l'Artiste Posant Dev... | National Gallery of Art | USA | Washington |
| 1 | Adèle Romany | Portrait of Auguste Vestris, French Dancer | National Gallery | UK | London |
| 2 | Adélaïde Labille-Guiard | Madame de Genlis | Los Angeles County Museum of Art | USA | Los Angeles |
| 3 | Adélaïde Labille-Guiard | Portrait of Madame Labille-Guyard and Her Pupils | The Metropolitan Museum of Art | USA | New York |
| 4 | Albert André | Bouquet of Roses and Fruit | The Metropolitan Museum of Art | USA | New York |
| ... | ... | ... | ... | ... | ... |
| 1595 | Élisabeth Vigée-LeBrun | Portrait of Princess Galitzin | National Gallery | UK | London |
| 1596 | Élisabeth Vigée-LeBrun | Princess Elisabeth of France | The Tate Gallery | UK | London |
| 1597 | Élisabeth Vigée-LeBrun | Self-Portrait in a Straw Hat | National Gallery | UK | London |
| 1598 | Élisabeth Vigée-LeBrun | The Marquise of Rouge with Her Sons Alexis and... | The Metropolitan Museum of Art | USA | New York |
| 1599 | Émile Bernard | Self-Portrait | Rijksmuseum | Netherlands | Amsterdam |
1600 rows × 5 columns
query = """
SELECT
ps.size_id,
cs.height,
cs.width,
COUNT(*) AS nr_of_paintings,
ROUND(AVG(ps.regular_price),2) AS avg_regular_price
FROM product_size AS ps
INNER JOIN canvas_size AS cs
ON ps.size_id = cs.size_id
WHERE cs.height IS NOT NULL
AND cs.width IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY nr_of_paintings DESC;
"""
pd.read_sql(query, engine)
| size_id | height | width | nr_of_paintings | avg_regular_price | |
|---|---|---|---|---|---|
| 0 | 3040 | 40.0 | 30 | 4563 | 695.17 |
| 1 | 3648 | 48.0 | 36 | 4563 | 874.92 |
| 2 | 2936 | 36.0 | 29 | 4535 | 635.10 |
| 3 | 4860 | 60.0 | 48 | 4466 | 1234.64 |
| 4 | 4030 | 30.0 | 40 | 4254 | 683.36 |
| ... | ... | ... | ... | ... | ... |
| 58 | 3627 | 27.0 | 36 | 1 | 595.00 |
| 59 | 3730 | 30.0 | 37 | 1 | 595.00 |
| 60 | 4434 | 34.0 | 44 | 1 | 675.00 |
| 61 | 4532 | 32.0 | 45 | 1 | 685.00 |
| 62 | 4635 | 35.0 | 46 | 1 | 855.00 |
63 rows × 5 columns
query = """
SELECT
w.name,
a.full_name,
p.sale_price,
p.regular_price
FROM work as w
INNER JOIN product_size AS p
ON w.work_id = p.work_id
INNER JOIN artist AS a
ON w.artist_id = a.artist_id
WHERE p.sale_price < 0.5 * regular_price
"""
pd.read_sql(query, engine)
| name | full_name | sale_price | regular_price | |
|---|---|---|---|---|
| 0 | Portrait of Madame Labille-Guyard and Her Pupils | Adélaïde Labille-Guiard | 10 | 125 |
| 1 | Portrait of Madame Labille-Guyard and Her Pupils | Adélaïde Labille-Guiard | 10 | 95 |
| 2 | Disrobing of Christ (Espolio) | El Greco | 30 | 125 |
| 3 | Disrobing of Christ (Espolio) | El Greco | 30 | 95 |
| 4 | Taking the Census | Francis William Edmonds | 30 | 85 |
| 5 | The Chalon Family in London | Jacques Laurent Agasse | 10 | 85 |
| 6 | The Chalon Family in London | Jacques Laurent Agasse | 10 | 95 |
| 7 | The Chalon Family in London | Jacques Laurent Agasse | 10 | 125 |
| 8 | Ducks Resting in Sunshine | Jean-Baptiste Oudry | 285 | 575 |
| 9 | Ducks Resting in Sunshine | Jean-Baptiste Oudry | 305 | 645 |
| 10 | Portrait of Mr. and Mrs. Thomas Mifflin (Sarah... | John Singleton Copley | 20 | 95 |
| 11 | Portrait of Mr. and Mrs. Thomas Mifflin (Sarah... | John Singleton Copley | 20 | 85 |
| 12 | Passion Flowers and Hummingbirds | Martin Johnson Heade | 585 | 1245 |
| 13 | Eucharist | Nicolas Poussin | 40 | 95 |
| 14 | Eucharist | Nicolas Poussin | 40 | 85 |
| 15 | Extreme Unction | Nicolas Poussin | 40 | 85 |
| 16 | Extreme Unction | Nicolas Poussin | 40 | 95 |
| 17 | Marriage | Nicolas Poussin | 40 | 85 |
| 18 | Marriage | Nicolas Poussin | 40 | 95 |
| 19 | Saint John Baptizing in the River Jordan | Nicolas Poussin | 60 | 165 |
| 20 | Saint John Baptizing in the River Jordan | Nicolas Poussin | 60 | 145 |
| 21 | Saint John Baptizing in the River Jordan | Nicolas Poussin | 60 | 125 |
| 22 | The Adoration of the Shepherds | Nicolas Poussin | 40 | 95 |
| 23 | The Adoration of the Shepherds | Nicolas Poussin | 40 | 125 |
| 24 | The Companions of Rinaldo | Nicolas Poussin | 30 | 75 |
| 25 | The Companions of Rinaldo | Nicolas Poussin | 30 | 85 |
| 26 | The Companions of Rinaldo | Nicolas Poussin | 30 | 95 |
| 27 | The Finding of Moses | Nicolas Poussin | 50 | 125 |
| 28 | The Nurture of Bacchus | Nicolas Poussin | 50 | 125 |
| 29 | Rest on the Flight Into Egypt | Orazio Gentileschi | 635 | 1275 |
| 30 | Bowl of Fruit and Tankard Before a Window | Paul Gauguin | 505 | 1015 |
| 31 | Avenue of Trees by a Stream | Theodor Von Hörmann | 335 | 695 |
| 32 | Southern Landscape | Theodor Von Hörmann | 335 | 695 |
| 33 | Summer in the Garden | Theodor Von Hörmann | 335 | 695 |
| 34 | Racehorses | Edgar Degas | 245 | 515 |
| 35 | Racehorses at Longchamp | Edgar Degas | 305 | 615 |
| 36 | Young Woman in a Garden | Édouard Manet | 305 | 615 |
| 37 | Young Woman in a Garden | Édouard Manet | 385 | 775 |
| 38 | Young Woman Reclining in Spanish Costume | Édouard Manet | 305 | 615 |
| 39 | Young Woman Reclining in Spanish Costume | Édouard Manet | 385 | 775 |
| 40 | Sun Rising Through Vapour | Joseph M. W. Turner | 30 | 125 |
| 41 | Sun Rising Through Vapour | Joseph M. W. Turner | 30 | 95 |
| 42 | Sun Rising Through Vapour | Joseph M. W. Turner | 30 | 85 |
| 43 | The Victory Returning from Trafalgar, in Three... | Joseph M. W. Turner | 30 | 85 |
| 44 | The Victory Returning from Trafalgar, in Three... | Joseph M. W. Turner | 30 | 95 |
| 45 | The Victory Returning from Trafalgar, in Three... | Joseph M. W. Turner | 30 | 125 |
| 46 | Venice, The Bridge of Sighs | Joseph M. W. Turner | 30 | 85 |
| 47 | Venice, The Bridge of Sighs | Joseph M. W. Turner | 30 | 95 |
| 48 | Three Tahitian Women Against a Yellow Background | Paul Gauguin | 505 | 1015 |
| 49 | Young Girl Standing in Front of a Wheatfield | Vincent Van Gogh | 615 | 1255 |
| 50 | The Blue Boy | Winslow Homer | 315 | 635 |
| 51 | The Initials | Winslow Homer | 305 | 625 |
| 52 | The Initials | Winslow Homer | 385 | 775 |
query = """
WITH artists_paintings AS (
SELECT
a.artist_id,
a.full_name,
COUNT(w.work_id) AS nr_of_paintings
FROM artist AS a
INNER JOIN work AS w
ON a.artist_id = w.artist_id
GROUP BY 1,2
)
SELECT
*,
RANK()OVER(
ORDER BY nr_of_paintings DESC
) AS rank
FROM artists_paintings
"""
pd.read_sql(query, engine)
| artist_id | full_name | nr_of_paintings | rank | |
|---|---|---|---|---|
| 0 | 500 | Pierre-Auguste Renoir | 469 | 1 |
| 1 | 550 | Claude Monet | 378 | 2 |
| 2 | 677 | Vincent Van Gogh | 308 | 3 |
| 3 | 649 | Maurice Utrillo | 253 | 4 |
| 4 | 559 | Albert Marquet | 233 | 5 |
| ... | ... | ... | ... | ... |
| 416 | 597 | Adriaen Thomasz. Key | 2 | 415 |
| 417 | 652 | Paul Kane | 2 | 415 |
| 418 | 707 | Laurent de La Hyre | 2 | 415 |
| 419 | 713 | Isaac Van Ostade | 2 | 415 |
| 420 | 750 | Christian Rohlfs | 2 | 415 |
421 rows × 4 columns
query = """
WITH work_prices AS (
-- Get max sale price for each painting
SELECT
a.artist_id,
a.full_name,
w.work_id,
w.name,
MAX(ps.sale_price) AS max_sale_price
FROM artist AS a
INNER JOIN work AS w
ON a.artist_id = w.artist_id
INNER join product_size AS ps
ON w.work_id = ps.work_id
GROUP BY 1, 2, 3, 4
),
rank_paintings AS (
SELECT
*,
ROW_NUMBER()OVER(
PARTITION BY artist_id
ORDER BY max_sale_price DESC
) AS rnk
FROM work_prices
)
SELECT *
FROM rank_paintings
WHERE rnk <= 3
"""
pd.read_sql(query, engine)
| artist_id | full_name | work_id | name | max_sale_price | rnk | |
|---|---|---|---|---|---|---|
| 0 | 500 | Pierre-Auguste Renoir | 3875 | Dance at Bougival | 1005 | 1 |
| 1 | 500 | Pierre-Auguste Renoir | 3877 | Dance in the City | 1005 | 2 |
| 2 | 500 | Pierre-Auguste Renoir | 3879 | Dance in the Country at Chatou | 1005 | 3 |
| 3 | 501 | Alexandre Cabanel | 23562 | The Governess | 815 | 1 |
| 4 | 501 | Alexandre Cabanel | 23540 | Albayde | 785 | 2 |
| ... | ... | ... | ... | ... | ... | ... |
| 1241 | 919 | Edmund Charles Tarbell | 122442 | Mary at the Harpsichord | 785 | 2 |
| 1242 | 919 | Edmund Charles Tarbell | 122453 | Mother and Child in Boat | 785 | 3 |
| 1243 | 920 | Rembrandt Van Rijn | 3177 | Balaam and His Ass | 875 | 1 |
| 1244 | 920 | Rembrandt Van Rijn | 3341 | The Beheading of John the Baptist | 875 | 2 |
| 1245 | 920 | Rembrandt Van Rijn | 3351 | The Music Party | 865 | 3 |
1246 rows × 6 columns
query = """
WITH avg_database_selling_price AS(
SELECT
ROUND(AVG(sale_price), 2) AS avg_sales_price
FROM product_size
),
style_counts AS(
SELECT
style,
COUNT(work_id) AS nr_of_paintings
FROM work
GROUP BY 1
HAVING COUNT(work_id) >= 500
)
SELECT
w.style,
ROUND(AVG(p.sale_price), 2) AS avg_style_price
FROM work AS w
INNER JOIN product_size AS p
ON w.work_id = p.work_id
CROSS JOIN avg_database_selling_price
WHERE style in (SELECT style FROM style_counts)
GROUP BY 1
HAVING avg_style_price > avg_sales_price
"""
pd.read_sql(query, engine)
| style | avg_style_price | |
|---|---|---|
| 0 | Other | 432.98 |
| 1 | Rococo | 447.78 |
| 2 | Romanticism | 429.03 |
The threshold of 10 was chosen as a reasonable cutoff to exclude clear outliers, rather than derived from the full distribution of paintings per museum.
query = """
WITH weekly_hours AS(
SELECT
museum_id,
SUM(open_hrs) AS weekly_hrs
FROM museum_hrs
GROUP BY 1
),
painting_cnts AS (
SELECT
museum_id,
COUNT(work_id) AS nr_of_paintings
FROM work
WHERE museum_id IS NOT NULL
GROUP BY 1
)
SELECT
m.name as museum,
m.city,
m.country,
wh.weekly_hrs AS weekly_operating_hrs,
pc.nr_of_paintings,
ROUND(wh.weekly_hrs / pc.nr_of_paintings , 2) AS access_to_art_ratio
FROM museum AS m
INNER JOIN weekly_hours AS wh
ON m.museum_id = wh.museum_id
INNER JOIN painting_cnts AS pc
ON m.museum_id = pc.museum_id
WHERE pc.nr_of_paintings >= 10
ORDER BY access_to_art_ratio DESC
LIMIT 1;
"""
pd.read_sql(query, engine)
| museum | city | country | weekly_operating_hrs | nr_of_paintings | access_to_art_ratio | |
|---|---|---|---|---|---|---|
| 0 | Van Gogh Museum | Amsterdam | Netherlands | 63.0 | 17 | 3.71 |
**Note on methodology:** Tiers are defined by splitting the *price range* into three equal dollar-width bands (0 to ⅓ of the max price, ⅓ to ⅔, ⅔ to max). Since painting prices are right-skewed (most paintings cluster at the lower end, with a few high-priced outliers stretching the range upward), this means "Budget" capturesa disproportionately large share of the collection, while "Luxury" captures a disproportionately small one.
query = """
WITH category_threshold AS (
SELECT
MAX(regular_price) / 3 AS price_bandwidth
FROM product_size
),
paintings_categorized AS (
SELECT
w.work_id,
w.name,
p.regular_price,
CASE
WHEN p.regular_price <= price_bandwidth THEN 'Budget'
WHEN p.regular_price <= 2 * price_bandwidth THEN 'Mid-Tier'
ELSE 'Luxury'
END AS price_tier
FROM work AS w
CROSS JOIN category_threshold
INNER JOIN product_size AS p
ON w.work_id = p.work_id
),
price_tier_cnts AS(
SELECT
price_tier,
COUNT(*) AS total_price_tier_count
FROM paintings_categorized
GROUP BY 1
)
SELECT
price_tier,
ROUND(total_price_tier_count * 100.0 /(SELECT COUNT(*) FROM paintings_categorized), 2) AS perc_of_total
FROM price_tier_cnts
ORDER BY
CASE price_tier
WHEN 'Budget' THEN 1
WHEN 'Mid-Tier' THEN 2
WHEN 'Luxury' THEN 3
END;
"""
pd.read_sql(query, engine)
| price_tier | perc_of_total | |
|---|---|---|
| 0 | Budget | 51.51 |
| 1 | Mid-Tier | 37.60 |
| 2 | Luxury | 10.90 |