perdona,
son tablas de prestahop 1.7
esto es lo que he intentado
SELECT SQL_CALC_FOUND_ROWS a.`reference`, `email`, `total_paid_tax_incl`, `payment`, a.`date_add` AS `date_add`,
CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `customer`,
IF((SELECT so.id_order FROM `ps_orders` so WHERE so.id_customer = a.id_customer AND so.id_order < a.id_order LIMIT 1) > 0, 0, 1) as new,
country_lang.name as cname
FROM `ps_orders` a
LEFT JOIN `ps_customer` c ON (c.`id_customer` = a.`id_customer`)
INNER JOIN `ps_address` address ON address.id_address = a.id_address_delivery
INNER JOIN `ps_country` country ON address.id_country = country.id_country
INNER JOIN `ps_country_lang` country_lang ON (country.`id_country` = country_lang.`id_country` AND country_lang.`id_lang` = 3)
LEFT JOIN `ps_order_state` os ON (os.`id_order_state` = a.`current_state`)
LEFT JOIN `ps_order_state_lang` osl ON (os.`id_order_state` = osl.`id_order_state` AND osl.`id_lang` = 3)
LEFT JOIN `ps_shop` shop
ON a.`id_shop` = shop.`id_shop` WHERE 1 AND a.id_shop IN (1) and date_add < CURRENT_DATE - 30
ORDER BY a.id_order DESC
pero no me saca el ultimo pedido de cada cliente que haga más de 30 dias que no haya pedido...