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:

In [1]:
import pandas as pd
from sqlalchemy import create_engine

# Create a SQLAlchemy engine directly
engine = create_engine('sqlite:///paintings.db')

1. Data Preparation

1.1 Subject data

1.1.1 Load data¶

In [2]:
df_subject = pd.read_csv('/content/subject.csv')
df_subject.head()
Out[2]:
work_id subject
0 160228 Still-Life
1 160236 Still-Life
2 160244 Still-Life
3 160252 Still-Life
4 160260 Still-Life
In [3]:
# The dimensionality of the DataFrame
df_subject.shape
Out[3]:
(6771, 2)
In [4]:
# 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

1.1.2 Data Cleaning

1.1.2.1 Check for duplicates¶

In [5]:
# Find the total number of duplicate rows in the DataFrame
df_subject.duplicated().sum()
Out[5]:
np.int64(59)
In [6]:
# Remove duplicates
df_subject = df_subject.drop_duplicates()
df_subject.shape
Out[6]:
(6712, 2)

1.1.2.2 Missing Values¶

In [7]:
df_subject.isnull().sum()
Out[7]:
0
work_id 0
subject 0

1.1.2.3 Data Quality Checks¶

In [8]:
df_subject['subject'].unique()
Out[8]:
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)
In [9]:
df_subject['subject'] = df_subject['subject'].replace({
    'Musics' : 'Music',
    'Deers' : 'Deer',
    'Architectures' : 'Architecture'
})
df_subject['subject'].unique()
Out[9]:
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)

1.2 Museum Hours

1.2.1 Load data¶

In [10]:
df_museum_hrs = pd.read_csv('/content/museum_hours.csv')
df_museum_hrs.head()
Out[10]:
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
In [11]:
# The dimensionality of the DataFrame
df_museum_hrs.shape
Out[11]:
(351, 4)
In [12]:
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

1.2.2 Data Cleaning

1.2.2.1 Check for duplicates¶

In [13]:
df_museum_hrs.duplicated().sum()
Out[13]:
np.int64(1)
In [14]:
df_museum_hrs = df_museum_hrs.drop_duplicates()
df_museum_hrs.shape
Out[14]:
(350, 4)

1.2.2.2 Missing Values¶

In [15]:
df_museum_hrs.isnull().sum()
Out[15]:
0
museum_id 0
day 0
open 0
close 0

1.2.2.3 Data Quality Checks¶

In [16]:
df_museum_hrs['day'].unique()
Out[16]:
array(['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thusday', 'Friday',
       'Saturday', 'Thursday'], dtype=object)
In [17]:
df_museum_hrs['day'] = df_museum_hrs['day'].str.replace('Thusday','Thursday', regex=False)
df_museum_hrs['day'].unique()
Out[17]:
array(['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday',
       'Saturday'], dtype=object)
In [18]:
df_museum_hrs['open'].unique()
Out[18]:
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)
In [19]:
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()
Out[19]:
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)
In [20]:
df_museum_hrs['close'].unique()
Out[20]:
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)
In [21]:
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()
Out[21]:
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)
In [22]:
df_museum_hrs
Out[22]:
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

1.2.2.3 Feature Engineering¶

In [23]:
df_museum_hrs.head()
Out[23]:
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
In [24]:
# 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()
Out[24]:
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

1.3 Artist

1.3.1 Load data¶

In [25]:
df_artist = pd.read_csv('/content/artist.csv')
df_artist.head()
Out[25]:
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
In [26]:
df_artist.shape
Out[26]:
(421, 9)
In [27]:
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

1.3.2 Data Cleaning

1.3.2.1 Check for duplicates¶

In [28]:
df_artist.duplicated().sum()
Out[28]:
np.int64(0)

1.3.2.2 Missing Values¶

In [29]:
df_artist.isnull().sum()
Out[29]:
0
artist_id 0
full_name 0
first_name 0
middle_names 273
last_name 0
nationality 0
style 0
birth 0
death 0

In [30]:
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]
Out[30]:
columns nr_of_missingValues perc_of_missingValues
3 middle_names 273 64.85
In [31]:
# Remove 'middle_names' column
df_artist.drop('middle_names', axis=1 , inplace=True)
df_artist.columns
Out[31]:
Index(['artist_id', 'full_name', 'first_name', 'last_name', 'nationality',
       'style', 'birth', 'death'],
      dtype='object')
In [32]:
# Distinct art styles
df_artist['style'].unique()
Out[32]:
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)

1.4 Museum

1.4.1 Load data¶

