Untitled

mail@pastecode.io avatar
unknown
plain_text
a year ago
648 B
17
Indexable
Never
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;