Skip to content
0

Understanding Lego sets popularity

💾 The data

(source):
Spinner
DataFrameas
df
variable
[1]
SELECT *
FROM sets

Average number of Lego sets released per year

Spinner
DataFrameas
df
variable
[51]
SELECT year,COUNT(set_num) as Lego_sets_Per_Year
FROM sets
GROUP BY year
ORDER BY year;

Average number of Lego parts per year

Spinner
DataFrameas
df
variable
SELECT year,ROUND(AVG(num_parts),2) as Average_Lego_Parts
FROM sets
GROUP BY year
ORDER BY year;

Data Visualization for Average number of Lego parts per year

Spinner
DataFrameas
df
variable
[13]
SELECT year,ROUND(AVG(num_parts),2) as Average_Lego_Parts
FROM sets
GROUP BY year
ORDER BY year;
Spinner
DataFrameas
df
variable
[15]
SELECT year,ROUND(AVG(num_parts),2) as Average_Lego_Parts
FROM sets
GROUP BY year
ORDER BY Average_Lego_Parts DESC
LIMIT 10;

Five most Popular Colours used in Lego Parts

Spinner
DataFrameas
df
variable
[21]
SELECT name,COUNT(color_id) as Total
FROM inventory_parts as ip 
LEFT JOIN colors as c
on ip.color_id=c.id
GROUP BY name
ORDER BY Total DESC
LIMIT 5;

The proportion of Lego parts that are transparent

Spinner
DataFrameas
df
variable
[37]
WITH Total_Parts as
(SELECT COUNT(part_num) as Total
 FROM inventory_parts)
SELECT is_trans,ROUND(COUNT(part_num) * 100.0/(select Total FROM Total_Parts),2) as Proportion_of_Lego_Parts
FROM inventory_parts as ip 
LEFT JOIN colors as c
on ip.color_id=c.id
GROUP BY is_trans;

The 5 rarest lego bricks

‌
‌
‌