In [33]:
df_museum = pd.read_csv('/content/museum.csv')
df_museum.head()
Out[33]:
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...
In [34]:
df_museum.shape
Out[34]:
(57, 9)
In [35]:
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

1.4.2 Data Cleaning

1.4.2.1 Check for duplicates¶

In [36]:
df_museum.duplicated().sum()
Out[36]:
np.int64(0)

1.4.2.2 Missing Values¶

In [37]:
df_museum.isnull().sum()
Out[37]:
0
museum_id 0
name 0
address 0
city 0
state 19
postal 7
country 0
phone 0
url 0

In [38]:
# Remove columns with missing values
df_museum.drop(['state', 'postal'], axis=1 , inplace=True)
df_museum.columns
Out[38]:
Index(['museum_id', 'name', 'address', 'city', 'country', 'phone', 'url'], dtype='object')

1.4.2.3 Data Quality Checks¶

In [39]:
df_museum['country'].unique()
Out[39]:
array(['USA', 'Russia', 'Australia', 'Brazil', 'Germany', 'France',
       'United Kingdom', 'Netherlands', 'Israel', 'Switzerland', 'UK',
       'Spain', 'Hungary', 'Italy', 'Japan', 'Norway', 'Czechia'],
      dtype=object)
In [40]:
df_museum['country'] = df_museum['country'].replace({
    'United Kingdom' : 'UK' ,
    'UK' : 'UK'
})
df_museum['country'].unique()
Out[40]:
array(['USA', 'Russia', 'Australia', 'Brazil', 'Germany', 'France', 'UK',
       'Netherlands', 'Israel', 'Switzerland', 'Spain', 'Hungary',
       'Italy', 'Japan', 'Norway', 'Czechia'], dtype=object)

1.5 Product Size

1.5.1 Load data¶

In [41]:
df_productSize = pd.read_csv('/content/product_size.csv')
df_productSize.head()
Out[41]:
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
In [42]:
df_productSize.shape
Out[42]:
(110347, 4)
In [43]:
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

1.5.2 Data Cleaning

1.5.2.1 Update datatype for size_id¶

In [44]:
df_productSize['size_id'].unique()
Out[44]:
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)
In [45]:
# 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()
Out[45]:
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])
In [46]:
# Remove all rows where 'size_id' has a NaN value
df_productSize = df_productSize.dropna(subset = ['size_id'])
In [47]:
# 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)]
In [48]:
# Convert column to an integer data type
df_productSize['size_id'] = df_productSize['size_id'].astype(int)
In [49]:
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
In [50]:
# Display the unique size_ids
df_productSize['size_id'].unique()
Out[50]:
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])

1.5.2.2 Check for duplicates¶

In [51]:
df_productSize.duplicated().sum()
Out[51]:
np.int64(687)
In [52]:
df_productSize = df_productSize.drop_duplicates()
df_productSize.shape
Out[52]:
(109332, 4)

1.5.2.3 Missing Values¶

In [53]:
df_productSize.isnull().sum()
Out[53]:
0
work_id 0
size_id 0
sale_price 0
regular_price 0

1.6 Image Link

1.6.1 Load data¶

In [54]:
df_imageLink = pd.read_csv('/content/image_link.csv')
df_imageLink.head()
Out[54]:
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...
In [55]:
df_imageLink.shape
Out[55]:
(14775, 4)
In [56]:
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

1.6.2 Data Cleaning

1.6.2.1 Check for duplicates¶

In [57]:
df_imageLink.duplicated().sum()
Out[57]:
np.int64(60)
In [58]:
df_imageLink = df_imageLink.drop_duplicates()
df_imageLink.shape
Out[58]:
(14715, 4)

1.6.2.2 Missing Values¶

In [59]:
df_imageLink.isnull().sum()
Out[59]:
0
work_id 0
url 0
thumbnail_small_url 2
thumbnail_large_url 2

In [60]:
# Remove all rows in specified columns where atleast 1 value is missing
df_imageLink = df_imageLink.dropna(subset=['thumbnail_small_url', 'thumbnail_large_url'])

1.7 Work

1.7.1 Load data¶

In [61]:
df_work = pd.read_csv('/content/work.csv')
df_work.head()
Out[61]:
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
In [62]:
df_work.shape
Out[62]:
(14776, 5)
In [63]:
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

1.7.2 Data Cleaning

1.7.2.1 Check for duplicates¶

In [64]:
df_work.duplicated().sum()
Out[64]:
np.int64(60)
In [65]:
df_work = df_work.drop_duplicates()
df_work.shape
Out[65]:
(14716, 5)

1.7.2.2 Missing Values¶

