Skip to content
Lego Database
  • AI Chat
  • Code
  • Report
    • Spinner

    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 = 1
    This 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.