Magento stock alerts report queries

Here are some interesting queries to obtain reports from the Magento database:

Total number registered

Last 30 days

select count(*) as totalregistered from product_alert_stock where add_date >= '2014-10-12 00:00:00' and add_date <= '2014-11-10 23:59:59';
Last year
select count(*) as totalregistered from product_alert_stock where add_date >= '2013-11-11 00:00:00' and add_date <= '2014-11-10 23:59:59';
All time
select count(*) as totalregistered from product_alert_stock;
Total number customers registered Last 30 days
select count(*) as totalcustomersregistered from product_alert_stock where add_date >= '2014-10-12 00:00:00' and add_date <= '2014-11-10 23:59:59' group by customer_id;
Last year
select count(*) as totalcustomersregistered from product_alert_stock where add_date >= '2013-11-11 00:00:00' and add_date <= '2014-11-10 23:59:59' group by customer_id;
All time
select count(*) as totalcustomersregistered from product_alert_stock group by customer_id;
Total number sent Last 30 days
select sum(send_count) as totalsent from product_alert_stock where send_date >= '2014-10-12 00:00:00' and send_date <= '2014-11-10 23:59:59' and send_count > 0;

Last year

select sum(send_count) as totalsent from product_alert_stock where send_date >= '2013-11-11 00:00:00' and send_date <= '2014-11-10 23:59:59' and send_count > 0;

All time

select sum(send_count) as totalsent from product_alert_stock where send_count > 0;

Get a list of products for which an alert has been registered but no email sent (to identify inventory shortfalls)


select a.sku
from product_alert_stock
inner join (SELECT entity_id as product_id, sku FROM catalog_product_entity) as a on product_alert_stock.product_id = a.product_id
where
send_count = 0
and add_date >= '2017-01-06 00:00:00' and add_date <= '2017-02-05 23:59:59' group by product_alert_stock.product_id;