Distinct Counting over period of time
<p>I have a simple table that consists of user_id, action_date </p>

<pre><code>user_id    action_date
user_001    2016-04-15
user_002    2016-04-15
user_003    2016-04-15
user_002    2016-04-15
user_001    2016-04-15
user_004    2016-04-16
user_005    2016-04-16
user_001    2016-04-16
user_001    2016-04-16
user_001    2016-04-16
</code></pre>

<p>and for every action_date in the database I need to check how many distinct users have been active in the period of 30 days counting back from that action_date.</p>

<p>I know I have to go back 30 days for every action_date and calculate distinct active users in that period, I tried something like this, but it doesn't work as intendent, as <strong>I need distinct count for every 30 days period proceeding action_date</strong>.</p>

<pre><code>SELECT action_date, COUNT (DISTINCT user_id) from Table_1 where user_id IN
(SELECT user_id from Table_1
WHERE action_date Between DATEADD(day,-30,action_date) and action_date)
GROUP BY action_date
</code></pre>