tpch.q7.sql Maven / Gradle / Ivy

Go to download

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; tables: supplier,lineitem,orders,customer,nation
SELECT supp_nation,
       cust_nation,
       l_year,
       sum(volume) AS revenue
FROM (SELECT n1.n_name                          AS supp_nation,
             n2.n_name                          AS cust_nation,
             extract(YEAR FROM l_shipdate)      AS l_year,
             l_extendedprice * (1 - l_discount) AS volume
      FROM supplier,
           lineitem,
           orders,
           customer,
           nation n1,
           nation n2
      WHERE s_suppkey = l_suppkey
        AND o_orderkey = l_orderkey
        AND c_custkey = o_custkey
        AND s_nationkey = n1.n_nationkey
        AND c_nationkey = n2.n_nationkey
        AND (
              (n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY')
              OR (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE')
          )
        AND l_shipdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31') AS shipping
GROUP BY supp_nation,
         cust_nation,
         l_year
ORDER BY supp_nation,
         cust_nation,
         l_year