Mysql in-time query
<p>I'm in need of some help structuring in-time queries. There's a few of them I need - but I think that if I can be shown how to do one, I can figure out the others. </p>

<p>What I'm after:
-Rolling 12 month view of 'inactive accounts'...ie number of accounts that have not placed an order in the 12 months prior. 
-This ideally will be a subquery (in a much larger script) joining back on to a dates table (see below)</p>

<pre><code>January 2015 | # of customers with no orders from 1/2014-1/2015
February 2015 | # of customers with no orders from 2/2014-2/2015
March 2015 | # of customers with no orders from 3/2014-3/2015
etc...
</code></pre>

<p>What I'm having trouble wrapping my mind around is how I'd structure a where clause to ensure that it scans all orders and only returns the total of account ID's that had not placed an order in the year prior to that month. I've used different combinations of DATEDIFF, DATESUB etc.</p>

<pre><code>SELECT DATE_FORMAT(order_datetime, '%Y-%m'), COUNT DISTINCT (account_id)
FROM warehouse.orders
JOIN warehouse.accounts ON xyz
WHERE...
</code></pre>

<p>It feels like I'm on the right path - I just keep mentally going in circles trying to figure this out.</p>

<p>Cheers and thanks in advance. </p>