• Home
• com.facebook.presto
• presto-product-tests
• 0.288
• source code
• q09.sql

×

Project download

Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $

You can buy this project and download/modify it how often you want.

sql-tests.testcases.hive_tpch.q09.sql Maven / Gradle / Ivy

-- database: presto; groups: tpch, big_query; tables: part,supplier,lineitem,partsupp,orders,nation
SELECT
  nation,
  o_year,
  sum(amount) AS sum_profit
FROM (
       SELECT
         n_name                                                          AS nation,
         extract(YEAR FROM o_orderdate)                                  AS o_year,
         l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount
       FROM
         part,
         supplier,
         lineitem,
         partsupp,
         orders,
         nation
       WHERE
         s_suppkey = l_suppkey
         AND ps_suppkey = l_suppkey
         AND ps_partkey = l_partkey
         AND p_partkey = l_partkey
         AND o_orderkey = l_orderkey
         AND s_nationkey = n_nationkey
         AND p_name LIKE '%green%'
     ) AS profit
GROUP BY
  nation,
  o_year
ORDER BY
  nation,
  o_year DESC