Statistics: Number of confirmations and participants from February 8 to May 30th, 2021
<a href="//commons.wikimedia.org/wiki/User:Euku" title="User:Euku">Euku</a>
<p><span class="int-own-work" lang="en">Own work</span>
</p>
<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="o">//</span><span class="w"> </span><span class="n">Diese</span><span class="w"> </span><span class="n">Abfrage</span><span class="w"> </span><span class="n">betrachtet</span><span class="w"> </span><span class="n">nicht</span><span class="w"> </span><span class="n">die</span><span class="w"> </span><span class="n">als</span><span class="w"> </span><span class="n">gelöscht</span><span class="w"> </span><span class="n">oder</span><span class="w"> </span><span class="n">gesperrt</span><span class="w"> </span><span class="n">markierte</span><span class="w"> </span><span class="n">Bestätigungen</span><span class="w"> </span><span class="n">und</span><span class="w"> </span><span class="n">Benutzer</span><span class="p">.</span><span class="w"> </span><span class="n">Somit</span><span class="w"> </span><span class="n">gibt</span><span class="w"> </span><span class="n">es</span><span class="w"> </span><span class="n">Abweichungen</span><span class="p">.</span>
<span class="n">mysql</span><span class="w"> </span><span class="c1">--defaults-file=~/replica.pb-db.cnf -h tools-db s51344__pb -e "</span>
<span class="k">SELECT</span><span class="w"> </span><span class="nb">Date</span><span class="p">,</span><span class="w"> </span><span class="n">IFNULL</span><span class="p">(</span><span class="n">newUsers</span><span class="p">,</span><span class="w"> </span><span class="mi">0</span><span class="p">),</span><span class="w"> </span><span class="n">IFNULL</span><span class="p">(</span><span class="n">newConfs</span><span class="p">,</span><span class="w"> </span><span class="mi">0</span><span class="p">)</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="p">(</span>
<span class="k">SELECT</span><span class="w"> </span><span class="nb">DATE</span><span class="p">(</span><span class="n">LEAST</span><span class="p">(</span><span class="n">IFNULL</span><span class="p">(</span><span class="n">cf</span><span class="p">.</span><span class="n">cf_timestamp_Date</span><span class="p">,</span><span class="w"> </span><span class="n">NOW</span><span class="p">()),</span><span class="w"> </span><span class="n">IFNULL</span><span class="p">(</span><span class="n">u</span><span class="p">.</span><span class="n">user_participates_since_Date</span><span class="p">,</span><span class="w"> </span><span class="n">NOW</span><span class="p">())))</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="nb">Date</span><span class="p">,</span><span class="w"> </span><span class="n">newUsers</span><span class="p">,</span><span class="w"> </span><span class="n">newConfs</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="p">(</span>
<span class="w">	</span><span class="k">SELECT</span><span class="w"> </span><span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">newConfs</span><span class="p">,</span><span class="w"> </span><span class="nb">DATE</span><span class="p">(</span><span class="n">cf_timestamp</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">cf_timestamp_Date</span><span class="w">  </span><span class="k">from</span><span class="w"> </span><span class="n">confirmation</span><span class="w"> </span><span class="k">GROUP</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="nb">DATE</span><span class="p">(</span><span class="n">cf_timestamp</span><span class="p">)</span>
<span class="p">)</span><span class="w"> </span><span class="n">cf</span><span class="w"> </span><span class="k">LEFT</span><span class="w"> </span><span class="k">JOIN</span><span class="w"> </span><span class="p">(</span>
<span class="w">	</span><span class="k">SELECT</span><span class="w"> </span><span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">newUsers</span><span class="p">,</span><span class="w"> </span><span class="nb">DATE</span><span class="p">(</span><span class="n">user_participates_since</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">user_participates_since_Date</span><span class="w"> </span><span class="k">from</span><span class="w"> </span><span class="k">user</span><span class="w"> </span><span class="k">GROUP</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="nb">DATE</span><span class="p">(</span><span class="n">user_participates_since</span><span class="p">)</span>
<span class="p">)</span><span class="w"> </span><span class="n">u</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">cf</span><span class="p">.</span><span class="n">cf_timestamp_Date</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">u</span><span class="p">.</span><span class="n">user_participates_since_Date</span>
<span class="k">UNION</span>
<span class="k">SELECT</span><span class="w"> </span><span class="nb">DATE</span><span class="p">(</span><span class="n">LEAST</span><span class="p">(</span><span class="n">IFNULL</span><span class="p">(</span><span class="n">cf</span><span class="p">.</span><span class="n">cf_timestamp_Date</span><span class="p">,</span><span class="w"> </span><span class="n">NOW</span><span class="p">()),</span><span class="w"> </span><span class="n">IFNULL</span><span class="p">(</span><span class="n">u</span><span class="p">.</span><span class="n">user_participates_since_Date</span><span class="p">,</span><span class="w"> </span><span class="n">NOW</span><span class="p">())))</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="nb">Date</span><span class="p">,</span><span class="w"> </span><span class="n">newUsers</span><span class="p">,</span><span class="w"> </span><span class="n">newConfs</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="p">(</span>
<span class="w">	</span><span class="k">SELECT</span><span class="w"> </span><span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">newConfs</span><span class="p">,</span><span class="w"> </span><span class="nb">DATE</span><span class="p">(</span><span class="n">cf_timestamp</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">cf_timestamp_Date</span><span class="w">  </span><span class="k">from</span><span class="w"> </span><span class="n">confirmation</span><span class="w"> </span><span class="k">GROUP</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="nb">DATE</span><span class="p">(</span><span class="n">cf_timestamp</span><span class="p">)</span>
<span class="p">)</span><span class="w"> </span><span class="n">cf</span><span class="w"> </span><span class="k">RIGHT</span><span class="w"> </span><span class="k">JOIN</span><span class="w"> </span><span class="p">(</span>
<span class="w">	</span><span class="k">SELECT</span><span class="w"> </span><span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">newUsers</span><span class="p">,</span><span class="w"> </span><span class="nb">DATE</span><span class="p">(</span><span class="n">user_participates_since</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">user_participates_since_Date</span><span class="w"> </span><span class="k">from</span><span class="w"> </span><span class="k">user</span><span class="w"> </span><span class="k">GROUP</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="nb">DATE</span><span class="p">(</span><span class="n">user_participates_since</span><span class="p">)</span>
<span class="p">)</span><span class="w"> </span><span class="n">u</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">cf</span><span class="p">.</span><span class="n">cf_timestamp_Date</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">u</span><span class="p">.</span><span class="n">user_participates_since_Date</span>
<span class="p">)</span><span class="w"> </span><span class="n">unionTable</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="nb">Date</span><span class="p">;</span><span class="err">"</span><span class="w"> </span><span class="o">&gt;</span><span class="w"> </span><span class="n">pb</span><span class="o">-</span><span class="n">statistik</span><span class="p">.</span><span class="n">txt</span>
</pre></div>
Created with Excel.