In [66]:
df_work.isnull().sum()
Out[66]:
0
work_id 0
name 0
artist_id 0
style 1228
museum_id 10163

In [67]:
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]
Out[67]:
columns nr_of_missingValues perc_of_missingValues
3 style 1228 8.34
4 museum_id 10163 69.06
In [68]:
# Fill missing values on style column with 'Other'
df_work['style'] = df_work['style'].fillna(value='Other')
df_work['style'].unique()
Out[68]:
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)

1.8 Canvas Size

1.8.1 Load data¶

In [69]:
df_canvasSize = pd.read_csv('/content/canvas_size.csv')
df_canvasSize.head()
Out[69]:
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
In [70]:
df_canvasSize.shape
Out[70]:
(200, 4)
In [71]:
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

1.8.1.1 Check for duplicates¶

In [72]:
df_canvasSize.duplicated().sum()
Out[72]:
np.int64(0)

1.8.1.2 Missing Values¶

In [73]:
df_canvasSize.isnull().sum()
Out[73]:
0
size_id 0
width 0
height 7
label 0

2. SQL Data Analysis

image.png

2.1 Write data to SQL database

In [74]:
# Initialize a new engine that can interface with the SQLite database
engine = create_engine('sqlite:///paintings.db')
In [75]:
# Connect to the database
conn = engine.connect()
In [76]:
# 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)
Out[76]:
200

2.2 Phase 1: Data Exploration & Basic Filtering


List all tables in the database:

In [77]:
query = """
SELECT
    name
FROM sqlite_master
WHERE type='table';
"""
pd.read_sql(query, engine)
Out[77]:
name
0 subject
1 museum_hrs
2 artist
3 museum
4 product_size
5 image_link
6 work
7 canvas_size
In [78]:
query = """
SELECT
  COUNT (DISTINCT work_id)
FROM product_size


"""
pd.read_sql(query, engine)
Out[78]:
COUNT (DISTINCT work_id)
0 14630

2.2.1 How many art pieces have not been assigned to a museum yet?

In [79]:
query = """
SELECT
  COUNT(work_id)
FROM work
WHERE museum_id IS NULL
"""
pd.read_sql(query,engine)
Out[79]:
COUNT(work_id)
0 10163
In [80]:
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)
Out[80]:
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

2.2.2 Top 10 most famous painting subjects

In [81]:
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)
Out[81]:
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

2.2.3 Geographic Spread: Which country hosts the most paintings?

In [82]:
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)
Out[82]:
country nr_of_paintings
0 USA 2672

2.2.4 How many paintings are missing both a small and large thumbnail image?

In [83]:
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)
Out[83]:
nr_of_paintings
0 0

2.3 Phase 2: Intermediate Joins & Conditional Logic


2.3.1 Schedule Analysis: Identify museums open on both Sunday and Monday.

In [84]:
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)
Out[84]:
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

2.3.2 Operational Extremes: Which museums are open every single day and which city and country are they in?

In [85]:
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)
Out[85]:
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

2.3.3 Cross-Border Art: List paintings by French artists displayed outside of France.

In [86]:
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)
Out[86]:
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

2.3.4 Which canvas size is used most often across all paintings, and what's its typical average price?

In [87]:
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)
Out[87]:
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

2.4 Phase 3: Advanced SQL


2.4.1 Pricing Anomalies: Find all paintings where the asking price is less than 50% of their regular price, and show the title, artist, and both prices.

In [88]:
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)
Out[88]:
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

2.4.2 Catalog Ranking: Rank artists by their total number of paintings.

In [89]:
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)
Out[89]:
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

2.4.3 Top Works per Artist: Find the 3 most expensive paintings per artist.

In [100]:
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)
Out[100]:
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

2.4.4 Advanced Aggregation: Find art styles where the average price is greater than the database average.

In [91]:
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)
Out[91]:
style avg_style_price
0 Other 432.98
1 Rococo 447.78
2 Romanticism 429.03

2.4.5 Museum Operational Density: Which museum offers the best "access-to-art ratio"—defined as total operating hours per week divided by the number of paintings on display?

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.

In [92]:
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)
Out[92]:
museum city country weekly_operating_hrs nr_of_paintings access_to_art_ratio
0 Van Gogh Museum Amsterdam Netherlands 63.0 17 3.71

2.4.6 Categorize all paintings into price tiers (Budget, Mid-Tier, Luxury) based on their regular price, and find the percentage of the total collection that falls into each tier.

**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.

In [93]:
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)
Out[93]:
price_tier perc_of_total
0 Budget 51.51
1 Mid-Tier 37.60
2 Luxury 10.90