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

SELECT 
  s.name, 
  count(*) as numwait
FROM 
  "${database}"."${schema}"."${prefix}supplier" s,
  "${database}"."${schema}"."${prefix}lineitem" l1,
  "${database}"."${schema}"."${prefix}orders" o,
  "${database}"."${schema}"."${prefix}nation" n
WHERE 
  s.suppkey = l1.suppkey 
  AND o.orderkey = l1.orderkey
  AND o.orderstatus = 'F'
  AND l1.receiptdate> l1.commitdate
  AND EXISTS (
    SELECT 
      * 
    FROM 
      "${database}"."${schema}"."${prefix}lineitem" l2
    WHERE 
      l2.orderkey = l1.orderkey
      AND l2.suppkey <> l1.suppkey
  ) 
  AND NOT EXISTS (
    SELECT 
      * 
    FROM 
      "${database}"."${schema}"."${prefix}lineitem" l3
    WHERE 
      l3.orderkey = l1.orderkey 
      AND l3.suppkey <> l1.suppkey 
      AND l3.receiptdate > l3.commitdate
  ) 
  AND s.nationkey = n.nationkey 
  AND n.name = 'SAUDI ARABIA'
GROUP BY 
  s.name
ORDER BY 
  numwait DESC, 
  s.name
LIMIT 
  100
;