All Downloads are FREE. Search and download functionalities are using the official Maven repository.
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.q50.sql Maven / Gradle / Ivy
-- database: presto_tpcds; groups: tpcds; requires: io.prestosql.tempto.fulfillment.table.hive.tpcds.ImmutableTpcdsTablesRequirements
SELECT
"s_store_name"
, "s_company_id"
, "s_street_number"
, "s_street_name"
, "s_street_type"
, "s_suite_number"
, "s_city"
, "s_county"
, "s_state"
, "s_zip"
, "sum"((CASE WHEN (("sr_returned_date_sk" - "ss_sold_date_sk") <= 30) THEN 1 ELSE 0 END)) "30 days"
, "sum"((CASE WHEN (("sr_returned_date_sk" - "ss_sold_date_sk") > 30)
AND (("sr_returned_date_sk" - "ss_sold_date_sk") <= 60) THEN 1 ELSE 0 END)) "31-60 days"
, "sum"((CASE WHEN (("sr_returned_date_sk" - "ss_sold_date_sk") > 60)
AND (("sr_returned_date_sk" - "ss_sold_date_sk") <= 90) THEN 1 ELSE 0 END)) "61-90 days"
, "sum"((CASE WHEN (("sr_returned_date_sk" - "ss_sold_date_sk") > 90)
AND (("sr_returned_date_sk" - "ss_sold_date_sk") <= 120) THEN 1 ELSE 0 END)) "91-120 days"
, "sum"((CASE WHEN (("sr_returned_date_sk" - "ss_sold_date_sk") > 120) THEN 1 ELSE 0 END)) ">120 days"
FROM
store_sales
, store_returns
, store
, date_dim d1
, date_dim d2
WHERE ("d2"."d_year" = 2001)
AND ("d2"."d_moy" = 8)
AND ("ss_ticket_number" = "sr_ticket_number")
AND ("ss_item_sk" = "sr_item_sk")
AND ("ss_sold_date_sk" = "d1"."d_date_sk")
AND ("sr_returned_date_sk" = "d2"."d_date_sk")
AND ("ss_customer_sk" = "sr_customer_sk")
AND ("ss_store_sk" = "s_store_sk")
GROUP BY "s_store_name", "s_company_id", "s_street_number", "s_street_name", "s_street_type", "s_suite_number", "s_city", "s_county", "s_state", "s_zip"
ORDER BY "s_store_name" ASC, "s_company_id" ASC, "s_street_number" ASC, "s_street_name" ASC, "s_street_type" ASC, "s_suite_number" ASC, "s_city" ASC, "s_county" ASC, "s_state" ASC, "s_zip" ASC
LIMIT 100
