Untitled

mail@pastecode.io avatar
unknown
plain_text
5 months ago
646 B
2
Indexable
Never
SELECT c.country,
c.total_invoice,
d.total_customer
FROM
(SELECT billing_country AS country,
COUNT(invoice_id) AS total_invoice
FROM invoice
WHERE EXTRACT(year FROM CAST(invoice_date AS date)) IN
(SELECT EXTRACT(year FROM CAST(invoice_date AS date))
FROM invoice
WHERE EXTRACT(month FROM CAST(invoice_date AS date)) BETWEEN 6 AND 8
GROUP BY EXTRACT(year FROM CAST(invoice_date AS date))
ORDER BY SUM(total) DESC
LIMIT 1)
GROUP BY country) AS c
JOIN
(SELECT billing_country,
COUNT(customer_id) AS total_customer
FROM invoice
GROUP BY billing_country) AS d ON c.country = d.billing_country
ORDER BY total_invoice DESC, country