Report not found

There was a problem finding this report.
Playoff Length - Chart 4
April 30, 2014 · Refreshed almost 5 years ago
Description
Full blog post: http://blog.modeanalytics.com/are-the-playoffs-taking-forever/

Collaborators

Run History
SELECT x.league, AVG(x.playoff_games) AS avg_playoff_games FROM ( SELECT z.league, CASE WHEN LENGTH(z.season) = 5 THEN CASE WHEN LEFT(RIGHT(z.season,2),1) IN ('0','1') THEN CONCAT('20',RIGHT(z.season,2))::INT ELSE CONCAT('19',RIGHT(z.season,2))::INT END ELSE z.season::INT END AS season, z.champion, CASE WHEN z.playoff_games = 0 THEN 1 ELSE z.playoff_games END AS playoff_games, CASE WHEN z.rs_champ_games = 0 THEN 1 ELSE z.rs_champ_games END AS champion_regular_season_games FROM ( SELECT t.league, t.season, MAX(w.team) AS champion, COUNT(CASE WHEN t.game_type != 'regular_season' THEN 1 ELSE NULL END)/2::FLOAT AS playoff_games, COUNT(CASE WHEN w.team IS NOT NULL AND t.game_type = 'regular_season' THEN 1 ELSE NULL END)::FLOAT AS rs_champ_games FROM benn.professional_sports_game_log t LEFT JOIN (SELECT c.league, c.season, c.team, c.date, MAX(c.date) OVER (PARTITION BY c.league,c.season) AS last_game, c.result FROM benn.professional_sports_game_log c WHERE c.result = 'win' AND c.game_type != 'regular_season' ) w ON w.league = t.league AND w.season = t.season AND w.team = t.team AND w.date = w.last_game WHERE t.date >= '1969-01-01' GROUP BY 1,2 ) z ) x WHERE x.season >= 2003 AND (x.season != 1994 OR x.league != 'mlb') AND (x.season != 1982 OR x.league != 'nfl') AND (x.season != 1995 OR x.league != 'nhl') AND (x.season != 2005 OR x.league != 'nhl') AND (x.season != 2013 OR x.league != 'nhl') AND (x.season != 1999 OR x.league != 'nba') AND (x.season != 2012 OR x.league != 'nba') GROUP BY 1 ORDER BY 1 LIMIT 100
SELECT x.league, AVG(x.playoff_games) AS avg_playoff_games FROM ( SELECT z.league, CASE WHEN LENGTH(z.season) = 5 THEN CASE WHEN LEFT(RIGHT(z.season,2),1) IN ('0','1') THEN CONCAT('20',RIGHT(z.season,2))::INT ELSE CONCAT('19',RIGHT(z.season,2))::INT END ELSE z.season::INT END AS season, z.champion, CASE WHEN z.playoff_games = 0 THEN 1 ELSE z.playoff_games END AS playoff_games, CASE WHEN z.rs_champ_games = 0 THEN 1 ELSE z.rs_champ_games END AS champion_regular_season_games FROM ( SELECT t.league, t.season, MAX(w.team) AS champion, COUNT(CASE WHEN t.game_type != 'regular_season' THEN 1 ELSE NULL END)/2::FLOAT AS playoff_games, COUNT(CASE WHEN w.team IS NOT NULL AND t.game_type = 'regular_season' THEN 1 ELSE NULL END)::FLOAT AS rs_champ_games FROM benn.professional_sports_game_log t LEFT JOIN (SELECT c.league, c.season, c.team, c.date, MAX(c.date) OVER (PARTITION BY c.league,c.season) AS last_game, c.result FROM benn.professional_sports_game_log c WHERE c.result = 'win' AND c.game_type != 'regular_season' ) w ON w.league = t.league AND w.season = t.season AND w.team = t.team AND w.date = w.last_game WHERE t.date >= '1969-01-01' GROUP BY 1,2 ) z ) x WHERE x.season >= 2003 AND (x.season != 1994 OR x.league != 'mlb') AND (x.season != 1982 OR x.league != 'nfl') AND (x.season != 1995 OR x.league != 'nhl') AND (x.season != 2005 OR x.league != 'nhl') AND (x.season != 2013 OR x.league != 'nhl') AND (x.season != 1999 OR x.league != 'nba') AND (x.season != 2012 OR x.league != 'nba') GROUP BY 1 ORDER BY 1
<style> body { font-family: Helvetica, Arial, sans-serif; font-size: 12px; text-align:center; } .bar { fill: steelblue; } .bar:hover { fill: brown; } .axis path, .axis line { fill: none; stroke: #000; shape-rendering: crispEdges; } .x.axis path { display: none; } .d3-tip { line-height: 1.5; padding: 12px; background: rgba(0, 0, 0, 0.8); color: #fff; border-radius: 2px; text-align: center; } .d3-tip:after { display: inline; position: absolute; } </style> <body> <div id="chart"> <h3 style="margin-bottom:5px">Average Games in the Playoffs</h3> <p style="margin-top:5px">2003-2013</p> </div> <script src="//cdnjs.cloudflare.com/ajax/libs/underscore.js/1.6.0/underscore-min.js"></script> <script src="//cdnjs.cloudflare.com/ajax/libs/d3-tip/0.6.3/d3-tip.min.js"></script> <script> var margin = {top: 20, right: 20, bottom: 30, left: 40}, width = 400 - margin.left - margin.right, height = 300 - margin.top - margin.bottom; var x = d3.scale.ordinal() .rangeRoundBands([0, width], .1); var y = d3.scale.linear() .range([height, 0]); var xAxis = d3.svg.axis() .scale(x) .orient("bottom"); var yAxis = d3.svg.axis() .scale(y) .orient("left") .ticks(5); var tip = d3.tip() .attr('class', 'd3-tip') .offset([-10,0]) .direction('n') .html(function(d) { return"Average games: " + Math.round(d.avg_playoff_games,1); }) var svg = d3.select("body").append("svg") .attr("width", width + margin.left + margin.right) .attr("height", height + margin.top + margin.bottom) .append("g") .attr("transform", "translate(" + margin.left + "," + margin.top + ")"); result = dataset.content x.domain(_.pluck(result, 'league').map(function (value){ return value.toUpperCase(); })); y.domain([0,90]); svg.call(tip); svg.append("g") .attr("class", "x axis") .attr("transform", "translate(0," + height + ")") .call(xAxis); svg.append("g") .attr("class", "y axis") .call(yAxis); svg.selectAll(".bar") .data(result) .enter().append("rect") .attr("class", "bar") .attr("x", function(d) { return x(d.league.toUpperCase()) ; }) .attr("width", x.rangeBand()) .attr("y", function(d) { return y(d.avg_playoff_games); }) .attr("height", function(d) { return height - y(d.avg_playoff_games); }) .on('mouseover', tip.show) .on('mouseout', tip.hide); </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