- SELECT i.billing_country AS country,
- COUNT(i.invoice_id) AS total_invoice,
- COUNT(DISTINCT c.customer_id) AS total_customer
- FROM
- (SELECT i.billing_country,
- COUNT(i.invoice_id)
- FROM invoice as i
- 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)
- LEFT OUTER JOIN
- (SELECT c.country,
- COUNT(DISTINCT c.customer_id)
- FROM client
- GROUP BY country) AS c ON i.customer_id = c.customer_id;
