Beta
Lego Database
👋 Welcome to your workspace! Here, you can run SQL queries, write Python code, and add text in Markdown. This workspace is automatically connected to a PostgreSQL database containing Lego set information (source).
You can click the "Browse tables" button in the upper righthand corner of the cell below to view the available tables.
To demonstrate the power of this combination, there is a query and a visualization of every Star Wars Lego set, rendered in Plotly.
Unknown integration
DataFrameavailable as
star_wars_sets
variable
WITH star_wars_sets AS (
SELECT set_num
FROM themes
INNER JOIN sets
ON themes.id = sets.theme_id
WHERE parent_id = (
SELECT id FROM themes
WHERE name = 'Star Wars'
AND parent_id IS NULL)
),
star_wars_sets_info AS (
SELECT
set_num,
set_name,
year,
num_parts,
color_name,
rgb,
ROW_NUMBER() OVER(PARTITION BY set_num ORDER BY number_per_color DESC) AS color_rank
FROM (
SELECT
sets.set_num AS set_num,
rgb,
colors.name AS color_name,
sets.name AS set_name,
year,
num_parts,
SUM(quantity) AS number_per_color
FROM inventory_parts
INNER JOIN inventories
ON inventory_parts.inventory_id = inventories.id
INNER JOIN sets
ON inventories.set_num = sets.set_num
INNER JOIN colors
ON inventory_parts.color_id = colors.id
WHERE sets.set_num IN (SELECT set_num FROM star_wars_sets)
GROUP BY sets.set_num, rgb, color_name, set_name, year, num_parts) AS sub
)
SELECT * FROM star_wars_sets_info
WHERE color_rank = 1This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
import plotly.express as px
# Create color map
colors = dict(zip(star_wars_sets.set_num, star_wars_sets.rgb.apply(lambda x: "#" + x)))
# Create a strip plot of the data
fig = px.strip(
star_wars_sets,
x="year",
y="num_parts",
color="set_num",
color_discrete_map=colors,
custom_data=["set_name", "year", "num_parts", "color_name"],
).update_traces(dict(marker_line_width=0.5, marker_line_color="black"))
# Customize data to show
fig.update_traces(
hovertemplate="<br>".join(
[
"<b>%{customdata[0]}</b>",
"<b>Year:</b> %{customdata[1]}",
"<b>Number of parts:</b> %{customdata[2]}",
"<b>Most common color in set:</b> %{customdata[3]}<extra></extra>",
]
),
)
# Update the layout and show the figure
fig.update_layout(
title="Star Wars Lego Sets<br><sup>Year of Release, Number of Parts, and Most Common Color</sup>",
title_x=0.5,
xaxis_title="Year of Release",
yaxis_title="Number of Parts",
template="plotly_white",
coloraxis_colorbar_title_text="Total Inventory",
showlegend=False
)
fig.show()This is an interactive plot! Hover over different sets to see the number of parts and most common color!
💪 Now it's your turn to construct your own queries and analyze the data! Remember, you can review the tables in the database at any point using the "Browse tables" button.