PrestaShop SQL useful Reports

Report 1 – Query sum of the products sold each month for each category in PrestaShop.

Prestashop tabels: [ps_order_detail], [ps_orders], [ps_product], [ps_order_history]

Combine aggregation function such as COCNAT and YEAR and DATE with multiple GROUP BY conditions.

SELECT CONCAT(YEAR(o.invoice_date),' - ', MONTH(o.invoice_date)) as 'year-month', substr(p.reference, 1, 3) as aka, COUNT(*) as num_sales 
 FROM ps_order_detail od 
 LEFT JOIN ps_orders o ON (od.id_order = o.id_order) 
 LEFT JOIN ps_product p ON od.product_id = p.id_product 
 WHERE o.date_add BETWEEN '2016-07-01' AND '2050-07-31' 
 AND o.id_order NOT IN ( 
       SELECT id_order 
       FROM ps_order_history 
       WHERE id_order_state = 6
       ) 
 GROUP BY CONCAT(YEAR(o.invoice_date),' - ', MONTH(o.invoice_date)), substr(p.reference, 1, 3)

Report 2 – Sum customers paid orders and show last activity date in PrestaShop.

PrestaShop tables: [ps_customer], [ps_orders], [ps_order_state]

You can use this report to count orders nad value of paid orders for each user with order.

SELECT MAX(o.date_add) AS "ostatnie zamówienie", COUNT(o.id_order) AS "ilosc zamówień", SUM(o.total_paid_tax_incl) AS "suma zamówień z podatkiem", c.id_customer, c.firstname, c.lastname, c.email, c.last_passwd_gen, c.newsletter_date_add, c.date_add, c.date_upd
FROM ps_customer c
LEFT JOIN ps_orders o
ON c.id_customer = o.id_customer
LEFT JOIN ps_order_state os
ON o.current_state = os.id_order_state
WHERE os.paid = 1
GROUP BY c.id_customer
HAVING COUNT(o.id_order) > 0
ORDER BY "suma zamówień z podatkiem" DESC

Useful resources:
PrestaShop database table structure: https://github.com/PrestaShop/PrestaShop/blob/1.7.7.x/install-dev/data/db_structure.sql (some tables might not be included due to class mapping – ORM)