Report not found

There was a problem finding this report.
Lego color palette by decade (remainder)
July 19, 2016 · Refreshed over 1 year 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 ), 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,4)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 ), 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,4)as perc FROM color JOIN total ON color.decade = total.decade ORDER BY 1,4 DESC
<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_a7ec902cb05f" options="python_options"></mode-python> </div> </div> </div> <script> $("#python_a7ec902cb05f .chart-content").css("height",650) $("#python_a7ec902cb05f .mode-python").css("height",650) $("#python_a7ec902cb05f .mode-python").css("max-height",650) $("#python_a7ec902cb05f 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