title: Rolling DAU, MAU using T-SQL
body: <p><strong>Situation:</strong></p>
  
  <p>I have a login table with columns email and login_time. I would like to calculate the daily login time (DAU) and the rolling monthly login time (MAU). The DAU and MAU must count the distinct users. i.e. If someone logs in 20 times in the past 30 days (MAU) then he is only counted once. Same logic goes for DAU. MAU's range is 30 days.</p>
  
  <p><strong>DAU:</strong> is calculated by taking the distinct email login per day.</p>
  
  <p><strong>MAU:</strong> is calculated by taking the distinct email login for the rolling 30 days.</p>
  
  <p><strong>Desired Result:</strong> <em>see fiddle below</em></p>
  
  <pre><code>Date         MAU     DAU     
  2019-04-01   4       2  
  2019-04-02   3       2  
  2019-04-03   4       2   
  </code></pre>
  
  <p>Calculating the DAU is rather simple, however getting the rolling MAU, not sure how.</p>
  
  <p><strong>Fiddle:</strong></p>
  
  <pre><code>create table #t1 (email varchar(max), login_time datetime)
  insert into #t1 values 
  ('aa@gmail.com', '2019-03-15 00:00:00.000'),
  ('aa@gmail.com', '2019-04-01 00:00:00.000'),
  ('aa@gmail.com', '2019-04-02 00:00:00.000'),
  ('aa@gmail.com', '2019-04-03 00:00:00.000'),
  
  ('bb@gmail.com', '2019-03-19 00:00:00.000'),
  ('bb@gmail.com', '2019-04-01 00:00:00.000'),
  ('bb@gmail.com', '2019-04-02 00:00:00.000'),
  ('bb@gmail.com', '2019-04-02 00:00:00.000'),
  
  ('cc@gmail.com', '2019-03-02 00:00:00.000'),
  ('cc@gmail.com', '2019-04-03 00:00:00.000'),
  
  ('dd@gmail.com', '2019-03-06 00:00:00.000')
  </code></pre>