- SELECT i.billing_country AS country,
- COUNT(i.total) AS total_invoice,
- COUNT(DISTINCT c.customer_id) AS total_customer
- FROM invoice AS i
- LEFT OUTER JOIN client AS c ON c.customer_id = i.customer_id
- WHERE EXTRACT(YEAR FROM CAST(invoice_date AS timestamp)) IN
- (SELECT EXTRACT(YEAR FROM CAST(invoice_date AS timestamp))
- FROM invoice
- WHERE EXTRACT(MONTH FROM CAST(invoice_date AS timestamp)) IN (6,7,8)
- GROUP BY EXTRACT(YEAR FROM CAST(invoice_date AS timestamp))
- ORDER BY SUM(total) DESC
- LIMIT 1)
- GROUP BY i.billing_country
- ORDER BY total_invoice DESC, country;
