Report not found

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

Collaborators

Run History
SELECT y.league, y.regular_season_pace, y.playoff_pace, y.playoff_pace/y.regular_season_pace AS percent_slowdown FROM ( SELECT x.league, AVG((x.champ_length_of_season - 1)/(x.champion_regular_season_games - 1)) AS regular_season_pace, AVG((x.champ_length_of_playoffs - 1)/(x.champion_playoff_games - 1)) AS playoff_pace 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, EXTRACT('DAY' FROM z.rg_champ_end - z.rg_champ_start) AS champ_length_of_season, EXTRACT('DAY' FROM z.pl_champ_end - z.pl_champ_start) AS champ_length_of_playoffs, CASE WHEN z.rs_champ_games = 0 THEN 1 ELSE z.rs_champ_games END AS champion_regular_season_games, z.pl_champ_games AS champion_playoff_games FROM ( SELECT t.league, t.season, MAX(w.team) AS champion, MIN(CASE WHEN w.team IS NOT NULL AND t.game_type = 'regular_season' THEN t.date ELSE NULL END) AS rg_champ_start, MAX(CASE WHEN w.team IS NOT NULL AND t.game_type = 'regular_season' THEN t.date ELSE NULL END) AS rg_champ_end, MIN(CASE WHEN w.team IS NOT NULL AND t.game_type != 'regular_season' THEN t.date ELSE NULL END) AS pl_champ_start, MAX(CASE WHEN w.team IS NOT NULL AND t.game_type != 'regular_season' THEN t.date ELSE NULL END) AS pl_champ_end, COUNT(CASE WHEN w.team IS NOT NULL AND t.game_type = 'regular_season' THEN 1 ELSE NULL END)::FLOAT AS rs_champ_games, COUNT(CASE WHEN w.team IS NOT NULL AND t.game_type != 'regular_season' THEN 1 ELSE NULL END)::FLOAT AS pl_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 >= 1969 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 ) y ORDER BY 1 LIMIT 100
SELECT y.league, y.regular_season_pace, y.playoff_pace, y.playoff_pace/y.regular_season_pace AS percent_slowdown FROM ( SELECT x.league, AVG((x.champ_length_of_season - 1)/(x.champion_regular_season_games - 1)) AS regular_season_pace, AVG((x.champ_length_of_playoffs - 1)/(x.champion_playoff_games - 1)) AS playoff_pace 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, EXTRACT('DAY' FROM z.rg_champ_end - z.rg_champ_start) AS champ_length_of_season, EXTRACT('DAY' FROM z.pl_champ_end - z.pl_champ_start) AS champ_length_of_playoffs, CASE WHEN z.rs_champ_games = 0 THEN 1 ELSE z.rs_champ_games END AS champion_regular_season_games, z.pl_champ_games AS champion_playoff_games FROM ( SELECT t.league, t.season, MAX(w.team) AS champion, MIN(CASE WHEN w.team IS NOT NULL AND t.game_type = 'regular_season' THEN t.date ELSE NULL END) AS rg_champ_start, MAX(CASE WHEN w.team IS NOT NULL AND t.game_type = 'regular_season' THEN t.date ELSE NULL END) AS rg_champ_end, MIN(CASE WHEN w.team IS NOT NULL AND t.game_type != 'regular_season' THEN t.date ELSE NULL END) AS pl_champ_start, MAX(CASE WHEN w.team IS NOT NULL AND t.game_type != 'regular_season' THEN t.date ELSE NULL END) AS pl_champ_end, COUNT(CASE WHEN w.team IS NOT NULL AND t.game_type = 'regular_season' THEN 1 ELSE NULL END)::FLOAT AS rs_champ_games, COUNT(CASE WHEN w.team IS NOT NULL AND t.game_type != 'regular_season' THEN 1 ELSE NULL END)::FLOAT AS pl_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 >= 1969 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 ) y ORDER BY 1 LIMIT 100
<style> body { font-family: Helvetica, Arial, sans-serif; font-size: 12px; text-align:center; } h2 {margin-bottom:4px;} p {margin:4px;} .axis path, .axis line { fill: none; stroke: 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> <div id="chart"> <h2>Frequency of Games</h2> </div> <script src="//cdnjs.cloudflare.com/ajax/libs/d3-tip/0.6.3/d3-tip.min.js"></script> <script> var result = dataset.content; var data = []; result.forEach(function(d) { var obj1 = {league:d.league.toUpperCase(),pace:d.regular_season_pace,season:'Regular Season'} var obj2 = {league:d.league.toUpperCase(),pace:d.playoff_pace,season:'Playoffs'} // var obj1 = {league:d.league.toUpperCase(),pace:1,season:'Regular Season'} // var obj2 = {league:d.league.toUpperCase(),pace:d.percent_slowdown,season:'Playoffs'} data.push(obj1); data.push(obj2); }) var margin = {top: 40, right: 10, bottom: 10, left: 40}, width = 300 - margin.left - margin.right, height = 500 - margin.top - margin.bottom; var x = d3.scale.ordinal() .rangeRoundBands([0, width], .2); var y = d3.scale.ordinal() .rangeRoundBands([0, height], .2); var yAxis = d3.svg.axis() .scale(y) .orient("left"); var xAxis = d3.svg.axis() .scale(x) .orient("top"); var color = d3.scale.category10(); var tip = d3.tip() .attr('class', 'd3-tip') .offset([-10,0]) .direction('n') .html(function(d) { return"Days per game: " + d3.round(d.pace,2); }) var svg = d3.select("#chart").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 + ")"); x.domain(data.map(function(d) { return d.season; })); y.domain(data.map(function(d) { return d.league; })); color.domain(data.map(function(d) { return d.league; })); svg.call(tip); svg.selectAll(".bar") .data(data) .enter() .append("rect") .style("fill", function(d) { return color(d.league); }) .attr("x", function(d) { return x(d.season); }) .attr("y", function(d) { return y(d.league); }) .attr("width", y.rangeBand()) .attr("height",y.rangeBand()) .on('mouseover', tip.show) .on('mouseout', tip.hide) .each(function(d){ var that = this; setInterval(function() { d3.select(that) .style("opacity",1) .transition() .duration(d.pace*1000) .style("opacity",0) },d.pace*1000); }); svg.append("g") .attr("class", "y axis") .call(yAxis); svg.append("g") .attr("class", "x axis") .call(xAxis); </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