TPC-H Q15

WITH revenue0(supplier_no, total_revenue) AS (
  SELECT
    l_suppkey,
    SUM(l_extendedprice * (
      1 - l_discount
    ))
  FROM lineitem
  WHERE
    l_shipdate >= CAST('1993-07-01' AS DATE)
    AND l_shipdate < DATEADD(MONTH, 3, CAST('1993-07-01' AS DATE))
  GROUP BY
    l_suppkey
)
SELECT
  s_suppkey,
  s_name,
  s_address,
  s_phone,
  total_revenue
FROM supplier, revenue0
WHERE
  s_suppkey = supplier_no
  AND total_revenue = (
    SELECT
      MAX(total_revenue)
    FROM revenue0
  )
ORDER BY
  s_suppkey;