Report not found

There was a problem finding this report.
Lego color palette by decade (top ten)
July 4, 2016 · Refreshed almost 2 years ago

Collaborators

Run History
WITH color AS( SELECT * FROM( SELECT *, rank() OVER (PARTITION BY decade ORDER BY count DESC) FROM( SELECT CASE WHEN ls.year BETWEEN '1950' AND '1959' THEN '1950s' WHEN ls.year BETWEEN '1960' AND '1969' THEN '1960s' WHEN ls.year BETWEEN '1970' AND '1979' THEN '1970s' WHEN ls.year BETWEEN '1980' AND '1989' THEN '1980s' WHEN ls.year BETWEEN '1990' AND '1999' THEN '1990s' WHEN ls.year BETWEEN '2000' AND '2009' THEN '2000s' WHEN ls.year BETWEEN '2010' AND '2019' THEN '2010s' END as decade, c.descr as color, '#'||c.hex as hex, SUM(sp.num)as count FROM modeanalytics.lego_set ls JOIN modeanalytics.lego_set_piece sp ON ls.set_id = sp.set_id AND sp.type=1 JOIN modeanalytics.lego_color_hex c ON sp.color = c.id AND c.descr NOT IN ('(No Color)','Unknown') GROUP BY 1,2,3)as a )as b WHERE (rank <=10 OR (decade='1960s' AND color='Metallic Silver')) ), total AS( SELECT CASE WHEN ls.year BETWEEN '1950' AND '1959' THEN '1950s' WHEN ls.year BETWEEN '1960' AND '1969' THEN '1960s' WHEN ls.year BETWEEN '1970' AND '1979' THEN '1970s' WHEN ls.year BETWEEN '1980' AND '1989' THEN '1980s' WHEN ls.year BETWEEN '1990' AND '1999' THEN '1990s' WHEN ls.year BETWEEN '2000' AND '2009' THEN '2000s' WHEN ls.year BETWEEN '2010' AND '2019' THEN '2010s' END as decade, SUM(sp.num)as count FROM modeanalytics.lego_set ls JOIN modeanalytics.lego_set_piece sp ON ls.set_id = sp.set_id AND sp.type=1 JOIN modeanalytics.lego_color_hex c ON sp.color = c.id AND c.descr NOT IN ('(No Color)','Unknown') GROUP BY 1) SELECT color.decade, color.color, color.hex, ROUND(100*(color.count / total.count)::numeric,2)as perc FROM color JOIN total ON color.decade = total.decade ORDER BY 1,4 DESC
WITH color AS( SELECT * FROM( SELECT *, rank() OVER (PARTITION BY decade ORDER BY count DESC) FROM( SELECT CASE WHEN ls.year BETWEEN '1950' AND '1959' THEN '1950s' WHEN ls.year BETWEEN '1960' AND '1969' THEN '1960s' WHEN ls.year BETWEEN '1970' AND '1979' THEN '1970s' WHEN ls.year BETWEEN '1980' AND '1989' THEN '1980s' WHEN ls.year BETWEEN '1990' AND '1999' THEN '1990s' WHEN ls.year BETWEEN '2000' AND '2009' THEN '2000s' WHEN ls.year BETWEEN '2010' AND '2019' THEN '2010s' END as decade, c.descr as color, '#'||c.hex as hex, SUM(sp.num)as count FROM modeanalytics.lego_set ls JOIN modeanalytics.lego_set_piece sp ON ls.set_id = sp.set_id AND sp.type=1 JOIN modeanalytics.lego_color_hex c ON sp.color = c.id AND c.descr NOT IN ('(No Color)','Unknown') GROUP BY 1,2,3)as a )as b WHERE (rank <=10 OR (decade='1960s' AND color='Metallic Silver')) ), total AS( SELECT CASE WHEN ls.year BETWEEN '1950' AND '1959' THEN '1950s' WHEN ls.year BETWEEN '1960' AND '1969' THEN '1960s' WHEN ls.year BETWEEN '1970' AND '1979' THEN '1970s' WHEN ls.year BETWEEN '1980' AND '1989' THEN '1980s' WHEN ls.year BETWEEN '1990' AND '1999' THEN '1990s' WHEN ls.year BETWEEN '2000' AND '2009' THEN '2000s' WHEN ls.year BETWEEN '2010' AND '2019' THEN '2010s' END as decade, SUM(sp.num)as count FROM modeanalytics.lego_set ls JOIN modeanalytics.lego_set_piece sp ON ls.set_id = sp.set_id AND sp.type=1 JOIN modeanalytics.lego_color_hex c ON sp.color = c.id AND c.descr NOT IN ('(No Color)','Unknown') GROUP BY 1) SELECT color.decade, color.color, color.hex, ROUND(100*(color.count / total.count)::numeric,2)as perc FROM color JOIN total ON color.decade = total.decade ORDER BY 1,4 DESC
SELECT DISTINCT color FROM( WITH color AS( SELECT * FROM( SELECT *, rank() OVER (PARTITION BY decade ORDER BY count DESC) FROM( SELECT CASE WHEN ls.year BETWEEN '1950' AND '1959' THEN '1950s' WHEN ls.year BETWEEN '1960' AND '1969' THEN '1960s' WHEN ls.year BETWEEN '1970' AND '1979' THEN '1970s' WHEN ls.year BETWEEN '1980' AND '1989' THEN '1980s' WHEN ls.year BETWEEN '1990' AND '1999' THEN '1990s' WHEN ls.year BETWEEN '2000' AND '2009' THEN '2000s' WHEN ls.year BETWEEN '2010' AND '2019' THEN '2010s' END as decade, c.descr as color, c.hex, SUM(sp.num)as count FROM modeanalytics.lego_set ls JOIN modeanalytics.lego_set_piece sp ON ls.set_id = sp.set_id AND sp.type=1 JOIN modeanalytics.lego_color_hex c ON sp.color = c.id AND c.descr NOT IN ('(No Color)','Unknown') GROUP BY 1,2,3)as a )as b WHERE (rank <=10 OR (decade='1960s' AND color='Metallic Silver')) ), total AS( SELECT CASE WHEN ls.year BETWEEN '1950' AND '1959' THEN '1950s' WHEN ls.year BETWEEN '1960' AND '1969' THEN '1960s' WHEN ls.year BETWEEN '1970' AND '1979' THEN '1970s' WHEN ls.year BETWEEN '1980' AND '1989' THEN '1980s' WHEN ls.year BETWEEN '1990' AND '1999' THEN '1990s' WHEN ls.year BETWEEN '2000' AND '2009' THEN '2000s' WHEN ls.year BETWEEN '2010' AND '2019' THEN '2010s' END as decade, SUM(sp.num)as count FROM modeanalytics.lego_set ls JOIN modeanalytics.lego_set_piece sp ON ls.set_id = sp.set_id AND sp.type=1 JOIN modeanalytics.lego_color_hex c ON sp.color = c.id AND c.descr NOT IN ('(No Color)','Unknown') GROUP BY 1) SELECT color.decade, color.color, color.hex, ROUND(100*(color.count / total.count)::numeric,2)as perc FROM color JOIN total ON color.decade = total.decade)as x
SELECT DISTINCT color FROM( WITH color AS( SELECT * FROM( SELECT *, rank() OVER (PARTITION BY decade ORDER BY count DESC) FROM( SELECT CASE WHEN ls.year BETWEEN '1950' AND '1959' THEN '1950s' WHEN ls.year BETWEEN '1960' AND '1969' THEN '1960s' WHEN ls.year BETWEEN '1970' AND '1979' THEN '1970s' WHEN ls.year BETWEEN '1980' AND '1989' THEN '1980s' WHEN ls.year BETWEEN '1990' AND '1999' THEN '1990s' WHEN ls.year BETWEEN '2000' AND '2009' THEN '2000s' WHEN ls.year BETWEEN '2010' AND '2019' THEN '2010s' END as decade, c.descr as color, c.hex, SUM(sp.num)as count FROM modeanalytics.lego_set ls JOIN modeanalytics.lego_set_piece sp ON ls.set_id = sp.set_id AND sp.type=1 JOIN modeanalytics.lego_color_hex c ON sp.color = c.id AND c.descr NOT IN ('(No Color)','Unknown') GROUP BY 1,2,3)as a )as b WHERE (rank <=10 OR (decade='1960s' AND color='Metallic Silver')) ), total AS( SELECT CASE WHEN ls.year BETWEEN '1950' AND '1959' THEN '1950s' WHEN ls.year BETWEEN '1960' AND '1969' THEN '1960s' WHEN ls.year BETWEEN '1970' AND '1979' THEN '1970s' WHEN ls.year BETWEEN '1980' AND '1989' THEN '1980s' WHEN ls.year BETWEEN '1990' AND '1999' THEN '1990s' WHEN ls.year BETWEEN '2000' AND '2009' THEN '2000s' WHEN ls.year BETWEEN '2010' AND '2019' THEN '2010s' END as decade, SUM(sp.num)as count FROM modeanalytics.lego_set ls JOIN modeanalytics.lego_set_piece sp ON ls.set_id = sp.set_id AND sp.type=1 JOIN modeanalytics.lego_color_hex c ON sp.color = c.id AND c.descr NOT IN ('(No Color)','Unknown') GROUP BY 1) SELECT color.decade, color.color, color.hex, ROUND(100*(color.count / total.count)::numeric,2)as perc FROM color JOIN total ON color.decade = total.decade)as x
SELECT *, rank() OVER (PARTITION BY decade ORDER BY count DESC) FROM( SELECT CASE WHEN ls.year BETWEEN '1950' AND '1959' THEN '1950s' WHEN ls.year BETWEEN '1960' AND '1969' THEN '1960s' WHEN ls.year BETWEEN '1970' AND '1979' THEN '1970s' WHEN ls.year BETWEEN '1980' AND '1989' THEN '1980s' WHEN ls.year BETWEEN '1990' AND '1999' THEN '1990s' WHEN ls.year BETWEEN '2000' AND '2009' THEN '2000s' WHEN ls.year BETWEEN '2010' AND '2019' THEN '2010s' END as decade, c.descr as color, c.hex, SUM(sp.num)as count FROM modeanalytics.lego_set ls JOIN modeanalytics.lego_set_piece sp ON ls.set_id = sp.set_id AND sp.type=1 JOIN modeanalytics.lego_color_hex c ON sp.color = c.id AND c.descr NOT IN ('(No Color)','Unknown') GROUP BY 1,2,3)as a
SELECT *, rank() OVER (PARTITION BY decade ORDER BY count DESC) FROM( SELECT CASE WHEN ls.year BETWEEN '1950' AND '1959' THEN '1950s' WHEN ls.year BETWEEN '1960' AND '1969' THEN '1960s' WHEN ls.year BETWEEN '1970' AND '1979' THEN '1970s' WHEN ls.year BETWEEN '1980' AND '1989' THEN '1980s' WHEN ls.year BETWEEN '1990' AND '1999' THEN '1990s' WHEN ls.year BETWEEN '2000' AND '2009' THEN '2000s' WHEN ls.year BETWEEN '2010' AND '2019' THEN '2010s' END as decade, c.descr as color, c.hex, SUM(sp.num)as count FROM modeanalytics.lego_set ls JOIN modeanalytics.lego_set_piece sp ON ls.set_id = sp.set_id AND sp.type=1 JOIN modeanalytics.lego_color_hex c ON sp.color = c.id AND c.descr NOT IN ('(No Color)','Unknown') GROUP BY 1,2,3)as a
SELECT distinct(color),string_agg(decade, ', ') FROM( SELECT *, rank() OVER (PARTITION BY decade ORDER BY count DESC) FROM( SELECT CASE WHEN ls.year BETWEEN '1950' AND '1959' THEN '1950s' WHEN ls.year BETWEEN '1960' AND '1969' THEN '1960s' WHEN ls.year BETWEEN '1970' AND '1979' THEN '1970s' WHEN ls.year BETWEEN '1980' AND '1989' THEN '1980s' WHEN ls.year BETWEEN '1990' AND '1999' THEN '1990s' WHEN ls.year BETWEEN '2000' AND '2009' THEN '2000s' WHEN ls.year BETWEEN '2010' AND '2019' THEN '2010s' END as decade, c.descr as color, c.hex, SUM(sp.num)as count FROM modeanalytics.lego_set ls JOIN modeanalytics.lego_set_piece sp ON ls.set_id = sp.set_id AND sp.type=1 JOIN modeanalytics.lego_color_hex c ON sp.color = c.id AND c.descr NOT IN ('(No Color)','Unknown') GROUP BY 1,2,3)as a)as b GROUP BY 1 ORDER BY 2
SELECT distinct(color),string_agg(decade, ', ') FROM( SELECT *, rank() OVER (PARTITION BY decade ORDER BY count DESC) FROM( SELECT CASE WHEN ls.year BETWEEN '1950' AND '1959' THEN '1950s' WHEN ls.year BETWEEN '1960' AND '1969' THEN '1960s' WHEN ls.year BETWEEN '1970' AND '1979' THEN '1970s' WHEN ls.year BETWEEN '1980' AND '1989' THEN '1980s' WHEN ls.year BETWEEN '1990' AND '1999' THEN '1990s' WHEN ls.year BETWEEN '2000' AND '2009' THEN '2000s' WHEN ls.year BETWEEN '2010' AND '2019' THEN '2010s' END as decade, c.descr as color, c.hex, SUM(sp.num)as count FROM modeanalytics.lego_set ls JOIN modeanalytics.lego_set_piece sp ON ls.set_id = sp.set_id AND sp.type=1 JOIN modeanalytics.lego_color_hex c ON sp.color = c.id AND c.descr NOT IN ('(No Color)','Unknown') GROUP BY 1,2,3)as a)as b GROUP BY 1 ORDER BY 2
<style> body { background: #cfd3d3 !important; } .chart { background-color: #cfd3d3; border: none; } .mode-embed .mode-object { border: none; background: #cfd3d3; } .js-table-container { background-color: #cfd3d3; } </style> <div class="mode-header embed-hidden"> <h1>{{ title }}</h1> <p>{{ description }}</p> </div> <div class="mode-grid container"> <div class="row"> <div class="col-md-12"> <mode-python id="python_7c1fa456b868" options="python_options"></mode-python> </div> </div> </div> <script> $("#python_7c1fa456b868 .chart-content").css("height",650) $("#python_7c1fa456b868 .mode-python").css("height",650) $("#python_7c1fa456b868 .mode-python").css("max-height",650) $("#python_7c1fa456b868 img").css("max-height",650) </script>
{{ dataSourceName(params.queryId) }}

The dataset is too large to view in browser

Export

Looks like something went wrong with your query.

{{ DS.queryRuns[params.queryId].errorMessage }}
This query was cancelled