• Home
• io.trino
• trino-product-tests
• 464
• source code
• q44.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.tpcds.q44.sql Maven / Gradle / Ivy

-- database: trino_tpcds; groups: tpcds; requires: io.trino.tempto.fulfillment.table.hive.tpcds.ImmutableTpcdsTablesRequirements
SELECT
  "asceding"."rnk"
, "i1"."i_product_name" "best_performing"
, "i2"."i_product_name" "worst_performing"
FROM
  (
   SELECT *
   FROM
     (
      SELECT
        "item_sk"
      , "rank"() OVER (ORDER BY "rank_col" ASC) "rnk"
      FROM
        (
         SELECT
           "ss_item_sk" "item_sk"
         , "avg"("ss_net_profit") "rank_col"
         FROM
           store_sales ss1
         WHERE ("ss_store_sk" = 4)
         GROUP BY "ss_item_sk"
         HAVING ("avg"("ss_net_profit") > (DECIMAL '0.9' * (
                  SELECT "avg"("ss_net_profit") "rank_col"
                  FROM
                    store_sales
                  WHERE ("ss_store_sk" = 4)
                     AND ("ss_addr_sk" IS NULL)
                  GROUP BY "ss_store_sk"
               )))
      )  v1
   )  v11
   WHERE ("rnk" < 11)
)  asceding
, (
   SELECT *
   FROM
     (
      SELECT
        "item_sk"
      , "rank"() OVER (ORDER BY "rank_col" DESC) "rnk"
      FROM
        (
         SELECT
           "ss_item_sk" "item_sk"
         , "avg"("ss_net_profit") "rank_col"
         FROM
           store_sales ss1
         WHERE ("ss_store_sk" = 4)
         GROUP BY "ss_item_sk"
         HAVING ("avg"("ss_net_profit") > (DECIMAL '0.9' * (
                  SELECT "avg"("ss_net_profit") "rank_col"
                  FROM
                    store_sales
                  WHERE ("ss_store_sk" = 4)
                     AND ("ss_addr_sk" IS NULL)
                  GROUP BY "ss_store_sk"
               )))
      )  v2
   )  v21
   WHERE ("rnk" < 11)
)  descending
, item i1
, item i2
WHERE ("asceding"."rnk" = "descending"."rnk")
   AND ("i1"."i_item_sk" = "asceding"."item_sk")
   AND ("i2"."i_item_sk" = "descending"."item_sk")
ORDER BY "asceding"."rnk" ASC
LIMIT 100