DS - Analysis of Lego Competition
  • AI Chat
  • Code
  • Report
    • Beta
    Spinner

    💪 Challenge

    Create a report to summarize your findings. Include:

    1. What is the average number of Lego sets released per year?
    2. What is the average number of Lego parts per year?
    3. Create a visualization for item 2.
    4. What are the 5 most popular colors used in Lego parts?
    5. [Optional] What proportion of Lego parts are transparent?
    6. [Optional] What are the 5 rarest lego bricks?
    7. Summarize your findings.
    Unknown integration
    DataFrameavailable as
    df
    variable
    SELECT year, 
       AVG(sets_per_year)
FROM (
    SELECT year, 
    COUNT(set_num) AS sets_per_year
    FROM sets
    GROUP BY year) AS sub
GROUP BY year
ORDER BY year ASC
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
    Unknown integration
    DataFrameavailable as
    df
    variable
    SELECT year,
        ROUND(AVG(num_parts), 2) AS avg_parts_per_year
FROM sets
GROUP BY year
ORDER BY year ASC;
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
    Unknown integration
    DataFrameavailable as
    df
    variable
    SELECT year,
        ROUND(AVG(num_parts), 2) AS avg_parts_per_year
FROM sets
GROUP BY year
ORDER BY year ASC;
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
    Unknown integration
    DataFrameavailable as
    df
    variable
    SELECT  c.name AS color_name,
        COUNT(c.name) AS color_name_count
FROM inventory_parts AS i
INNER JOIN colors AS c
ON (i.color_id = c.id)
GROUP BY i.color_id, c.name
ORDER BY (color_name_count) DESC
LIMIT 5;
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
    Unknown integration
    DataFrameavailable as
    df
    variable
    SELECT name LIKE 'Trans%' AS color_type, COUNT(*), 
        ROUND((COUNT(*) * 100.0 / (SELECT COUNT(*) FROM colors)), 2) as proportion_percentage 
FROM colors 
GROUP BY color_type;
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
    Unknown integration
    DataFrameavailable as
    df
    variable
    SELECT p.name, i.quantity
FROM parts AS p
INNER JOIN inventory_parts AS i
USING (part_num)
GROUP BY p.name, i.quantity
ORDER BY i.quantity ASC
LIMIT 5;
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

    We can see from the analysis that the average number of Lego sets released each year typically follows an upward trend, however, it wasn't until 1999 where this really grew from prior years. It continues this larger growth curve throughout the rest of the data, with the exception of two years: 2006 & 2017. Further analysis would be required to draw a conclusion as to why this occured.

    Similar to sets, parts also showed a growth when looking at the average released each year. Unlike sets, this is a little more sporadic in each year.

    The five most popular colours are: Black White Light Bluish Gray red Dark Bluish Gray

    It is no surprise to not see transparent parts in the list as they only make up 16.3% of the overall colours.

    Finally the rarest Lego bricks were: ½ CIRCLE W/SLIT 8X5X3 10240stk01 10240stk02 1/4 CIRCLE TILE 1X1 1/4 CIRCLE TILE 1X1 with Lattice Pie Print