title: How to calculate daily average frequency?
body: <p>I have this table <code>my_table</code>:</p>
  
  <pre><code>recorder_id    person_id     day
  A1             1             2017-06-03 12:30
  A1             1             2017-06-03 12:45
  B1             1             2017-06-03 12:50
  A1             2             2017-06-03 16:40
  B1             2             2017-06-03 16:45
  B1             2             2017-06-03 18:20
  A1             1             2017-06-04 11:22
  </code></pre>
  
  <p>I want to know how many times each person passes by each recorder on average per day. For example, a person with id 1 passes by recorder A1 on average 1.5 times per day, while person 2 passes by this recorder 0.5 times per day on average (because this person does not have records for 2017-06-04). The same logic should be applied to B1.</p>
  
  <pre><code>recorder_id   person_id   daily_average_per_person
  A1            1           1.5 
  A1            2           0.5
  B1            1           0.5
  B1            2           1.0 
  </code></pre>
  
  <p>How can I get this result?</p>
  
  <p>I tried this query, but I don't know how to calculate a daily average per unique person:</p>
  
  <pre><code>SELECT recorder_id, person_id,
         to_date(day) as hour,
         count(*) as hourly_count
  
  FROM        my_table
  
  GROUP BY    recorder_id, person_id, to_date(day)
  
  ORDER BY    hourly_count;
  </code></pre>