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

Go to download
-- database: presto_tpcds; groups: tpcds; requires: io.prestosql.tempto.fulfillment.table.hive.tpcds.ImmutableTpcdsTablesRequirements
SELECT
  "c_last_name"
, "c_first_name"
, "ca_city"
, "bought_city"
, "ss_ticket_number"
, "amt"
, "profit"
FROM
  (
   SELECT
     "ss_ticket_number"
   , "ss_customer_sk"
   , "ca_city" "bought_city"
   , "sum"("ss_coupon_amt") "amt"
   , "sum"("ss_net_profit") "profit"
   FROM
     store_sales
   , date_dim
   , store
   , household_demographics
   , customer_address
   WHERE ("store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk")
      AND ("store_sales"."ss_store_sk" = "store"."s_store_sk")
      AND ("store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk")
      AND ("store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk")
      AND (("household_demographics"."hd_dep_count" = 4)
         OR ("household_demographics"."hd_vehicle_count" = 3))
      AND ("date_dim"."d_dow" IN (6   , 0))
      AND ("date_dim"."d_year" IN (1999   , (1999 + 1)   , (1999 + 2)))
      AND ("store"."s_city" IN ('Fairview'   , 'Midway'   , 'Fairview'   , 'Fairview'   , 'Fairview'))
   GROUP BY "ss_ticket_number", "ss_customer_sk", "ss_addr_sk", "ca_city"
)  dn
, customer
, customer_address current_addr
WHERE ("ss_customer_sk" = "c_customer_sk")
   AND ("customer"."c_current_addr_sk" = "current_addr"."ca_address_sk")
   AND ("current_addr"."ca_city" <> "bought_city")
ORDER BY "c_last_name" ASC, "c_first_name" ASC, "ca_city" ASC, "bought_city" ASC, "ss_ticket_number" ASC
LIMIT 100