tpch.q9.sql Maven / Gradle / Ivy

Show more of this group Show more artifacts with this name
Show all versions of druid Show documentation

An JDBC datasource implementation.

There is a newer version: 1.2.23

-- 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