Untitled
unknown
plain_text
2 years ago
648 B
24
Indexable
SELECT i.billing_country AS country, COUNT(i.invoice_id) AS total_invoice, COUNT(DISTINCT c.customer_id) AS total_customer FROM invoice AS i RIGHT JOIN client AS c ON c.customer_id = i.customer_id WHERE EXTRACT(YEAR FROM CAST(i.invoice_date AS DATE)) = ( SELECT EXTRACT(YEAR FROM CAST(i.invoice_date AS DATE)) AS year FROM invoice AS i WHERE EXTRACT(MONTH FROM CAST(i.invoice_date AS DATE)) IN (6, 7, 8) GROUP BY year ORDER BY SUM(i.total) DESC LIMIT 1 ) GROUP BY i.billing_country ORDER BY total_invoice DESC, country;
Editor is loading...