Skip to content

Instantly share code, notes, and snippets.

@hokaccha hokaccha/query.sql Secret
Created Feb 4, 2017

Embed
What would you like to do?
Adventar posted entry rate
select
year
, entry_count
, posted_url_count
, round(100.0 * posted_url_count / entry_count, 2) as rate
from (
select
extract(year from date) as year
, count(*) as entry_count
, count(case when url != '' then 1 end) as posted_url_count
from entries
group by 1
) t
order by
1
;
year entry_count posted_url_count rate
2012 793 746 94.07
2013 3827 3346 87.43
2014 5201 4622 88.87
2015 8505 7346 86.37
2016 9261 7922 85.54
<svg class="main-svg" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="700" height="450"><rect x="0" y="0" width="700" height="450" style="fill: rgb(255, 255, 255); fill-opacity: 1;"/><defs id="defs-a4ffca"><g class="clips"><clipPath class="axesclip" id="clipa4ffcax"><rect x="50" y="0" width="557" height="450"/></clipPath><clipPath class="axesclip" id="clipa4ffcay"><rect x="0" y="12" width="700" height="318"/></clipPath><clipPath class="axesclip" id="clipa4ffcaxy"><rect x="50" y="12" width="557" height="318"/></clipPath><clipPath class="plotclip" id="clipa4ffcaxyplot"><rect width="557" height="318"/></clipPath></g></defs><g class="layer-below"><g class="imagelayer"/><g class="shapelayer"/></g><g class="cartesianlayer"><g class="subplot xy"><rect class="bg" x="46" y="8" width="565" height="326" style="stroke-width: 0; fill: rgb(255, 255, 255); fill-opacity: 1;"/><g class="layer-subplot"><g class="shapelayer"/><g class="imagelayer"/></g><g class="gridlayer" transform="translate(50,12)"><path class="xgrid crisp" d="M0,0v318" transform="translate(139.25,0)" style="stroke: rgb(238, 238, 238); stroke-opacity: 1; stroke-width: 1px;"/><path class="xgrid crisp" d="M0,0v318" transform="translate(278.5,0)" style="stroke: rgb(238, 238, 238); stroke-opacity: 1; stroke-width: 1px;"/><path class="xgrid crisp" d="M0,0v318" transform="translate(417.75,0)" style="stroke: rgb(238, 238, 238); stroke-opacity: 1; stroke-width: 1px;"/><path class="ygrid crisp" d="M0,0h557" transform="translate(0,286.58)" style="stroke: rgb(238, 238, 238); stroke-opacity: 1; stroke-width: 1px;"/><path class="ygrid crisp" d="M0,0h557" transform="translate(0,219.52)" style="stroke: rgb(238, 238, 238); stroke-opacity: 1; stroke-width: 1px;"/><path class="ygrid crisp" d="M0,0h557" transform="translate(0,152.46)" style="stroke: rgb(238, 238, 238); stroke-opacity: 1; stroke-width: 1px;"/><path class="ygrid crisp" d="M0,0h557" transform="translate(0,85.4)" style="stroke: rgb(238, 238, 238); stroke-opacity: 1; stroke-width: 1px;"/><path class="ygrid crisp" d="M0,0h557" transform="translate(0,18.35)" style="stroke: rgb(238, 238, 238); stroke-opacity: 1; stroke-width: 1px;"/></g><g class="overgrid"/><g class="zerolinelayer" transform="translate(50,12)"/><g class="overzero"/><g class="plot" transform="translate(50, 12)" clip-path="url(#clipa4ffcaxyplot)"><g class="imagelayer"/><g class="maplayer"/><g class="barlayer"/><g class="boxlayer"/><g class="scatterlayer"><g class="trace scatter trace6197be" style="stroke-miterlimit: 2; opacity: 1;"><path class="js-line" d="M0,16L139.25,238.63L278.5,190.35L417.75,274.17L557,302" style="vector-effect: non-scaling-stroke; fill: none; stroke: rgb(31, 119, 180); stroke-opacity: 1; stroke-width: 2px; opacity: 1;"/><g class="points"/></g></g></g><g class="overplot"/><path class="xlines crisp" transform="translate(50,12)" d="M-5,322.5h567" style="fill: none; stroke-width: 1px; stroke: rgb(0, 0, 0); stroke-opacity: 0;"/><path class="ylines crisp" transform="translate(50,12)" d="M-4.5,-5v327" stroke-width="1px" style="fill: none; stroke: rgb(0, 0, 0); stroke-opacity: 0;"/><g class="overlines"/><g class="xaxislayer" transform="translate(50,12)"><g class="xtick" data-bb="691"><text text-anchor="middle" x="0" y="336" transform="translate(0,0)" style="font-family: 'Open Sans', verdana, arial, sans-serif; font-size: 12px; fill: rgb(68, 68, 68); fill-opacity: 1; visibility: visible; white-space: pre;" data-bb="685">2012</text></g><g class="xtick" data-bb="692"><text text-anchor="middle" x="0" y="336" transform="translate(139.25,0)" style="font-family: 'Open Sans', verdana, arial, sans-serif; font-size: 12px; fill: rgb(68, 68, 68); fill-opacity: 1; visibility: visible; white-space: pre;" data-bb="686">2013</text></g><g class="xtick" data-bb="693"><text text-anchor="middle" x="0" y="336" transform="translate(278.5,0)" style="font-family: 'Open Sans', verdana, arial, sans-serif; font-size: 12px; fill: rgb(68, 68, 68); fill-opacity: 1; visibility: visible; white-space: pre;" data-bb="687">2014</text></g><g class="xtick" data-bb="694"><text text-anchor="middle" x="0" y="336" transform="translate(417.75,0)" style="font-family: 'Open Sans', verdana, arial, sans-serif; font-size: 12px; fill: rgb(68, 68, 68); fill-opacity: 1; visibility: visible; white-space: pre;" data-bb="688">2015</text></g><g class="xtick" data-bb="695"><text text-anchor="middle" x="0" y="336" transform="translate(557,0)" style="font-family: 'Open Sans', verdana, arial, sans-serif; font-size: 12px; fill: rgb(68, 68, 68); fill-opacity: 1; visibility: visible; white-space: pre;" data-bb="689">2016</text></g></g><g class="yaxislayer" transform="translate(50,12)"><g class="ytick"><text text-anchor="end" x="-6" y="6" transform="translate(0,286.58)" style="font-family: 'Open Sans', verdana, arial, sans-serif; font-size: 12px; fill: rgb(68, 68, 68); fill-opacity: 1; visibility: visible; white-space: pre;">86</text></g><g class="ytick"><text text-anchor="end" x="-6" y="6" transform="translate(0,219.52)" style="font-family: 'Open Sans', verdana, arial, sans-serif; font-size: 12px; fill: rgb(68, 68, 68); fill-opacity: 1; visibility: visible; white-space: pre;">88</text></g><g class="ytick"><text text-anchor="end" x="-6" y="6" transform="translate(0,152.46)" style="font-family: 'Open Sans', verdana, arial, sans-serif; font-size: 12px; fill: rgb(68, 68, 68); fill-opacity: 1; visibility: visible; white-space: pre;">90</text></g><g class="ytick"><text text-anchor="end" x="-6" y="6" transform="translate(0,85.4)" style="font-family: 'Open Sans', verdana, arial, sans-serif; font-size: 12px; fill: rgb(68, 68, 68); fill-opacity: 1; visibility: visible; white-space: pre;">92</text></g><g class="ytick"><text text-anchor="end" x="-6" y="6" transform="translate(0,18.35)" style="font-family: 'Open Sans', verdana, arial, sans-serif; font-size: 12px; fill: rgb(68, 68, 68); fill-opacity: 1; visibility: visible; white-space: pre;">94</text></g></g><g class="overaxes"/></g></g><g class="ternarylayer"/><g class="layer-above"><g class="imagelayer"/><g class="shapelayer"/></g><g class="pielayer"/><g class="glimages"/><g class="geoimages"/><text class="js-plot-link-container" y="441" text-anchor="end" x="693" style="font-family: 'Open Sans', Arial, sans-serif; font-size: 12px; fill: rgb(68, 68, 68); pointer-events: all; visibility: visible;"><tspan class="js-link-to-tool"/><tspan class="js-link-spacer"/><tspan class="js-sourcelinks"/></text><defs id="topdefs-a4ffca"><clipPath id="legenda4ffca"><rect width="70" height="29" x="0" y="0"/></clipPath></defs><g class="infolayer"><g class="g-gtitle"/><g class="g-xtitle" data-bb="690"/><g class="g-ytitle" data-bb="696" transform="translate(2,0)"/><g class="legend" pointer-events="all" transform="translate(618.14, 12)"><rect class="bg" shape-rendering="crispEdges" width="70" height="29" x="0" y="0" style="stroke: rgb(68, 68, 68); stroke-opacity: 1; fill: rgb(255, 255, 255); fill-opacity: 1; stroke-width: 0px;"/><g class="scrollbox" transform="translate(0, 0)" clip-path="url(#legenda4ffca)"><g class="groups"><g class="traces" transform="translate(0, 14.5)" style="opacity: 1;"><g class="legendfill"/><g class="legendlines"><path class="js-line" d="M5,0h30" style="fill: none; stroke: rgb(31, 119, 180); stroke-opacity: 1; stroke-width: 2px;"/></g><g class="legendsymbols" style="opacity: 1;"><g class="legendpoints"/></g><text class="legendtext user-select-none" x="40" y="4.680000000000001" style="text-anchor: start; font-family: 'Open Sans', verdana, arial, sans-serif; font-size: 12px; fill: rgb(68, 68, 68); fill-opacity: 1; white-space: pre; visibility: visible;" data-bb="697">rate</text><rect class="legendtoggle" pointer-events="all" x="0" y="-9.5" width="110" height="19" style="cursor: pointer; fill: rgb(0, 0, 0); fill-opacity: 0;"/></g></g></g><rect class="scrollbar" rx="20" ry="2" width="0" height="0" style="fill: rgb(128, 139, 164); fill-opacity: 1;"/></g></g></svg>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.