• Home
• io.trino
• trino-benchmark-queries
• 468
• source code
• q19.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.trino.tpch.q19.sql Maven / Gradle / Ivy

SELECT 
  sum(l.extendedprice* (1 - l.discount)) as revenue
FROM 
  "${database}"."${schema}"."${prefix}lineitem" l,
  "${database}"."${schema}"."${prefix}part" p
WHERE
  p.partkey = l.partkey
  AND
  ((
    p.brand = 'Brand#12'
    AND p.container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') 
    AND l.quantity >= 1 
    AND l.quantity <= 1 + 10 
    AND p.size BETWEEN 1 AND 5
    AND l.shipmode IN ('AIR', 'AIR REG') 
    AND l.shipinstruct = 'DELIVER IN PERSON'
  )
  OR (
    p.brand ='Brand#23'
    AND p.container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') 
    AND l.quantity >=10 
    AND l.quantity <=10 + 10 
    AND p.size BETWEEN 1 AND 10 
    AND l.shipmode IN ('AIR', 'AIR REG') 
    AND l.shipinstruct = 'DELIVER IN PERSON'
  ) 
  OR (
    p.brand = 'Brand#34'
    AND p.container IN ( 'LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') 
    AND l.quantity >=20 
    AND l.quantity <= 20 + 10 
    AND p.size BETWEEN 1 AND 15
    AND l.shipmode IN ('AIR', 'AIR REG') 
    AND l.shipinstruct = 'DELIVER IN PERSON'
  ))
;