Excersize

From Ivan Kirilenkov, 4 Months ago, written in SQL, viewed 79 times.

URL http://codebin.org/view/99330591 Embed

Download Paste or View Raw

SELECT
    invoice.billing_country AS country,
    COUNT(DISTINCT invoice.invoice_id) AS total_invoice,
    COUNT(DISTINCT client.customer_id) AS total_customer
FROM invoice AS invoice
JOIN client AS client
    ON client.customer_id = invoice.customer_id
WHERE 1=1
    AND EXTRACT(YEAR FROM CAST(invoice_date AS DATE)) = (
        SELECT
            EXTRACT(YEAR FROM CAST(invoice_date AS DATE)) AS YEAR
        FROM invoice
        WHERE 1=1
            AND EXTRACT(MONTH FROM CAST(invoice_date AS DATE)) IN (6,7,8)
        GROUP BY EXTRACT(YEAR FROM CAST(invoice_date AS DATE))
        ORDER BY SUM(total) DESC
        LIMIT 1
)
GROUP BY  1
ORDER BY 2 DESC, 1

Reply to "Excersize"

Here you can reply to the paste above

Author What's your name?

Title Give your paste a title.

Language What language is your paste written in?

Your paste Paste your paste here

Create Shorturl Create a shorter url that redirects to your paste?

Private Private paste aren't shown in recent listings.

Delete After When should we delete your paste?