Report not found

There was a problem finding this report.
Lego set theme connections
August 17, 2016 · Refreshed 8 months ago

Collaborators

Run History
WITH pieces AS ( SELECT t1 AS node, piece_id AS piece, SUM(num) AS number FROM modeanalytics.lego_set ls JOIN modeanalytics.lego_set_piece sp ON ls.set_id = sp.set_id AND sp.type=1 WHERE ls.pieces>3 GROUP BY 1,2 ), piece_counts AS ( SELECT t1 as node, SUM(num) AS total FROM modeanalytics.lego_set ls JOIN modeanalytics.lego_set_piece sp ON ls.set_id = sp.set_id AND sp.type=1 WHERE ls.pieces>3 GROUP BY 1 ), color AS ( SELECT type as node, hex FROM ( SELECT ls.t1 as type, lch.descr as color, lch.hex as hex, SUM(sp.num)as count, rank() OVER (PARTITION BY ls.t1 ORDER BY SUM(sp.num) DESC) 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 lch ON sp.color = lch.id WHERE ls.pieces>3 GROUP BY 1,2,3)as a WHERE rank=1) SELECT *, RANK() OVER (PARTITION BY table_1 ORDER BY connections DESC) AS rel_rank_1, RANK() OVER (PARTITION BY table_2 ORDER BY connections DESC) AS rel_rank_2 FROM ( SELECT 1 as database, 2 as organization, p1.node AS table_1, c.hex AS table_1_color, p2.node AS table_2, pc1.total as total_queries, 100*((SUM(p1.number + p2.number)) / (SUM(pc1.total + pc2.total))) AS connections FROM pieces p1 JOIN pieces p2 ON p2.piece = p1.piece AND p2.node != p1.node -- AND p2.node > p1.node JOIN piece_counts pc1 ON pc1.node = p1.node JOIN piece_counts pc2 ON pc2.node = p2.node JOIN color c ON c.node = p1.node GROUP BY 1,2,3,4,5,6 ) a ORDER BY 7 DESC
WITH pieces AS ( SELECT t1 AS node, piece_id AS piece, SUM(num) AS number FROM modeanalytics.lego_set ls JOIN modeanalytics.lego_set_piece sp ON ls.set_id = sp.set_id AND sp.type=1 WHERE ls.pieces>3 GROUP BY 1,2 ), piece_counts AS ( SELECT t1 as node, SUM(num) AS total FROM modeanalytics.lego_set ls JOIN modeanalytics.lego_set_piece sp ON ls.set_id = sp.set_id AND sp.type=1 WHERE ls.pieces>3 GROUP BY 1 ), color AS ( SELECT type as node, hex FROM ( SELECT ls.t1 as type, lch.descr as color, lch.hex as hex, SUM(sp.num)as count, rank() OVER (PARTITION BY ls.t1 ORDER BY SUM(sp.num) DESC) 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 lch ON sp.color = lch.id WHERE ls.pieces>3 GROUP BY 1,2,3)as a WHERE rank=1) SELECT *, RANK() OVER (PARTITION BY table_1 ORDER BY connections DESC) AS rel_rank_1, RANK() OVER (PARTITION BY table_2 ORDER BY connections DESC) AS rel_rank_2 FROM ( SELECT 1 as database, 2 as organization, p1.node AS table_1, c.hex AS table_1_color, p2.node AS table_2, pc1.total as total_queries, 100*((SUM(p1.number + p2.number)) / (SUM(pc1.total + pc2.total))) AS connections FROM pieces p1 JOIN pieces p2 ON p2.piece = p1.piece AND p2.node != p1.node -- AND p2.node > p1.node JOIN piece_counts pc1 ON pc1.node = p1.node JOIN piece_counts pc2 ON pc2.node = p2.node JOIN color c ON c.node = p1.node GROUP BY 1,2,3,4,5,6 ) a ORDER BY 7 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; } #title { font-weight: 300 !important; font-size: 16px !important; text-align: center; } .title-block { font: Helvetica !important; width: 600px; margin: 0 auto; color: #394242; } .viz { text-align: center; } .node { stroke: black; stroke-width: 1px; } .link { fill: none; stroke: #a4a5a9; } .legend { opacity: .5; font-size: 10px; } .d3-tip { line-height: 1; font-weight: normal; padding: 12px; background: rgba(0, 0, 0, 0.8); color: #fff; border-radius: 2px; } </style> <div class="title-block"> <h1 id="title"></h1> </div> <div class="viz"></div> <script src="https://cdnjs.cloudflare.com/ajax/libs/underscore.js/1.8.3/underscore-min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/d3-tip/0.6.7/d3-tip.min.js"></script> <script> var visibleConnections = 3, queryThreshold = 0; var width = 638, height = 600; function rt(n) { return Math.sqrt(n); }; var tip = d3.tip() .attr('class', 'd3-tip') .offset([-10, 0]) .html(function(d) { return d.name; }) var gradient = ["#C96853", "#f46d43", "#fdae61", "#fee090", "#abd9e9", "#74add1", "#4575b4"].reverse(); var force = d3.layout.force() .linkDistance(40) .linkStrength(1) .size([width, height]); var svg = d3.select(".viz").append("svg") .attr("width", width) .attr("height", height); svg.call(tip); var tables = _.uniq(_.pluck(dataset.content,"table_1")); var nodes = [], links = []; tables.forEach(function(t,i) { var match = _.where(dataset.content, {table_1: t}), total_queries = match[0].total_queries, hex = match[0].table_1_color; nodes.push ( {"name": t, "group": 1, "id": i, "total_queries": total_queries, "hex": hex}) }) $("#title").text(function() { var r = dataset.content[0]; return "Lego set theme connections "; }) dataset.content.forEach(function(c) { if ( (c.rel_rank_1 <= visibleConnections || c.rel_rank_2 <= visibleConnections) && c.table_1 > c.table_2 && c.total_queries > queryThreshold ) { var source = _.where(nodes, {name: c.table_1})[0], target = _.where(nodes, {name: c.table_2})[0]; links.push ( {"source": source.id, "target": target.id, "source_name": c.table_1, "target_name": c.table_2, "value": c.connections } ); } }) var graph = {"nodes": nodes, "links": links}; var radiusScale = d3.scale.linear() .domain( d3.extent(nodes, function(d) { return rt(d.total_queries); }) ) .range([2,20]); var nodeScale = d3.scale.quantize() .domain( d3.extent(nodes, function(d) { return rt(d.total_queries); }) ) .range(gradient); var linkScale = d3.scale.quantize() .domain( d3.extent(links, function(d) { return rt(d.value); }) ) .range(gradient); var widthScale = d3.scale.linear() .domain( d3.extent(links, function(d) { return rt(d.value); }) ) .range([0,4]); var opacityScale = d3.scale.linear() .domain( d3.extent(links, function(d) { return rt(d.value); }) ) .range([.05,.8]); var nodes = graph.nodes.slice(), links = [], bilinks = []; graph.links.forEach(function(link) { var s = nodes[link.source], t = nodes[link.target], i = {}; // intermediate node i["connections"] = link["value"] nodes.push(i); links.push({source: s, target: i}, {source: i, target: t}); bilinks.push([s, i, t]); }); force .nodes(nodes) .links(links) .start(); var link = svg.selectAll(".link") .data(bilinks) .enter().append("path") .attr("class", "link") .style("stroke-width", function(d) { return widthScale(rt(d[1]["connections"])); }) // .style("stroke", function(d) { return linkScale(rt(d[1]["connections"])); }) .style("opacity", function(d) { return opacityScale(rt(d[1]["connections"])); }); var node = svg.selectAll(".node") .data(graph.nodes) .enter().append("g") .attr("class", "node") .call(force.drag); node.append("circle") .attr("r", function(d) { return radiusScale(rt(d.total_queries)); }) // .style("fill", function(d) { return nodeScale(rt(d.total_queries)); }) .style("fill", function(d) { return "#" + d.hex;; }) .on('mouseover', tip.show) .on('mouseout', tip.hide) /* node.append("text") .attr("class","legend") .attr("dx", 12) .attr("dy", ".35em") .text(function(d) { return d.name })*/; force.on("tick", function() { link.attr("d", function(d) { return "M" + d[0].x + "," + d[0].y + "S" + d[1].x + "," + d[1].y + " " + d[2].x + "," + d[2].y; }); node.attr("transform", function(d) { return "translate(" + d.x + "," + d.y + ")"; }); });</script> <div class="mode-grid container"></div>
{{ 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