title: PostgreSQL - Getting statistical data
body: <p>I need to collect some statistical information in my application.
  I have a table of users (tb_user)
  Every time a new user accesses the application, it adds a new record in this table, ie, one line for each user. The main field are <strong>id</strong> and <strong>date_hour</strong> (timestamp for the first time user accessed the application).</p>
  
  <p><strong>tb_user</strong></p>
  
  <pre><code>id (bigint) | date_time (timestamp with time zone)
   1          |  2012-01-29 11:29:50.359-03
   2          |  2012-01-31 14:27:10.359-03
  </code></pre>
  
  <p><strong>I  need get:</strong></p>
  
  <p><strong>amount average users by day, week and month</strong></p>
  
  <p>Example:</p>
  
  <p>by day: 55.45</p>
  
  <p>by week : XX.XX</p>
  
  <p>month: XX.XX</p>
  
  <p><em><strong>EDIT:</em></strong></p>
  
  <p>My best solution was:</p>
  
  <pre><code>WITH daily_count AS (SELECT COUNT(id) AS user_count FROM tb_user)
  SELECT user_count, tbaux2.days, (user_count/tbaux2.days) FROM daily_count, 
      (SELECT EXTRACT(DAY FROM (t2.diff) ) + 1 AS days
       FROM
         (with tbaux AS(SELECT  min(date_time) AS min FROM tb_user)
         SELECT (now() - min) AS diff
         FROM tbaux) AS t2) AS tbaux2
  GROUP BY user_count, tbaux2.days
  </code></pre>
  
  <p>But this solution only worked with EXTRACT (DAY ... With weeks and month did not work</p>
  
  <p>Any help is welcome.</p>
  
  <p>Alternatively:</p>
  
  <pre><code>SELECT user_count, tbaux2.days, (user_count/tbaux2.days) AS userPerDay, ((user_count/tbaux2.days) * 7) AS userPerWeek, ((user_count/tbaux2.days) * 30) AS userPerMonth
  </code></pre>
  
  <p><strong>EDIT 2:</strong></p>
  
  <p>Based on responses from @Bruno, there are some considerations:</p>
  
  <p>When I asked the question, in really I requested a way to select data by day, month and year. I believe that the search that I posted and @Bruno refined, should be interpreted as average of <strong>"a day, every 7 days and every 30 days"</strong> and not by days, weeks and months. I believe that if it is interpreted  in this way, there not will be problems of gender-quoted in example (10% drop). I believe this approach of "every" is answer I need in moment, so will sign this answer.</p>
  
  <p>I suggest as an improvement of post:</p>
  
  <ul>
  <li>Consider only closed day  in result (not collect users of the current day, and not counting the current day in division)</li>
  <li>The result is two numeric digits.</li>
  <li>New research  considering a data really per week and per month.</li>
  </ul>
  
  <p>Thanks.</p>