gistfile1.sql

//Get cohort report on WC buyers
SELECT DATE_FORMAT( o.date, '%Y-%m' ) as 'Year/Month', count(oi.id) as 'Total orders', count( DISTINCT c.id ) as 'Number of users', SUM( oi.line_item_total ) as 'Gross Revenue'
FROM order_item as oi
LEFT JOIN `order` as o on oi.order_id = o.id
left join customer as c on o.customer_id = c.id
LEFT JOIN product as p on p.id = oi.product_id
LEFT JOIN product_product_cat_rel AS rel on rel.product_id = p.id
WHERE DATE_FORMAT(c.date_registered, '%Y-%m') = '2014-01'
AND oi.line_item_total > 0
AND DATE_FORMAT(o.date, '%Y') > 2013
AND DATE_FORMAT(o.date, '%Y') < 2015
AND rel.product_cat_id = 1021
GROUP BY DATE_FORMAT( o.date, '%Y-%m' )