Report not found

There was a problem finding this report.
Character connections
November 4, 2016 · Refreshed about 2 years ago

Collaborators

Run History
SELECT 1 AS database, 2 AS organization, c1.name AS table_1, 'b40000' AS table_1_color, c2.name AS table_2, COALESCE(1+(b.connections/10::float8),0) AS connections, COALESCE(c1.image,'_') AS image, COALESCE(1000*SUM(word_count),10) AS total_queries FROM modeanalytics.westworld_characters c1 JOIN modeanalytics.westworld_characters c2 ON c1.image is NOT NULL AND c2.image is NOT NULL LEFT JOIN modeanalytics.westworld_lines l ON c1.id = l.character_id LEFT JOIN( SELECT * FROM( WITH convos AS( SELECT DISTINCT l.conversation_id, c.id as character_id, c.name, c.image FROM modeanalytics.westworld_characters c JOIN modeanalytics.westworld_lines l ON c.id = l.character_id WHERE c.image is NOT NULL ) SELECT c.name AS table_1, c2.name AS table_2, c.image, COUNT(DISTINCT c.conversation_id) AS connections FROM convos c JOIN convos c2 ON c.conversation_id = c2.conversation_id AND c.character_id != c2.character_id GROUP BY 1,2,3 ) a ) b ON c1.name = b.table_1 AND c2.name = b.table_2 GROUP BY 1,2,3,4,5,6,7
SELECT 1 AS database, 2 AS organization, c1.name AS table_1, 'b40000' AS table_1_color, c2.name AS table_2, COALESCE(1+(b.connections/10::float8),0) AS connections, COALESCE(c1.image,'_') AS image, COALESCE(1000*SUM(word_count),10) AS total_queries FROM modeanalytics.westworld_characters c1 JOIN modeanalytics.westworld_characters c2 ON c1.image is NOT NULL AND c2.image is NOT NULL LEFT JOIN modeanalytics.westworld_lines l ON c1.id = l.character_id LEFT JOIN( SELECT * FROM( WITH convos AS( SELECT DISTINCT l.conversation_id, c.id as character_id, c.name, c.image FROM modeanalytics.westworld_characters c JOIN modeanalytics.westworld_lines l ON c.id = l.character_id WHERE c.image is NOT NULL ) SELECT c.name AS table_1, c2.name AS table_2, c.image, COUNT(DISTINCT c.conversation_id) AS connections FROM convos c JOIN convos c2 ON c.conversation_id = c2.conversation_id AND c.character_id != c2.character_id GROUP BY 1,2,3 ) a ) b ON c1.name = b.table_1 AND c2.name = b.table_2 GROUP BY 1,2,3,4,5,6,7
<style> body { background: #3f4d67 !important; } .chart { background-color: #3f4d67; border: none; } .mode-embed .mode-object { border: none; background: #3f4d67; } .js-table-container { background-color: #3f4d67; } #title { font-weight: 300 !important; font-size: 16px !important; text-align: center; } .title-block { font: Helvetica !important; width: 600px; margin: 0 auto; color: #394242; } .node { stroke: black; stroke-width: 1px; } .viz { text-align: left; } image { transition: all .25s linear; } text.legend { color: #fff; } image + text.legend { display inline; } image:hover, image:active { transform: scale(1.3); transition: all .1s linear; cursor: pointer; } .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 = 1, queryThreshold = 0; var width = 1000, height = 1300; 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(1.5) .linkStrength(.02) .gravity(.01) .charge(-5) .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, image = match[0].image, hex = match[0].table_1_color; nodes.push ( {"name": t, "group": 1, "id": i, "total_queries": total_queries, "hex": hex, "image": image}) }) $("#title").text(function() { var r = dataset.content[0]; return " "; }) dataset.content.forEach(function(c) { if (1 == 1) { 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) .on('mouseover', tip.show) .on('mouseout', tip.hide); node.append("circle") .attr("r", function(d) { return Math.sqrt(d.size) / 10 || 4.5; }) .style("fill", "#eee") var images = node.append("svg:image") .attr("xlink:href", function(d) { return d.image;}) .attr("x", function(d) { return -25;}) .attr("y", function(d) { return -25;}) .attr("height", 50) .attr("width", 50); /* node.append("text") .attr("class","legend") .attr("dx", "0") .attr("dy", "2em") .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