Facebook
From Buff Dolphin, 1 Month ago, written in SQL.
Embed
Download Paste or View Raw
Hits: 63
  1. SELECT a.billing_country AS country,
  2. total_invoice,
  3. total_customer
  4.  
  5. FROM (SELECT
  6. billing_country, COUNT(total) AS total_invoice
  7. FROM invoice
  8. WHERE EXTRACT(YEAR FROM (CAST(invoice_date AS TIMESTAMP))) IN (SELECT YEAR
  9. FROM
  10. (SELECT EXTRACT(YEAR FROM (CAST(invoice_date AS TIMESTAMP))) AS YEAR, SUM(total) AS revenue
  11. FROM invoice
  12. WHERE EXTRACT(MONTH FROM (CAST(invoice_date AS TIMESTAMP))) IN (6,7,8)
  13. GROUP BY YEAR
  14. ORDER BY revenue DESC
  15. LIMIT 1) AS correct_year)
  16. GROUP BY billing_country
  17. ORDER BY total_invoice DESC) AS s
  18.  
  19. LEFT JOIN
  20. (SELECT
  21. billing_country, COUNT(customer_id) AS total_customer
  22. FROM invoice
  23. GROUP BY billing_country) AS a ON s.billing_country = a.billing_country
  24. ORDER BY total_invoice DESC, country