Facebook
From Baby Motmot, 1 Month ago, written in SQL.
Embed
Download Paste or View Raw
Hits: 62
  1. SELECT EXTRACT(YEAR FROM (CAST(invoice_date AS TIMESTAMP))) AS YEAR,
  2. billing_country, COUNT(total) AS total_invoice
  3. FROM invoice
  4. GROUP BY YEAR, billing_country
  5. WHERE YEAR IN (SELECT YEAR
  6. FROM
  7. (SELECT EXTRACT(YEAR FROM (CAST(invoice_date AS TIMESTAMP))) AS YEAR, SUM(total) AS revenue
  8. FROM invoice
  9. WHERE EXTRACT(MONTH FROM (CAST(invoice_date AS TIMESTAMP))) IN (6,7,8)
  10. GROUP BY YEAR
  11. ORDER BY revenue DESC
  12. LIMIT 1) AS correct_year)
  13. ORDER BY total_invoice DESC