sql-tests.testcases.tpcds.q70.sql Maven / Gradle / Ivy

Go to download
-- database: presto_tpcds; groups: tpcds; requires: io.prestosql.tempto.fulfillment.table.hive.tpcds.ImmutableTpcdsTablesRequirements
SELECT
  "sum"("ss_net_profit") "total_sum"
, "s_state"
, "s_county"
, (GROUPING ("s_state") + GROUPING ("s_county")) "lochierarchy"
, "rank"() OVER (PARTITION BY (GROUPING ("s_state") + GROUPING ("s_county")), (CASE WHEN (GROUPING ("s_county") = 0) THEN "s_state" END) ORDER BY "sum"("ss_net_profit") DESC) "rank_within_parent"
FROM
  store_sales
, date_dim d1
, store
WHERE ("d1"."d_month_seq" BETWEEN 1200 AND (1200 + 11))
   AND ("d1"."d_date_sk" = "ss_sold_date_sk")
   AND ("s_store_sk" = "ss_store_sk")
   AND ("s_state" IN (
   SELECT "s_state"
   FROM
     (
      SELECT
        "s_state" "s_state"
      , "rank"() OVER (PARTITION BY "s_state" ORDER BY "sum"("ss_net_profit") DESC) "ranking"
      FROM
        store_sales
      , store
      , date_dim
      WHERE ("d_month_seq" BETWEEN 1200 AND (1200 + 11))
         AND ("d_date_sk" = "ss_sold_date_sk")
         AND ("s_store_sk" = "ss_store_sk")
      GROUP BY "s_state"
   )  tmp1
   WHERE ("ranking" <= 5)
))
GROUP BY ROLLUP (s_state, s_county)
ORDER BY "lochierarchy" DESC, (CASE WHEN ("lochierarchy" = 0) THEN "s_state" END) ASC, "rank_within_parent" ASC
LIMIT 100